如何在一个组中找到副本- SQL 2008

I have a table (MyTable) with following data. (Order by Order_No,Category,Type)

我有一个包含以下数据的表(MyTable)。(按Order_No、类别、类型)

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   

What I need to do is, to scan through the table and see if any ‘Category’ has more than one ‘Main Unit’. If so, give a warning for the whole Category. Expected results should look like this.

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

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

I tried couple of ways (using subquery) to achieve results, but no luck. Please Help !!

我尝试了几种方法(使用子查询)来实现结果，但是没有运气。请帮助! !

(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

4 个解决方案

#1

One option would be using COUNT() OVER()to count the main units, partitioning by category;

一种选择是使用COUNT() /()来计算主要单元，按类别进行分区;

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.

一个用来测试的SQLfiddle。

#2

Another way is to use the CTE. Here is the sqlfiddle for this

另一种方法是使用CTE。这里是这个的sqlfiddle

   ;with categoriesWithMoreThanOneType as
   (
      select category, order_type
      from
        mytable
      where 
        order_type = 'Main Unit'
      group by category, order_type
      having count(1) > 1
   )

   select
     m.*, 
     case 
       when c.order_type is null then ''
       else
        'More than one ' + c.order_type 
     end as Warning
   from 
      mytable m 
      left outer join categoriesWithMoreThanOneType c on 
         m.category = c.category

#3

do you need to list all records?

你需要列出所有的记录吗?

If you only need the duplicates you could do something like this:

如果你只需要复制，你可以这样做:

select Order_No, Category, Type, count(*) as dupes
from MyTable
where Type='Main Unit'
group by Order_No, Category, Type
having count(*)>1
order by count(*) DESC;

#4

just another option, using EXISTS

只是另一个选项，使用存在

select [Order], _No, Category, Type, 
    (case when exists (
        select 1 from mytable t2
        Where category = 'main unit'
        and t2.[Order] = t.[Order] and t2._No = t._No
        group by [Order]
        having Count(*) > 1)
     then 'More than one Main Units'
     else '' end) as Warning
From MyTable t