一句Sql能否解决这个问题

[月光下的土豆]

组大家准备点数据

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'

把以上数据整理成如下数据


整理原则，同一个合同的同一个产品，如果“日期”跨月就抓取出来

[發糞塗牆]

SELECT a.*--合同号,产品ID,日期 FROM #tmp a LEFT JOIN #tmp b ON a.合同号=b.合同号 AND a.产品ID=b.产品ID WHERE DATEPART(MONTH,a.日期)<>DATEPART(MONTH,b.日期)

[發糞塗牆]

SELECT a.*
FROM #tmp a LEFT JOIN  #tmp b ON a.合同号=b.合同号 AND a.产品ID=b.产品ID
WHERE DATEPART(MONTH,a.日期)<>DATEPART(MONTH,b.日期)
ORDER BY 合同号,产品ID,日期

/*
合同号                                                                                                  产品ID                                                                                                 日期
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- -----------------------
XSHT000618                                                                                           桌子                                                                                                   2013-09-30 00:00:00.000
XSHT000618                                                                                           桌子                                                                                                   2013-10-31 00:00:00.000
XSHT000620                                                                                           硬盘                                                                                                   2013-09-30 00:00:00.000
XSHT000620                                                                                           硬盘                                                                                                   2013-10-31 00:00:00.000
*/

[lzw_0736]

;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]

[lzw_0736]

--如果這樣跨年也行：
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,日期

[Andy__Huang]

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
*/

[LongRui888]

是这样吗：

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'


select 合同号,产品ID,日期
from
(
select *,
       count(*) over(partition by 合同号,产品ID) as rownum,
       min(日期) over(partition by 合同号,产品ID) as min_date,
       max(日期) over(partition by 合同号,产品ID) as max_date
from #tmp
)t
where rownum >= 2
      and datediff(month,min_date,max_date) >=1
/*
合同号	    产品ID	日期
XSHT000618	桌子	    2013-10-31 00:00:00.000
XSHT000618	桌子	    2013-09-30 00:00:00.000
XSHT000620	硬盘	    2013-09-30 00:00:00.000
XSHT000620	硬盘	    2013-10-31 00:00:00.000
*/

[詩和遠方]

自连接即可：

select a.[合同号],a.[产品ID],a.[日期]
from #tmp a inner join #tmp b 
on a.[合同号] = b.[合同号] and a.[产品ID] = b.[产品ID]
where datepart(mm,a.[日期]) <> datepart(mm,b.[日期])
order by a.[合同号]

/*
合同号         产品ID        日期
---------------------------------------------------
XSHT000618     桌子          2013-09-30 00:00:00.000
XSHT000618     桌子          2013-10-31 00:00:00.000
XSHT000620     硬盘          2013-09-30 00:00:00.000
XSHT000620     硬盘          2013-10-31 00:00:00.000

(4 行受影响)
*/

[ywx41769]

select * from #tmp where 合同号+产品id in(select iie from ( select RANK() over(partition by 合同号+产品id order by substring(convert(varchar(10),日期,120),6,2)) as iid, 合同号+产品id iie from #tmp ) a where iid>1) order by 合同号+产品id,日期 asc