如何根据SQL / php中另一列的多个值从列中选择单个值

问题描述：

The database is not mine. The structure is

fid| uid | value
 3 |  3  | spain
 3 |  5  | France
 2 |  3  | 45
 6 |  3  | male
 6 |  5  | female
 2 |  5  | 32

The field ID is primary key in another table, I'd like to forget about. Im trying to find all uid that have values 'spain', 'male'

I have the following working.

SELECT uid 
FROM DATABASE
WHERE value IN ('spain', 'male') GROUP BY uid HAVING COUNT(*) >= 2

The catch is as follows.. How would I select male, spain with value in range of 20-30 (that is males born in spain aged between 20-30?

Thanks a ton!!

数据库不是我的。结构是 p>

  fid |  uid | 价值
 3 |  3 | 西班牙
 3 |  5 | 法国
 2 |  3 |  45 
 6 |  3 | 男性
 6 |  5 | 女性
 2 |  5 |  32 
  code>  pre> 
 
 字段ID是另一个表中的主键，我想忘记。
我试图找到所有具有值'sain'的uid，  'male' p> 
 
 
我有以下工作。 p> 
 
 
  SELECT uid 
FROM DATABASE 
WHERE值IN（'spain'，'male  '）GROUP BY uid有COUNT（*）＆gt; = 2 
  code>  pre> 
 
 捕捉如下。我如何选择男性，西班牙的价值范围为 20-30 
（那是在西班牙出生的年龄在20-30岁之间的男性？ p> 
 
 
非常感谢!!  p> 
  div>

答

You have a really messed up table. You are mixing unrelated things in value.

Try:

SELECT uid
FROM DATABASE a JOIN
  DATABASE b USING (uid)
WHERE a.value IN ('spain', 'male')
  AND b.value >= 20
  AND b.value <= 30
GROUP BY uid
HAVING COUNT(*) >= 2

Note that I am comparing a string and an integer. You will need to test to see how well that works.

Also, I just put spain and male together, but perhaps they are really unrelated?

Does fid determine the type of value?

Try:

SELECT uid
FROM DATABASE country
JOIN DATABASE gender USING (uid)
JOIN DATABASE age USING (uid)
WHERE
  country.fid = 3 AND
  gender.fid = 6 AND
  age.fid = 2 AND
  county.value = 'spain' AND
  gender.value = 'male' AND
  age.value >= 20
  age.value <= 30
GROUP BY uid
HAVING COUNT(*) >= 2

This code should work more reliably.

答

Use a self join:

SELECT tland.uid
FROM `table` AS tland
INNER JOIN `table` AS tgender ON tland.uid = tgender.uid
INNER JOIN `table` AS tage ON tland.uid = tage.uid
WHERE tland.value = 'spain'
  AND tgender.value = 'male'
  AND 20 <= tage.value AND tage.value <= 30

答

This will work.

SELECT d1.uid FROM demo d1
INNER JOIN demo d2 ON d2.uid = d1.uid AND d2.fid = 6 AND d2.value = "Male"
INNER JOIN demo d3 ON d3.uid = d1.uid AND d3.fid = 3 AND d3.value = "Spain"
WHERE (d1.fid = 2 AND (d1.value BETWEEN 20 AND 30))

答

Keeping your syntax as close to what it was as possible, this might work:

SELECT uid
FROM database
WHERE value in ('spain', 'male') or convert(value, , SIGNED INTEGER) between 30 and 50
GROUP BY uid HAVING COUNT(*) >= 3

答

Since your fields are mapped as so:

fid 3 = country
fid 2 = age
fid 6 = sex

You can transform that table into a more logical view (indexed on UID)

(SELECT c.uid, c.country, a.age, s.sex FROM
   (SELECT uid, value AS country FROM maintable WHERE fid = 3) c INNER JOIN
   (SELECT uid, value AS age FROM maintable WHERE fid = 2) a ON c.uid = a.uid INNER JOIN
   (SELECT uid, value AS sex FROM maintable WHERE fid = 6) s ON c.uid = s.uid
) as X

Once it is inside a view you can then treat your data as a regular table. I'm assuming you may have more than 3 types of data since fid goes up to 6 on your example.

Assuming you have put that into a view (called X), you can just do:

SELECT * FROM X
WHERE age BETWEEN 20 AND 30
   AND country = 'spain'
   AND sex = 'male'

如何根据SQL / php中另一列的多个值从列中选择单个值

相关推荐