复杂分组和结果集

Question

我需要测试两个条件：

1. 有多少mysku不具有“REP”的过度类型，但在table2 of mysku_catagory列中却有相同的类型。
2. 条件:有多少COMPETETOR_SKU存在于mysku柱中，不包含在COMPETETOR_SKU_CATAGORY中有多少种存在于table2中。

样本数据：

DECLARE @table1 TABLE
(
 MYSKU VARCHAR (20),    
 CATAGORY VARCHAR (20), 
 OVERTYPE VARCHAR (20), 
 COMPETETOR_SKU VARCHAR (20)
)

Insert @table1


SELECT 'AAA19-1405',    'Cold Air Intake',  'REP',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-1505',    'Cold Air Intake',  'REC',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-1508',    'Cold Air Intake',  'REP',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-2303',    'Cold Air Intake',  'REP',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-2305',    'Cold Air Intake',  'REC',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-2308',    'Cold Air Intake',  'REC',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-2405',    'Cold Air Intake',  'REC',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-4003',    'Cold Air Intake',  'REC',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-4005',    'Cold Air Intake',  'REP',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-4103',    'Cold Air Intake',  'REC',  'MCN8LTC8K' UNION ALL
SELECT 'MCN8LTC8K',    'Air Filter Wrap',  'REP',  'MCN8AWCC3' UNION ALL
SELECT 'MCN8LTC8K',    'Air Filter Wrap',  'REP',  'MCN8AWCC3' UNION ALL
SELECT 'MCN8LTC8K',    'Catchmehere'    ,  'REP',  'KZNWRTY65' UNION ALL
SELECT 'MCN8LTC8K',     'I M HERE TOO' ,  'REP',  'SDREWTY345'



declare @table2 table
(
mysku_catagory VARCHAR (50),    
COMPETETOR_SKU_CATAGORY VARCHAR (50)

)
INSERT @table2

SELECT 'Cold Air Intake',   'Air Filter%'

预期产出：

--'AAA19-1505', 'Cold Air Intake',  'REC',  'MCN8LTC8K'

--'AAA19-2305', 'Cold Air Intake',  'REC',  'MCN8LTC8K'

--'AAA19-2308', 'Cold Air Intake',  'REC',  'MCN8LTC8K'

--'AAA19-2405', 'Cold Air Intake',  'REC',  'MCN8LTC8K'

--'AAA19-4003', 'Cold Air Intake',  'REC',  'MCN8LTC8K'

--'AAA19-4103', 'Cold Air Intake',  'REC',  'MCN8LTC8K'

--'MCN8LTC8K',    'Catchmehere'    ,  'REP',  'KZNWRTY65'

--'MCN8LTC8K',     'I M HERE TOO' ,  'REP',  'SDREWTY345'

Answer 1

试着这样做：

SELECT *
FROM   @table1 AS t1
WHERE  ( t1.OVERTYPE <> 'REP'
         AND EXISTS(SELECT *
                    FROM   @table2 AS t2
                    WHERE  t2.mysku_catagory = t1.CATAGORY) )
        OR ( NOT EXISTS(SELECT *
                        FROM   @table2 AS t2
                        WHERE  t1.CATAGORY LIKE t2.COMPETETOR_SKU_CATAGORY)
             AND ( t1.OVERTYPE = 'REP'
                   AND NOT EXISTS(SELECT *
                                  FROM   @table2 AS t2
                                  WHERE  t2.mysku_catagory = t1.CATAGORY) ) )