这样一个表,itemcode一样的,所对应的itemlot可能有1个或者2个或者N个,我要怎么把itemcode所对应的超过3个的itemlot的所有记录找出来。
itemcode sizecode itemlot
------------------------- -------- ---------------
*A 08 02-B1154
*D 12 03-B1125
*E 12 06-B3847
*C 14 04-B3172
*C 14 05-B1302
*C 14 05-B3884
*B 14 06-B3429
*B 14 06-B3430
*B 14 06-B3547
*B 14 06-B3848
*B 14 99-B2787
itemcode sizecode itemlot
------------------------- -------- ---------------
*A 08 02-B1154
*D 12 03-B1125
*E 12 06-B3847
*C 14 04-B3172
*C 14 05-B1302
*C 14 05-B3884
*B 14 06-B3429
*B 14 06-B3430
*B 14 06-B3547
*B 14 06-B3848
*B 14 99-B2787
DECLARE @T TABLE
(ItemCode CHAR(3),
SizeCode CHAR(2),
ItemLot VARCHAR(10))INSERT INTO @T
SELECT '*A', '08', '02-B1154 '
UNION ALL SELECT '*D', '12', '03-B1125'
UNION ALL SELECT'*E','12','06-B3847'
UNION ALL SELECT '*C','14','05-B1302'
UNION ALL SELECT '*C','14','05-B1302'
UNION ALL SELECT '*C', '14', '06-B3429'
UNION ALL SELECT 'B', '14', '06-B3429'
UNION ALL SELECT 'B', '14', '06-B3429'
UNION ALL SELECT 'B', '14', '06-B3429'
UNION ALL SELECT 'B', '14', '06-B3848 '
UNION ALL SELECT 'B', '14', '99-B2787'
SELECT ItemCode, ItemLot
FROM @T
GROUP BY ItemCode, ItemLot
HAVING COUNT(*)>2
DECLARE @T TABLE
(ItemCode CHAR(3),
SizeCode CHAR(2),
ItemLot VARCHAR(10))INSERT INTO @T
SELECT '*A', '08', '02-B1154 '
UNION ALL SELECT '*D', '12', '03-B1125'
UNION ALL SELECT'*E','12','06-B3847'
UNION ALL SELECT '*C','14','05-B1302'
UNION ALL SELECT '*C','14','05-B1302'
UNION ALL SELECT '*C', '14', '06-B3429'
UNION ALL SELECT 'B', '14', '06-B3429'
UNION ALL SELECT 'B', '14', '06-B3429'
UNION ALL SELECT 'B', '14', '06-B3429'
UNION ALL SELECT 'B', '14', '06-B3848 '
UNION ALL SELECT 'B', '14', '99-B2787'--代码
select ta.itemlot from
(
select itemlot,count(itemcode) [itemcode] from @T
group by itemlot
having count(itemcode)>3
)Ta
--结果 itemlot
----------
06-B3429
set nocount onDECLARE @T TABLE
(ItemCode CHAR(3),
SizeCode CHAR(2),
ItemLot VARCHAR(10))INSERT INTO @T
SELECT '*A', '08', '02-B1154 '
UNION ALL SELECT '*D', '12', '03-B1125'
UNION ALL SELECT'*E','12','06-B3847'
UNION ALL SELECT '*C','14','05-B1302'
UNION ALL SELECT '*C','14','05-B1302'
UNION ALL SELECT '*C', '14', '06-B3429'
UNION ALL SELECT 'B', '14', '06-B3429'
UNION ALL SELECT 'B', '14', '06-B3429'
UNION ALL SELECT 'B', '14', '06-B3429'
UNION ALL SELECT 'B', '14', '06-B3848 '
UNION ALL SELECT 'B', '14', '99-B2787'--代码
select * from @T where itemcode in
(
select itemcode
from @T
group by itemcode
having count(1)>3
)
--结果
ItemCode SizeCode ItemLot
-------- -------- ----------
B 14 06-B3429
B 14 06-B3429
B 14 06-B3429
B 14 06-B3848
B 14 99-B2787
(ItemCode CHAR(3),
SizeCode CHAR(2),
ItemLot VARCHAR(10))INSERT INTO @T
SELECT '*A', '08', '02-B1154 '
UNION ALL SELECT '*D', '12', '03-B1125'
UNION ALL SELECT'*E','12','06-B3847'
UNION ALL SELECT '*C','14','05-B1302'
UNION ALL SELECT '*C','14','05-B1302'
UNION ALL SELECT '*C', '14', '06-B3429'
UNION ALL SELECT 'B', '14', '06-B3429'
UNION ALL SELECT 'B', '14', '06-B3429'
UNION ALL SELECT 'B', '14', '06-B3429'
UNION ALL SELECT 'B', '14', '06-B3848 '
UNION ALL SELECT 'B', '14', '99-B2787'SELECT DISTINCT * FROM @T T1 WHERE EXISTS
(SELECT ItemCode, ItemLot FROM @T T2
WHERE T1.ItemCode=T2.ItemCode AND T1.SizeCode=T2.SizeCode AND T1.ItemLot=T2.ItemLot
GROUP BY ItemCode, ItemLot HAVING COUNT(*)>2)