Geek Answers Handbook

"SELECT .. WHERE .. AND .." (two conditions) for the key value table?

What am i trying to do

I try to get commentIdby asking type = xandtarget = x ,

usually (THIS EXAMPLE) , the table structure should look like this:

+-----------+-------+--------+
| commentId | type  | target |
+-----------+-------+--------+
|         1 | post  |      2 |
|         2 | post  |      8 |
|         3 | video |      6 |
+-----------+-------+--------+

and in this situation, I can use this query to get commentId:

SELECT `commentId` FROM `comment_datas` WHERE type = 'post' AND target = '2'

Real problem

But this is the real table structure (with key value construction):

+-----------+--------+-------+
| commentId |  name  | value |
+-----------+--------+-------+
|         1 | type   | post  |
|         1 | target | 2     |
|         2 | type   | post  |
|         2 | target | 8     |
|         3 | type   | post  |
|         3 | target | 6     |
+-----------+--------+-------+

Now I don’t know how to get commentIdany thoughts on the request I wrote above.

+4
source share
5 answers

Until @ juergen-d fixes a typo: here is the corrected version:

SELECT commentId 
FROM comment_datas  
GROUP BY commentId
HAVING sum(name = 'type' AND value = 'post') > 0 
   AND sum(name = 'target' AND value = '2') > 0

Explanation:

sum , name = type value = post
sum 0, group by, , - target.

+4

commentId. , , ,

SELECT commentId 
FROM comment_datas 
GROUP BY commentId
HAVING sum(name = 'type' and value = 'post') > 0 
   AND sum(name = 'target' and value = '2') > 0
+4

.

-

SELECT commentId FROM comment_datas
WHERE name="type" AND value="post"
AND commentId IN (
    SELECT commentId FROM comment_datas WHERE name="target" AND value=2
);

, , . .

"" "" . "name" "value" "type" "target" . , , . .

+1

,

SELECT `commentId` FROM `comment_datas ` WHERE (name = 'type' AND value = 'post') OR (name = 'target' AND value = '2') GROUP BY `commentId`
0

,

    SELECT 
            `commentId` 
    FROM 
            `comment_datas` 
    WHERE 
            (`name` = 'type'   AND `value` = 'post') OR 
            (`name` = 'target' AND `value` = '2') 
    GROUP BY
            `commentId`
0

More articles:


All Articles