SELECT * FROM #T T WHERE NOT EXISTS(SELECT 1 FROM #T WHERE Category=T.Category
AND subcategory=R.subcategory AND answer=T.answer AND ID<T.ID)
AND subcategory=R.subcategory AND answer=T.answer AND ID<T.ID)
调试欢乐多
create table #t
(
ID int,
Category varchar(10),
SubCategory varchar(10),
Answer varchar(10)
)
GO
insert into #t values(1,'A','B','C')
insert into #t values(1,'A','C','D')
insert into #t values(1,'A','B','D')
insert into #t values(2,'B','C','D')
insert into #t values(2,'B','E','D')
insert into #t values(3,'C','E','F')
insert into #t values(3,'C','E','D')
insert into #t values(4,'C','E','F')
insert into #t values(4,'C','E','D')
insert into #t values(5,'B','C','D')
insert into #t values(5,'B','E','D')
insert into #t values(6,'D','C','A')
insert into #t values(6,'D','E','F')
SELECT * FROM #T T WHERE NOT EXISTS(SELECT 1 FROM #T WHERE Category=T.Category
AND subcategory=T.subcategory AND answer=T.answer AND ID<T.ID)
DROP TABLE #T
/*ID Category SubCategory Answer
----------- ---------- ----------- ----------
1 A B C
1 A C D
1 A B D
2 B C D
2 B E D
3 C E F
3 C E D
6 D C A
6 D E F
*/
min(number) as number,
Category,
subcategory,
answer
from
#t
group by
Category,
subcategory,
answer
category,subcategory,answer相同时,取ID最小?
AND subcategory=R.subcategory AND answer=T.answer ) group by id