list results in mysql from the same table

问题:

How can i list the iduser from this table where gender=man and int=woman?

Table usermeta
----------------------
id  iduser a        b
12  204    age      19
7   203    age      35
6   200    age      24
3   201    age      34
5   201    gender   man
2   200    gender   woman
8   203    gender   man
9   204    gender   man
4   201    int      woman
10  204    int      male
11  203    int      woman
1   200    int      male

The answer should be:

iduser

204

I have another question, what if i want filter the age too? I'am trying to resolve this but i can't... Where shopuld i put the "age=19"? I try this... a.a = 'age' AND a.b = '19' but dont work... thanks.

回答1:

A self join should work. Someting like (untested):

SELECT a.`iduser` FROM `table` a 
JOIN `table` b ON b.`iduser` = a.`iduser` 
WHERE a.`a`='gender' AND a.`b`='man' AND b.`a`='int' AND b.`b`='woman'

But you should consider to optimize the db structure since this seems a little performace hungry.

回答2:

Yikes!

This is not how a database is supposed to look like, anyway.

SELECT DISTINCT a.iduser FROM usermeta t1
INNER JOIN usermeta t2 ON (t1.iduser = t2.iduser)
WHERE t1.a = 'gender' AND t1.b = 'man'
      AND t2.a = 'int' AND t2.b = 'woman'