需要帮助编写查询 [英] Need help writing a query
问题描述
数据库由以下4个表组成，各自具有
属性：
CUSTOMER（C＃，CUSTOMER NAME，ADDRESS），关键是[C＃]
项目（我＃，项目名称，制造商，年份），关键是[我＃]
BOUGHT（C＃，I＃，DATE，QUANTITY），关键是[C＃，我＃，日期]
首选（我＃，C＃），关键是[我＃，C＃]
我是尝试构建以下查询（在SQL中）
购买John喜欢的所有商品的客户列表。
我可以得到约翰喜欢的所有物品的清单，但我不是。
确定如何检查那些购买了所有那些
物品的顾客的清单。我假设它是一个分区或某种减法
但是我不知道如何制定SQL查询。
感谢所有的帮助，谢谢！
（ti ****** @ gmail.com）写道：
数据库由以下4个表组成，各自具有
属性：
CUSTOMER（C＃ ，CUSTOMER NAME，ADDRESS），关键是[C＃]
ITEM（我＃，项目名称，制造商，年份），关键是[我＃]
BOUGHT （C＃，I＃，DATE，QUANTITY），关键是[C＃，我＃，DATE]
PREFER（我＃，C＃），关键是[我＃，C＃]
我正在尝试构建以下查询（在SQL中）
购买了John喜欢的所有项目的客户列表。
我可以获得John喜欢的所有物品的清单，但我不是
确定如何检查该清单与购买所有那些
项目。我假设它是一个分区或某种减法
但我不知道如何制定SQL查询。
这种气味分类，但好吧，不管怎样，让我们去吧。
如果内存服务于此他们出于某种原因我从来没有理解呼叫关系师。在较少封闭的术语中，HAVING
子句可以缩短对几个EXISTS和NOT EXISTS的需求。
SELKCT CC＃，C.CUSTOMER_NAME
来自CUSTOMER C
加入（选择BC＃
来自BOUGHT B
GROUP BY BC＃
有计数（DISTINCT BI＃）=
（选择计数（*）
来自首选P
加入CUSTOMER C在PC上＃ = CC＃
WHERE C.CUSTOMER_NAME =''John''））AS res
ON CC＃= res.C＃
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
SQL Server 2005联机丛书
http://www.microsoft.com/technet/pro...ads/books.mspx
联机丛书SQL版服务器2000在
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Erland Sommarskog写道：
（ti******@gmail.com）写道：
>数据库由以下4个具有各自属性的表组成：
CUSTOMER（C＃，CUSTOMER NAME，ADDRESS），关键是[C＃]
ITEM（I＃ ，项目名称，制造商，年份，关键是[我＃]
BOUGHT（C＃，我＃，DATE，QUANTITY），关键是[C＃，我＃，日期]
PREFER（我＃，C＃），关键是[我＃，C＃]
我正在尝试构建以下查询（在SQL中）
全部购买的客户列表约翰喜欢的物品。
我可以得到约翰喜欢的所有物品的清单，但我不确定如何检查那些购买了所有物品的顾客的清单
项目。我假设它是一个分区或某种减法
但我不知道如何制定SQL查询。
这种气味可以分类，但是好吧，不管怎么说，让我们去吧。
如果内存是这样的话他们出于某种原因我从来没有理解呼叫关系师。在较少封闭的术语中，HAVING
子句可以缩短对几个EXISTS和NOT EXISTS的需求。
SELKCT CC＃，C.CUSTOMER_NAME
来自CUSTOMER C
加入（选择BC＃
来自BOUGHT B
GROUP BY BC＃
有计数（DISTINCT BI＃）=
（选择计数（*）
来自首选P
加入CUSTOMER C在PC上＃ = CC＃
WHERE C.CUSTOMER_NAME =''John''））AS res
ON CC＃= res.C＃
这将选择所有购买相同/数量/
物品的顾客，而不是John喜欢的物品。
我认为这将选择所有购买所有物品的顾客
，John更喜欢：
SELECT CC＃，C.CUSTOMER_NAME
来自客户C
加入B＃CC＃= BC＃
JOI N PREFER P ON BI＃= PI＃
在PC上加入客户J＃= JC＃AND J.CUSTOMER_NAME =''John''
GROUP BY CC＃，C .CUSTOMER_NAME
HAVING COUNT（*）=（
SELECT COUNT（*）
来自PREFER P
加入PC上的CUSTOMER J＃= JC＃AND J.CUSTOMER_NAME =''John''
）
Erland：为什么如果它重要是不是上课？是不是
Usenet小组的目的是相互分享和相互学习？什么
相关性是什么知识将用于什么？无论如何，谢谢你的尝试
，但Ed的答案似乎更符合
查询的目的。
谢谢Ed，似乎是我在找什么，这很有意思，我甚至没有设置一个伯爵，但现在我看着它，
很难想象其他任何方式。
再次感谢:)
Database consists of the following 4 tables with respective
attributes:
CUSTOMER(C#, CUSTOMER NAME, ADDRESS), the key is [C#]
ITEM(I#, ITEM NAME, MANUFACTURER, YEAR), the key is [I#]
BOUGHT(C#, I#, DATE, QUANTITY), the key is [C#, I#,DATE]
PREFER(I#, C#), the key is [I#, C#]
I''m trying to construct the following query (in SQL)
List of customers that bought all the items that John prefers.
I can get the list of all the items that John prefers, but I''m not
sure how to check that list against customers who bought ALL those
items. I''m assuming it''s either a division or some sort of subtraction
but I''m not sure how to formulate the SQL query.
Any and all help is appreciated, thanks!
(ti******@gmail.com) writes:Database consists of the following 4 tables with respective
attributes:
CUSTOMER(C#, CUSTOMER NAME, ADDRESS), the key is [C#]
ITEM(I#, ITEM NAME, MANUFACTURER, YEAR), the key is [I#]
BOUGHT(C#, I#, DATE, QUANTITY), the key is [C#, I#,DATE]
PREFER(I#, C#), the key is [I#, C#]
I''m trying to construct the following query (in SQL)
List of customers that bought all the items that John prefers.
I can get the list of all the items that John prefers, but I''m not
sure how to check that list against customers who bought ALL those
items. I''m assuming it''s either a division or some sort of subtraction
but I''m not sure how to formulate the SQL query.This smells of class assignment, but OK, let''s go for it anyway.
If memory serves this is something they for some reason I''ve never
understood call relational division. In less occluded terms, a HAVING
clause can shortcut the need for a couple of EXISTS and NOT EXISTS.
SELKCT C.C#, C.CUSTOMER_NAME
FROM CUSTOMER C
JOIN (SELECT B.C#
FROM BOUGHT B
GROUP BY B.C#
HAVING COUNT(DISTINCT B.I#) =
(SELECT COUNT(*)
FROM PREFER P
JOIN CUSTOMER C ON P.C# = C.C#
WHERE C.CUSTOMER_NAME = ''John'')) AS res
ON C.C# = res.C#
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Erland Sommarskog wrote:
(ti******@gmail.com) writes:>Database consists of the following 4 tables with respective
attributes:
CUSTOMER(C#, CUSTOMER NAME, ADDRESS), the key is [C#]
ITEM(I#, ITEM NAME, MANUFACTURER, YEAR), the key is [I#]
BOUGHT(C#, I#, DATE, QUANTITY), the key is [C#, I#,DATE]
PREFER(I#, C#), the key is [I#, C#]
I''m trying to construct the following query (in SQL)
List of customers that bought all the items that John prefers.
I can get the list of all the items that John prefers, but I''m not
sure how to check that list against customers who bought ALL those
items. I''m assuming it''s either a division or some sort of subtraction
but I''m not sure how to formulate the SQL query.
This smells of class assignment, but OK, let''s go for it anyway.
If memory serves this is something they for some reason I''ve never
understood call relational division. In less occluded terms, a HAVING
clause can shortcut the need for a couple of EXISTS and NOT EXISTS.
SELKCT C.C#, C.CUSTOMER_NAME
FROM CUSTOMER C
JOIN (SELECT B.C#
FROM BOUGHT B
GROUP BY B.C#
HAVING COUNT(DISTINCT B.I#) =
(SELECT COUNT(*)
FROM PREFER P
JOIN CUSTOMER C ON P.C# = C.C#
WHERE C.CUSTOMER_NAME = ''John'')) AS res
ON C.C# = res.C#That will select all customers who bought the same /number/ of
items as what John prefers, but not necessarily the same items.
I think this will select all customers who bought all the items
that John prefers:
SELECT C.C#, C.CUSTOMER_NAME
FROM CUSTOMER C
JOIN BOUGHT B ON C.C# = B.C#
JOIN PREFER P ON B.I# = P.I#
JOIN CUSTOMER J ON P.C# = J.C# AND J.CUSTOMER_NAME = ''John''
GROUP BY C.C#, C.CUSTOMER_NAME
HAVING COUNT(*) = (
SELECT COUNT(*)
FROM PREFER P
JOIN CUSTOMER J ON P.C# = J.C# AND J.CUSTOMER_NAME = ''John''
)
Erland: Why would it matter if it''s a class assignment or not? Is not
the purpose of a Usenet group to share and learn from each other? What
relevance is it what the knowledge will be used for? Thank you for
your attempt anyway, but Ed''s answer seems more in line with what the
query is intended to do.
Thank you Ed, seems to be what I''m looking for, it''s interesting, I
never even though of setting up a Count, but now that I look at it,
it''s hard to imagine any other way of doing it.
Thanks again :)
这篇关于需要帮助编写查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
