我已经搜索了一个答案,但是找不到如何根据条件得到这个不同的记录集.我有一个表格,具有以下示例数据：
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
我想创建一个查询,显示每个不同类型颜色的独特位置的数量,其中唯一位置的数量大于1,例如
Type Color UniqueLocations ---- ----- -------- Apple Green 2 Banana Yellow 2
请注意,{Apple,Red,1}不会出现,因为红苹果只有1个位置(芝加哥).我想我有这个(但也许有一个更简单的方法).我在用着：
SELECT Type,Color,Count(Location) FROM (SELECT DISTINCT Type,Location FROM MyTable) GROUP BY Type,Color HAVING Count(Location)>1;
如果该类型,颜色的唯一位置数大于1,则可以为每个不同的类型,颜色创建另一个列出类型,颜色和位置的查询？得到的记录集将如下所示：
Type Color Location ---- ----- -------- Apple Green New York Apple Green Los Angeles Banana Yellow Miami Banana Yellow Atlanta
请注意,苹果,红,芝加哥不会出现,因为红苹果只有1个位置.谢谢！
解决方法
使用COUNT(DISTINCT位置)并与类型和颜色上的子查询相连接您尝试使用它们的GROUP BY和HAVING子句将执行该作业.
/* 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,/* 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
