sql查询以选择两列中具有相同id但不同值的记录 [英] sql query to select record having same id but different value in two columns

问题描述

我的桌子是这样的-

现在查看articleid = 54的最后两条记录

Now look at the last two records with articleid=54

我只想一次选择该记录-我想为(fieldsid = 1和value = 1)和(fieldsid = 2和value = 1)选择具有相同商品ID的所有记录

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)

articleid必须相同，但是它将在两个不同的记录中检查所需的值.

The articleid must be same but it would check for the desired value in two different records.

请有人帮助我.

我正在尝试这样做-

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)

推荐答案

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)

这篇关于sql查询以选择两列中具有相同id但不同值的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！