sql - 如何在组中查找重复项-SQL 2008

我有一张表（mytable），里面有以下数据。（按订单编号、类别、类型排序）

Order _No   Category    Type
Ord1    A   Main Unit
Ord1    A   Other
Ord1    A   Other
Ord2    B   Main Unit
Ord2    B   Main Unit
Ord2    B   Other

我需要做的是，扫描表格，看看是否有任何“类别”有一个以上的“主要单位”。
如果是，请对整个类别发出警告。预期的结果应该是这样的。

Order _No   Category    Type      Warning
Ord1    A   Main Unit
Ord1    A   Other
Ord1    A   Other
Ord2    B   Main Unit     More than one Main Units
Ord2    B   Main Unit     More than one Main Units
Ord2    B   Other     More than one Main Units

我尝试了几种方法（使用子查询）来获得结果，但没有成功。请帮忙！啊！

(Case
 When (Select t1.Category
             From MyTable as t1
    Where  MyTable.Order_No = t1.Order_No
                    AND MyTable.Category = t1. Category
        AND  MyTable.Type = t1.Type
               AND  MyTable.Type = ‘Main Unit’
    Group by t1. t1.Order_No, t1. Category, t1.Type
Having  Count(*) >1) = 1
Then ‘More than one Main Units’
Else ‘’ End ) as Warning

最佳答案

一种选择是使用COUNT() OVER()来计算主要单元，按类别划分；

SELECT Order_No, Category, Type,
  CASE WHEN COUNT(CASE WHEN Type='Main Unit' THEN 1 ELSE NULL END)
            OVER (PARTITION BY Category) > 1
       THEN 'More than one Main Units' ELSE '' END Warning
FROM MyTable

An SQLfiddle to test with。

关于sql - 如何在组中查找重复项-SQL 2008，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20809456/