在sql中的字段之间订阅

Question

我有一个包含以下字段的表

id,cid,cv

用这些数据

1,5,code
2,3,code4
3,3,cod2
1,4,code5
1,3,code4

]想要选择id什么cid=5和cv=code和cid=3或4和cv=code4。我希望id=1。我使用了这个查询，但结果是0

SELECT id FROM table WHERE (cid='5' and cv='code') and (cid in ('3','4') and cv='code4')

抱歉英语不好。

Answer 1

SELECT id FROM my_table  
WHERE (cid=5 and cv='code') 
or ((cid = 3 and cv='code4') 
and (cid = 4 and cv='code4')) 
group by id

SQL Fiddle示例

Answer 2

试试这个（将父级之间的AND改为OR）：

SELECT id 
FROM table 
WHERE (cid='5' and cv='code') OR (cid in ('3','4') and cv='code4')

Answer 3

您应该尝试使用ORs而不是ANDs

SELECT id 
FROM table 
WHERE (cid='5' and cv='code') 
    OR (cid in ('3','4') and cv='code4')

Answer 4

我把它放在http://sqlfiddle.com/#！2/40054/51（你可以从最后一个数字51看出我转错了几个圈）。我认为这被称为"键值"，我建议对这篇文章的标签进行编辑，但我认为你应该重新标记它以包含键值。无论如何：与一行引用一件事的方法相比，像这样的数据库很难查询——相反，一行引用了一个属性。因此，如果你想了解哪些"事物"具有特定的属性，你必须将每个事物的属性粘贴在一行中。运行下面的查询，你就会明白我的意思：

SELECT  laptops.laptop, rams.termvalue as ramCount,
  cpus.termvalue as cpusCount, 
  maker.termvalue as company
  FROM  (SELECT DISTINCT laptop FROM my_table) As Laptops
LEFT JOIN (SELECT laptop, termvalue FROM my_table 
            WHERE term = 'ram') AS rams
            ON rams.laptop = laptops.laptop
LEFT JOIN (SELECT laptop, termvalue FROM my_table 
            WHERE term = 'cpu') AS cpus
            ON cpus.laptop = laptops.laptop
LEFT JOIN (SELECT laptop, termvalue FROM my_table 
            WHERE term = 'company') AS maker
            ON maker.laptop = laptops.laptop

如果你对这种类型的数据库提出其他问题，你应该总是提到你使用的是"键/值"排列，这是不常见的，也不是我们任何人所假设的。

在您的fiddler示例中，您希望找到company=dell和cpu=2或ram=2，因此您可以通过将封装在括号中并给它一个别名来查询整个查询，如下所示：

SELECT * FROM  (
SELECT  laptops.laptop, rams.termvalue as ramCount,
  cpus.termvalue as cpusCount, 
  maker.termvalue as company
  FROM  (SELECT DISTINCT laptop FROM my_table) As Laptops
LEFT JOIN (SELECT laptop, termvalue FROM my_table 
            WHERE term = 'ram') AS rams
            ON rams.laptop = laptops.laptop
LEFT JOIN (SELECT laptop, termvalue FROM my_table 
            WHERE term = 'cpu') AS cpus
            ON cpus.laptop = laptops.laptop
LEFT JOIN (SELECT laptop, termvalue FROM my_table 
            WHERE term = 'company') AS maker
            ON maker.laptop = laptops.laptop
) as laps
WHERE  company = 'dell' AND (cpuscount = 2 OR ramcount = 2)