if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (字段A int,字段B int,字段C int,字段D int,字段E int)
insert into [TB]
select 1,1,1,0,0 union all
select 2,1,1,2,2 union all
select 3,1,1,2,3 union all
select 4,1,1,0,0 union all
select 5,1,1,2,2 union all
select 6,1,1,4,4select * from [TB];WITH TT
AS(
SELECT ROW_NUMBER() OVER(PARTITION BY 字段D,字段E ORDER BY 字段A) AS NO,*
FROM dbo.TB)SELECT 字段A ,字段B ,字段C,字段D ,字段E FROM TT WHERE no =1/*
字段A 字段B 字段C 字段D 字段E
1 1 1 0 0
2 1 1 2 2
3 1 1 2 3
6 1 1 4 4*/
go
create table [TB] (字段A int,字段B int,字段C int,字段D int,字段E int)
insert into [TB]
select 1,1,1,0,0 union all
select 2,1,1,2,2 union all
select 3,1,1,2,3 union all
select 4,1,1,0,0 union all
select 5,1,1,2,2 union all
select 6,1,1,4,4select * from [TB];WITH TT
AS(
SELECT ROW_NUMBER() OVER(PARTITION BY 字段D,字段E ORDER BY 字段A) AS NO,*
FROM dbo.TB)SELECT 字段A ,字段B ,字段C,字段D ,字段E FROM TT WHERE no =1/*
字段A 字段B 字段C 字段D 字段E
1 1 1 0 0
2 1 1 2 2
3 1 1 2 3
6 1 1 4 4*/
select * from [TB];WITH TT AS( SELECT ROW_NUMBER() OVER(PARTITION BY 字段D,字段E ORDER BY 字段A) AS NO,* FROM dbo.TB)
吗?
AS(
SELECT ROW_NUMBER() OVER(PARTITION BY 字段D,字段E ORDER BY 字段A) AS NO,*
FROM dbo.TB)
SELECT 字段A ,字段B ,字段C,字段D ,字段E FROM TT WHERE no =1--这里的TT就是上面生成的结果,你完全可以用临时表或者表变量来替代,仅限你这里的例子
如果with不好理解,那就这样写吧select * from (
SELECT ROW_NUMBER() OVER(PARTITION BY 字段D,字段E ORDER BY 字段A) AS sn,*
FROM dbo.TB) tt where tt.sn=1
create table ca
(字段A int,字段B int,字段C int,字段D int,字段E int)insert into ca
select 1,1,1,0,0 union all
select 2,1,1,2,2 union all
select 3,1,1,2,3 union all
select 4,1,1,0,0 union all
select 5,1,1,2,2 union all
select 6,1,1,4,4
-- SQL语句
select *
from ca a
where not exists
(select 1 from ca b
where b.字段A<a.字段A and b.字段D=a.字段D and b.字段E=a.字段E)-- 结果
/*
字段A 字段B 字段C 字段D 字段E
----------- ----------- ----------- ----------- -----------
1 1 1 0 0
2 1 1 2 2
3 1 1 2 3
6 1 1 4 4(4 row(s) affected)
*/