MySQL sql查询选择两列中id相同但值不同的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21497348/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql query to select record having same id but different value in two columns
提问by Gaurav Manral
My table looks like this -
我的桌子看起来像这样 -
Now look at the last two records with articleid=54
现在查看 articleid=54 的最后两条记录
I want to select this record only once like - i want to select all records which have same articleid for (fieldsid=1 and value=1) and also for (fieldsid=2 and value=1)
我只想选择这条记录一次 - 我想为（fieldsid=1 and value=1）和（fieldsid=2 and value=1）选择所有具有相同文章ID的记录
The articleidmust be same but it would check for the desired value in two different records.
在articleid必须相同，但它会为您在两个不同的记录所需的值。
Please somebody help me.
请有人帮助我。
I am trying to do it like this -
我正在尝试这样做 -
select n1.id, n1.fieldsid, n1.value
from `tablename` n1
where (n1.fieldsid='1' and n1.value='1')
and n1.id = (select n2.id
from `tablename` n2
where (n1.fieldsid='2' and n2.value='2')
and n1.id=n2.id)
采纳答案by fthiella
SELECT youtable.*
FROM yourtable
WHERE articleid IN (SELECT articleid
FROM yourtable
WHERE (fieldsid, value) IN ((1,1),(2,1))
GROUP BY articleid
HAVING COUNT(*)=2)
edit: if you only need the first record:
编辑：如果您只需要第一条记录：
SELECT youtable.*
FROM yourtable
WHERE id IN (SELECT MIN(id)
FROM yourtable
WHERE (fieldsid, value) IN ((1,1),(2,1))
GROUP BY articleid
HAVING COUNT(*)=2)
回答by Gaurav Manral
Thanks for your quick response fthiella. with your help i sorted it out like this -
感谢您的快速回复 fthiella。在你的帮助下，我是这样整理的——
SELECT joom_fieldsattach_values.*
FROM joom_fieldsattach_values
WHERE `articleid` IN (SELECT `articleid`
FROM joom_fieldsattach_values
WHERE (`fieldsid`, `value`) IN ((1,1),(2,1))
GROUP BY `articleid`
HAVING COUNT(*)=2)
GROUP BY `articleid`
