问题如下:测试内容Date ID1 IDs1 Num1 X1 Sty1 ID2 IDs2 Num2 X2 Sty2
2011-08-05 NULL NULL .000 NULL NULL 2011080014 7 17.000 1 B
2011-08-05 NULL NULL .000 NULL NULL 2011080014 8 13.000 1 B
2011-08-05 2011070051 13 5.000 2 A NULL NULL .000 NULL NULL
2011-08-05 2011070051 14 10.000 2 A NULL NULL .000 NULL NULL
2011-08-05 2011070051 15 20.000 2 A NULL NULL .000 NULL NULL
2011-08-06 NULL NULL .000 NULL NULL 2011080014 8 13.000 1 B/*-----------想要的结果---------------*/
Date ID1 IDs1 Num1 X1 Sty1 ID2 IDs2 Num2 X2 Sty2
2011-08-05 2011070051 13 5.000 2 A 2011080014 7 17.000 1 B
2011-08-05 2011070051 14 10.000 2 A 2011080014 8 13.000 1 B
2011-08-05 2011070051 15 20.000 2 A NULL NULL .000 NULL NULL
2011-08-06 NULL NULL .000 NULL NULL 2011080014 8 13.000 1 B
2011-08-05 NULL NULL .000 NULL NULL 2011080014 7 17.000 1 B
2011-08-05 NULL NULL .000 NULL NULL 2011080014 8 13.000 1 B
2011-08-05 2011070051 13 5.000 2 A NULL NULL .000 NULL NULL
2011-08-05 2011070051 14 10.000 2 A NULL NULL .000 NULL NULL
2011-08-05 2011070051 15 20.000 2 A NULL NULL .000 NULL NULL
2011-08-06 NULL NULL .000 NULL NULL 2011080014 8 13.000 1 B/*-----------想要的结果---------------*/
Date ID1 IDs1 Num1 X1 Sty1 ID2 IDs2 Num2 X2 Sty2
2011-08-05 2011070051 13 5.000 2 A 2011080014 7 17.000 1 B
2011-08-05 2011070051 14 10.000 2 A 2011080014 8 13.000 1 B
2011-08-05 2011070051 15 20.000 2 A NULL NULL .000 NULL NULL
2011-08-06 NULL NULL .000 NULL NULL 2011080014 8 13.000 1 B
;with ach as
(
select *,rid=row_number() over (partition by date,id1 order by getdate())
from tb
)select date,max(id1) id1,max(ids1) ids1,max(Num1) Num1,max(X1) X1,max(Sty1) Sty1,
max(ID2) ID2,max(IDs2) IDs2,max(Num2) Num2,max(X2) X2,max(Sty2) Sty2
from ach
group by date,rid
--or;with ach as
(
select *,rid=row_number() over (partition by convert(varchar(10),date,120),id1 order by getdate())
from tb
)select convert(varchar(10),date,120) date,max(id1) id1,max(ids1) ids1,max(Num1) Num1,max(X1) X1,max(Sty1) Sty1,
max(ID2) ID2,max(IDs2) IDs2,max(Num2) Num2,max(X2) X2,max(Sty2) Sty2
from ach
group by convert(varchar(10),date,120),rid
end
改下,刚才激动了
还是激动了,再改下
使用max会将数据去掉,
但是,我要的是每笔数据都要出现.
Date ID1 IDs1 Num1 X1 Sty1 ID2 IDs2 Num2 X2 Sty2
2011-8-5 2011070051 13 5 2 A 2011080014 7 17 1 B
2011-8-5 2011070051 14 10 2 A NULL NULL 0 NULL NULL
2011-8-5 2011070051 15 20 2 A 2011080014 8 13 1 B
2011-8-6 NULL NULL 0 NULL NULL 2011080014 8 13 1 B
两个子查询都再分组排序,增加一个id字段,id=row_number() over (patition by Date order by getdate()),
两个子查询利用上面的date字段和id字段关联(full join),
关联后goup by date,isnull(a.id,b.id),
select 列表取最大值就可以了,
只能讲个思路,机器一双启动到win7+sql2005,没一会儿就死机,弄不了