仅当总和大于 100 时，SQL 才选择所有记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8827245/
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 select all records only if the sum is greater than 100
提问by user1144191
I'm not exactly sure how to ask this so I'll give an example
我不太确定如何问这个，所以我举个例子
I have a huge table that resembles something like this...
我有一张巨大的桌子，类似于这样的东西......
Name Widgets TransDate Location
Abby 2 12/1/2010 Middleton
Abby 13 1/10/2011 Burmingham
Bobby 10 12/12/2011 Easton
Bobby 5 10/10/2011 Weston
.
.
And my current sql statement is...
而我当前的 sql 语句是...
SELECT name, widgets, TransDate, Location
FROM MyTable
WHERE TransDate BETWEEN 1/1/2011 and 12/31/2011
to give me a table like this...
给我一张这样的桌子...
Name Widgets TransDate Location
Abby 13 1/10/2011 Burmingham
Bobby 15 12/12/2011 Easton
Bobby 5 10/10/2011 Weston
.
.
How do I modify the above SQL to also get rid of the records of people who do not meet a Widget quota X... say X = 16. In this case, Abby would be dropped because her total # of widgets is 13 and Bobby's records would stay because his total is 20.
我如何修改上面的 SQL 以删除不满足小部件配额 X 的人的记录...比如说 X = 16。在这种情况下，Abby 将被删除，因为她的小部件总数为 13，而 Bobby 的小部件总数为记录会保留下来，因为他的总数是 20。
Thank you in advance!
先感谢您！
回答by Michael Berkowski
If I understand your request, you want similar results to what you've already got, but filtering for those names who have met the quota. If that is correct, you can use an IN()subquery to find names grouped with >= 100 widgets.
如果我理解您的要求，您希望获得与您已经获得的结果类似的结果，但要过滤那些达到配额的名称。如果这是正确的，您可以使用IN()子查询来查找按 >= 100 个小部件分组的名称。
SELET name, widgets, TransDate, Location FROM MyTable
WHERE
/* IN() subquery for names meeting the quota */
name IN (
SELECT name
FROM tbl
/* If they must have met the quota only during the time window, uncomment below */
/* Otherwise, omit the WHERE clause to find those who have met the quota at any time */
/* WHERE TransDate BETWEEN '1/1/2011' and '12/31/2011' */
GROUP BY name
HAVING SUM(widgets) >= 100
)
AND TransDate BETWEEN '1/1/2011' and '12/31/2011'
回答by SQLMenace
for sql server it could be done like this
对于 sql server，它可以像这样完成
SELECT m.name, m.widgets, m.TransDate, m.Location
FROM MyTable m
JOIN(SELECT name, SUM(widgets)
FROM MyTable
WHERE TransDate BETWEEN '1/1/2011' and '12/31/2011'
GROUP BY NAME
HAVING SUM(widgets) >= 16) x
ON x.NAME = m.NAME
WHERE m.TransDate BETWEEN '1/1/2011' and '12/31/2011'
回答by Lamak
For SQL Server 2005+ you could also try:
对于 SQL Server 2005+，您还可以尝试：
SELECT name, widgets, TransDate, Location
FROM (
SELECT name, widgets, TransDate, Location, SUM(widgets) OVER(PARTITION BY Name) Quant
FROM MyTable
WHERE TransDate BETWEEN 1/1/2011 and 12/31/2011) A
WHERE Quant >= 16
This is assuming that the quota must be meeted on the same date frame.
这是假设必须在同一日期范围内满足配额。
