日期:2014-05-17 浏览次数:20465 次
create table #tmp(
合同号 nvarchar(100),
产品ID nvarchar(100),
日期 datetime
)
INSERT INTO #tmp
SELECT 'XSHT000619','电脑','2013-10-31' UNION ALL
SELECT 'XSHT000619','主板','2013-10-31' UNION ALL
SELECT 'XSHT000619','机箱','2013-10-31' UNION ALL
SELECT 'XSHT000620','硬盘','2013-10-31' UNION ALL
SELECT 'XSHT000620','鼠标','2013-10-31' UNION ALL
SELECT 'XSHT000620','显示器','2013-10-31' UNION ALL
SELECT 'XSHT000618','桌子','2013-10-31' UNION ALL
SELECT 'XSHT000618','桌子','2013-09-30' UNION ALL
SELECT 'XSHT000620','硬盘','2013-09-30'
;WITH a1 AS
(
SELECT DISTINCT [合同号],[产品ID],CONVERT(CHAR(7),[日期],20) ym
FROM #tmp
)
,a2 AS
(
SELECT [合同号],[产品ID]
FROM a1
GROUP BY [合同号],[产品ID]
HAVING COUNT(*)>1
)
SELECT a.*
FROM #tmp a
INNER JOIN a2 b ON a.[合同号]=b.[合同号] AND a.[产品ID]=b.[产品ID]
--如果這樣跨年也行:
SELECT a.*
FROM #tmp a LEFT JOIN #tmp b ON a.合同号=b.合同号 AND a.产品ID=b.产品ID
WHERE CONVERT(CHAR(7),a.[日期],20)<>CONVERT(CHAR(7),b.[日期],20)
ORDER BY 合同号,产品ID,日期
select t1.*
from #tmp t1
where exists(select 1
from (
select a.合同号,a.产品ID,convert(varchar(7),日期,120) as 日期
from #tmp a
inner join
(select 合同号,产品ID,COUNT(*) as num
from #tmp
group by 合同号,产品ID
having COUNT(*)>1
)b on a.合同号=b.合同号 and a.产品ID=b.产品ID
group by a.合同号,a.产品ID,convert(varchar(7),日期,120)
having COUNT(*)=1
)t2 where t1.合同号=t2.合同号 and t1.产品ID=t2.产品ID)
/*
合同号 产品ID 日期
XSHT000620 硬盘 2013-10-31 00:00:00.000
XSHT000618 桌子 2013-10-31 00:00:00.000
XSHT000618 桌子 2013-09-30 00:00:00.000
XSHT000620 硬盘 2013-09-30 00:00:00.000
*/
create table #tmp(
合同号 nvarchar(100),
产品ID nvarchar(100),
日期 datetime
)
INSERT INTO #tmp
SELECT 'XSHT000619',