SQL SELECT DISTINCT HAVING 计算唯一条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13258442/
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
SELECT DISTINCT HAVING Count unique conditions
提问by vfxdev
I've searched for an answer on this but can't find quite how to get this distinct recordset based on a condition. I have a table with the following sample data:
我已经搜索了这个问题的答案，但找不到如何根据条件获取这个不同的记录集。我有一个包含以下示例数据的表：
Type Color Location Supplier
---- ----- -------- --------
Apple Green New York ABC
Apple Green New York XYZ
Apple Green Los Angeles ABC
Apple Red Chicago ABC
Apple Red Chicago XYZ
Apple Red Chicago DEF
Banana Yellow Miami ABC
Banana Yellow Miami DEF
Banana Yellow Miami XYZ
Banana Yellow Atlanta ABC
I'd like to create a query that shows the count of unique locations for each distinct Type+Color where the number of unique locations is more than 1, e.g.
我想创建一个查询，显示每个不同类型+颜色的唯一位置计数，其中唯一位置的数量超过 1，例如
Type Color UniqueLocations
---- ----- --------
Apple Green 2
Banana Yellow 2
Note that {Apple, Red, 1} doesn't appear because there is only 1 location for red apples (Chicago). I think I've got this one (but perhaps there is a simpler method). I'm using:
请注意，{Apple, Red, 1} 不会出现，因为红苹果（芝加哥）只有 1 个位置。我想我有这个（但也许有一种更简单的方法）。我正在使用：
SELECT Type, Color, Count(Location) FROM
(SELECT DISTINCT Type, Color, Location FROM MyTable)
GROUP BY Type, Color HAVING Count(Location)>1;
How can I create another query that lists the Type, Color, and Locationfor each distinct Type,Colorwhen the count of unique locations for that Type,Coloris greater than 1? The resulting recordset would look like:
当唯一位置的计数大于 1时Type, Color，如何创建另一个查询来列出, 和Location对于每个不同Type,Color的位置Type,Color？生成的记录集如下所示：
Type Color Location
---- ----- --------
Apple Green New York
Apple Green Los Angeles
Banana Yellow Miami
Banana Yellow Atlanta
Note that Apple, Red, Chicagodoesn't appear because there is only 1 location for red apples. Thanks!
请注意，Apple, Red, Chicago它不会出现，因为红苹果只有 1 个位置。谢谢！
采纳答案by Michael Berkowski
Use a COUNT(DISTINCT Location)and join against a subquery on Typeand ColorThe GROUP BYand HAVINGclauses as you have attempted to use them will do the job.
COUNT(DISTINCT Location)对子查询使用and 连接Typeand尝试使用它们Color的GROUP BYandHAVING子句将完成这项工作。
/* Be sure to use DISTINCT in the outer query to de-dup */
SELECT DISTINCT
MyTable.Type,
MyTable.Color,
Location
FROM
MyTable
INNER JOIN (
/* Joined subquery returns type,color pairs having COUNT(DISTINCT Location) > 1 */
SELECT
Type,
Color,
/* Don't actually need to select this value - it could just be in the HAVING */
COUNT(DISTINCT Location) AS UniqueLocations
FROM
MyTable
GROUP BY Type, Color
/* Note: Some RDBMS won't allow the alias here and you
would have to use the expanded form
HAVING COUNT(DISTINCT Location) > 1
*/
HAVING UniqueLocations > 1
/* JOIN back against the main table on Type, Color */
) subq ON MyTable.Type = subq.Type AND MyTable.Color = subq.Color
回答by fthiella
You could write your first query as this:
你可以这样写你的第一个查询：
Select Type, Color, Count(Distinct Location) As UniqueLocations
From Table
Group By Type, Color
Having Count(Distinct Location) > 1
(if you're using MySQL you could use the alias UniqueLocationsin your havingclause, but on many other systems the aliases are not yet available as the havingclause is evaluated before the selectclause, in this case you have to repeat the count on both clauses).
（如果您使用 MySQL，您可以UniqueLocations在having子句中使用别名，但在许多其他系统上，别名尚不可用，因为having子句在子句之前进行评估select，在这种情况下，您必须对两个子句重复计数）。
And for the second one, there are many different ways to write that, this could be one:
对于第二个，有很多不同的写法，这可能是一种：
Select Distinct Type, Color, Location
From Table
Where
Exists (
Select
*
From
Table Table_1
Where
Table_1.Type = Table.Type
and Table_1.Color = Table.Color
Group By
Type, Color
Having
Count(Distinct Location) > 1
)
