复杂分组条件查询
zarge 2006-12-02 05:45:29 最近脑筋不好使,只好请大家帮忙想,分不够的再加
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
*/