我一直在考虑如何做到这一点,现在几乎2天,仍然无济于事。如果有人能给我指点或帮助我做这个SQL语句,我将不胜感激。所以我有这些表格。
Table1
NameField
Mark
John
Chris
Tina
Charles
Table2
NameField ItemField
Mark Pencil
Mark Bag
Mark Paper
Mark Book
John Book
John Ballpen
Chris Bag
Chris Paper
Chris Pencil
Tina Ballpen
Charles Computer
Charles Book
Charles Pencil
Charles Box
Charles Shoes
如何编写基于这些条件查询数据的SQL语句?
a)从Table1
中得到所有NameField
,其中ItemField
有Bag
和Paper
和Pencil
。
Chris
b)从Table1
中获得所有NameField
,其中ItemField
有Bag
和Paper
和Pencil
,额外的0或1或2额外的随机ItemField
。
Mark, Chris
c)从Table1
中获得所有NameField
,其中ItemField
有Pencil
,无论ItemField
是否有其他项目,并计算额外的项目(不包括Pencil
)并列出额外的项目。
Mark - 3 - Bag, Paper, Book
Chris - 2 - Bag, Paper
Charles - 4 - Computer, Book, Box, Shoes
我尝试在服务器端脚本语言的帮助下通过Table1
中的每个NameField
循环,然后再次循环每个ItemField
。所以如果我在Table
下有1000个项目,我需要循环1000个项目。这个解决方案的效率低得离谱。很抱歉我没有写代码因为我真的没有。什么都没有。这是我第一次遇到这样的问题。非常感谢。
我尝试了一下,只使用了Table2。
1)
SELECT a.NameField,
COUNT(DISTINCT b.ItemField) AS MatchCount,
COUNT(DISTINCT a.ItemField) AS TotalCount,
GROUP_CONCAT(DISTINCT b.ItemField) AS MatchedItems,
GROUP_CONCAT(DISTINCT a.ItemField) AS AllItems
FROM Table2 a
LEFT JOIN Table2 b
ON b.NameField = a.NameField
AND b.ItemField IN ('Bag', 'Paper', 'Pencil')
GROUP BY a.NameField
HAVING TotalCount = 3 AND MatchCount = 3;
2)
SELECT a.NameField,
COUNT(DISTINCT b.ItemField) AS MatchCount,
COUNT(DISTINCT a.ItemField) AS TotalCount,
GROUP_CONCAT(DISTINCT b.ItemField) AS MatchedItems,
GROUP_CONCAT(DISTINCT a.ItemField) AS AllItems
FROM Table2 a
LEFT JOIN Table2 b
ON b.NameField = a.NameField
AND b.ItemField IN ('Bag', 'Paper', 'Pencil')
GROUP BY a.NameField
HAVING MatchCount >= 3;
3)
SELECT a.NameField,
COUNT(DISTINCT b.ItemField) AS OtherItemCount,
GROUP_CONCAT(DISTINCT b.ItemField) AS OtherItems
FROM Table2 a
LEFT JOIN Table2 b
ON b.NameField = a.NameField
AND b.ItemField != 'Pencil'
WHERE a.ItemField = 'Pencil'
GROUP BY a.NameField;
它确实需要输入你要匹配的项目的数量,但希望它能帮助!
编辑:这是一个小提琴-看到这个改进会很有趣!
查询A很简单所以我就从它开始:
SELECT Namefield FROM Table2
WHERE ItemField IN ('Bag', 'Paper', 'Pencil')
GROUP BY NameField
HAVING count(*) = 3;
查询B:在子查询中使用与上述相同的查询,但检查总体项目计数小于或等于5(允许额外的0,1,2)
SELECT Namefield FROM Table2
WHERE NameField IN
(SELECT Namefield FROM Table2 WHERE ItemField IN ('Bag', 'Paper', 'Pencil') GROUP BY NameField HAVING count(*) = 3)
GROUP BY NameField
HAVING count(*) <= 5;
查询C:我认为pssdbt有最好的结果。但请注意,包含多个项目的东西可能应该由服务器端语言处理,而不是SQL。SQL是数据的平面表示。
我的想法与其他人略有不同,但它确实需要以使检查字符串按字母顺序排列的形式进行一些前期工作。关于我的查询的好处是,它支持对同一项目进行多次计数,例如获得3支铅笔;2袋。
(灵感来自@pssdbt的回答)
查询:
SELECT NameField, GROUP_CONCAT(ItemField ORDER BY ItemField) AS MatchedItems
FROM Table2
GROUP BY NameField
HAVING MatchedItems = "Bag,Paper,Pencil";
查询B:
SELECT NameField, GROUP_CONCAT(DISTINCT ItemField ORDER BY ItemField) AS MatchedItems ,
count(ItemField) as totalCount
FROM Table2
GROUP BY NameField
HAVING MatchedItems LIKE "%Bag%Paper%Pencil%" AND totalCount <= 5;
查询C: SELECT NameField,
GROUP_CONCAT(DISTINCT if(ItemField="Pencil",null,ItemField) ORDER BY ItemField) AS MatchedItems,
SUM(if(ItemField="Pencil",0,1)) as extraCount,
SUM(if(ItemField="Pencil",1,0)) as hasPencil
FROM Table2
GROUP BY NameField
HAVING hasPencil > 0;