行合并的问题,求解
假如有一张这样的表
orderId orderSummoney invoiceId invoiceDate invoiceMoney payId paydate payMoney
001 10000 FP001 2011-11-11 2000 NULL NULL NULL
001 10000 FP002 2011-11-12 3000 NULL NULL NULL
001 10000 FP003 2011-11-13 5000 NULL NULL NULL
001 10000 NULL NULL NULL P002 2011-11-12 3000
001 10000 NULL NULL NULL P003 2011-11-12 7000
我想到到这样一个结果
orderId orderSummoney invoiceId invoiceDate invoiceMoney payId payDate payMoney
001 10000 FP001 2011-11-11 2000 P002 2011-11-12 3000
001 10000 FP002 2011-11-12 3000 P003 2011-11-12 7000
001 10000 FP003 2011-11-13 5000 NULL NULL NULL
不知道怎么弄?
[解决办法]
create table t1( oid varchar(3), summoney int, iid varchar(20), idate datetime, imoney int, pid varchar(10), pdate datetime, pmoney int)insert into t1select '001', 10000, 'FP001', '2011-11-11', 2000, NULL, NULL, NULL union allselect '001', 10000, 'FP002', '2011-11-12', 3000, NULL, NULL, NULL union all select '001', 10000, 'FP003', '2011-11-13', 5000, NULL, NULL, NULL union allselect '001', 10000, NULL, NULL, NULL, 'P002', '2011-11-12', 3000 union allselect '001', 10000, NULL, NULL, NULL, 'P003', '2011-11-12', 7000select * from t1;with aaa as( select ROW_NUMBER() over(order by getdate()) as row1,* from t1 where pid is null and pdate is null and pmoney is null)select a.oid,a.summoney,a.iid,a.idate,a.imoney,b.pid,b.pdate,b.pmoney from aaa as a left join(select ROW_NUMBER() over(order by getdate()) as row2,* from t1 where iid is null and idate is null and imoney is null) as bon a.row1=b.row2-------------------------------------oid summoney iid idate imoney pid pdate pmoney001 10000 FP001 2011-11-11 00:00:00.000 2000 P002 2011-11-12 00:00:00.000 3000001 10000 FP002 2011-11-12 00:00:00.000 3000 P003 2011-11-12 00:00:00.000 7000001 10000 FP003 2011-11-13 00:00:00.000 5000 NULL NULL NULL
