复杂分组条件查询

[zarge]

最近脑筋不好使，只好请大家帮忙想，分不够的再加

declare @tbl table (
id int identity(1, 1),
name varchar(10),
d1 int,
d2 int,
a1 int,
a2 int )

insert into @tbl(name,d1,d2,a1,a2)values('t0',0,0,0,0)
insert into @tbl(name,d1,d2,a1,a2)values('t0',0,0,0,0)
insert into @tbl(name,d1,d2,a1,a2)values('t1',10,10,11,10)
insert into @tbl(name,d1,d2,a1,a2)values('t1',10,11,10,10)
insert into @tbl(name,d1,d2,a1,a2)values('t2',20,20,20,20)
insert into @tbl(name,d1,d2,a1,a2)values('t2',20,21,20,20)
insert into @tbl(name,d1,d2,a1,a2)values('t3',30,30,30,30)
insert into @tbl(name,d1,d2,a1,a2)values('t3',30,30,30,31)
insert into @tbl(name,d1,d2,a1,a2)values('t4',40,41,41,40)
insert into @tbl(name,d1,d2,a1,a2)values('t4',41,41,40,41)

-- select * from @tbl
/*
分组的条件除了要求name相同外，只要满足以下任一条件即可
1. d1,a1相同
2. d1,a2相同
3. d2,a1相同
4. d2,a2相同
*/

-- 结果如下，d1,d2,a1,a2取分组中任意一条记录的值即可
/*
name d1 d2 a1 a2
t0 0 0 0 0
t1 10 10 11 10
t2 20 20 20 20
t3 30 30 30 30
t4 40 41 41 40
t4 41 41 40 41
*/

[tx1icenhe]

很简单

declare @tbl table (
id int identity(1, 1),
name varchar(10),
d1 int,
d2 int,
a1 int,
a2 int )

insert into @tbl(name,d1,d2,a1,a2)values('t0',0,0,0,0)
insert into @tbl(name,d1,d2,a1,a2)values('t0',0,0,0,0)
insert into @tbl(name,d1,d2,a1,a2)values('t1',10,10,11,10)
insert into @tbl(name,d1,d2,a1,a2)values('t1',10,11,10,10)
insert into @tbl(name,d1,d2,a1,a2)values('t2',20,20,20,20)
insert into @tbl(name,d1,d2,a1,a2)values('t2',20,21,20,20)
insert into @tbl(name,d1,d2,a1,a2)values('t3',30,30,30,30)
insert into @tbl(name,d1,d2,a1,a2)values('t3',30,30,30,31)
insert into @tbl(name,d1,d2,a1,a2)values('t4',40,41,41,40)
insert into @tbl(name,d1,d2,a1,a2)values('t4',41,41,40,41)

select * from @tbl a
where not exists (
select 1 from @tbl
where name=a.name
and id<a.id
and ((a1=a.a1 and d1=a.d1) or (a1=a.a1 and d2=a.d2) or (a2=a.a2 and d1=a.d1) or (a2=a.a2 and d2=a.d2))
)

[tx1icenhe]

--结果
id name d1 d2 a1 a2
----------- ---------- ----------- ----------- ----------- -----------
1 t0 0 0 0 0
3 t1 10 10 11 10
5 t2 20 20 20 20
7 t3 30 30 30 30
9 t4 40 41 41 40
10 t4 41 41 40 41

（所影响的行数为 6 行）

[akuzou]

楼上的不错!
楼主可能想偏了.

[zarge]

呵呵，确实是想偏了，揭帖