id date note1 2001-1-1 test
2 2001-1-1 sjd
3 2002-3-3 fdfd
4 2002-3-3 fdfd
5 2003-3-3 fdfd
6 2004-3-3 asa
7 2004-3-3 fds===>
date note2001-1-1 null
2002-3-3 fdfd
2003-3-3 fdfd
2004-3-3 null
2 2001-1-1 sjd
3 2002-3-3 fdfd
4 2002-3-3 fdfd
5 2003-3-3 fdfd
6 2004-3-3 asa
7 2004-3-3 fds===>
date note2001-1-1 null
2002-3-3 fdfd
2003-3-3 fdfd
2004-3-3 null
相同日期 相同note 显示(对应分组日期)对应note(因为都相同)
相同日期 不同note 显示(对应分组日期)对应null(因为不同)
大体意思这个
select * from 表1,表2 where 表1.date =表2.date and 表1.note=表2.note
不知道行不行...
Select
Distinct
A.[date],
(Case When Exists(Select id From 表 Where [date] = A.[date] And note != A.note) Then Null Else A.note End) As note
From
表 A
Create Table TEST
(id Int,
[date] Varchar(10),
note Varchar(10))
--插入數據
Insert TEST Select 1, '2001-1-1', 'test'
Union All Select 2, '2001-1-1', 'sjd'
Union All Select 3, '2002-3-3', 'fdfd'
Union All Select 4, '2002-3-3', 'fdfd'
Union All Select 5, '2003-3-3', 'fdfd'
Union All Select 6, '2004-3-3', 'asa'
Union All Select 7, '2004-3-3', 'fds'
GO
--測試
Select
Distinct
A.[date],
(Case When Exists(Select id From TEST Where [date] = A.[date] And note != A.note) Then Null Else A.note End) As note
From
TEST A
GO
--刪除測試環境
Drop Table TEST
--結果
/*
date note
2001-1-1 NULL
2002-3-3 fdfd
2003-3-3 fdfd
2004-3-3 NULL
*/
--創建測試環境
Create Table TEST
(id Int,
[date] Varchar(10),
note Varchar(10))
--插入數據
Insert TEST Select 1, '2001-1-1', 'test'
Union All Select 2, '2001-1-1', 'sjd'
Union All Select 3, '2002-3-3', 'fdfd'
Union All Select 4, '2002-3-3', 'fdfd'
Union All Select 5, '2003-3-3', 'fdfd'
Union All Select 6, '2004-3-3', 'asa'
Union All Select 7, '2004-3-3', 'fds'
GO
--測試
Select
Distinct
A.[date],
(Case When B.id Is Not Null Then Null Else A.note End) As note
From
TEST A
Left Join
TEST B
On A.[date] = B.[date] And A.note != B.note
GO
--刪除測試環境
Drop Table TEST
--結果
/*
date note
2001-1-1 NULL
2002-3-3 fdfd
2003-3-3 fdfd
2004-3-3 NULL
*/
select distinct B.date, case when B.count>1 then 'null'
else A.note
end note
from Test A,(select count(distinct note) count,date from Test group by date) B
where A.date=B.date结果为
date note
2001-1-1 null
2001-1-2 fngd
2001-1-3 null