这个如何实现表A
id,A, B, C
1,101, aaa, bbb
1,101, ccc, ddd
2,101, eee, fff
1,102, sss, hhh得到下面的数据:
A, B, C, A, B, C
101, aaa, bbb,101, eee, fff
101, ccc, ddd
102, sss, hhh----
以ID来区分,ID为1的放在左边,同类的(A)ID为2的放在右边,2者得到相比较的数据,怎么实现??
id,A, B, C
1,101, aaa, bbb
1,101, ccc, ddd
2,101, eee, fff
1,102, sss, hhh得到下面的数据:
A, B, C, A, B, C
101, aaa, bbb,101, eee, fff
101, ccc, ddd
102, sss, hhh----
以ID来区分,ID为1的放在左边,同类的(A)ID为2的放在右边,2者得到相比较的数据,怎么实现??
select px = identity(int,1,1) , * into n from a were id = 2select m.* , isnull(n.a,'') A , isnull(n.b,'') B , isnull(n.c,'') C
from m left join n on m.px = n.px
insert into dd select '1','101','aaa','bbb'
insert into dd select '1','101','ccc','ddd'
insert into dd select '2','101','eee','fff'
insert into dd select '1','102','sss','hhh'
insert into dd select '2','102','kkk','rrr'
大家测试用
id,A, B, C
1,101, aaa, bbb
1,101, ccc, ddd
2,101, eee, fff
1,102, sss, hhh 得到下面的数据:
A, B, C, A, B, C
101, aaa, bbb,101, eee, fff
101, ccc, ddd
102, sss, hhh ----
以ID来区分,ID为1的放在左边,同类的(A)ID为2的放在右边,2者得到相比较的数据,怎么实现?? -------------------
上面看错.你101ID为2有多个咋办?
insert into dd select '1','101','aaa','bbb'
insert into dd select '1','101','ccc','ddd'
insert into dd select '2','101','eee','fff'
insert into dd select '1','102','sss','hhh'
insert into dd select '2','102','kkk','rrr'
goselect isnull(m.A , '') A , isnull(m.B , '') B , isnull(m.C , '') C,
isnull(n.A , '') A , isnull(n.B , '') B , isnull(n.C , '') C
from
(
select t2.* , px = (select count(1) from
(
select * from dd where id = 1
) t1
where A = t2.A and B < t2.B) + 1 from
(
select * from dd where id = 1
) t2
) m
full join
(
select t2.* , px = (select count(1) from
(
select * from dd where id = 2
) t1
where A = t2.A and B < t2.B) + 1 from
(
select * from dd where id = 2
) t2
) n
on m.A = n.A and m.px = n.pxdrop table dd/*
A B C A B C
---------- ---------- ---------- ---------- ---------- ----------
101 aaa bbb 101 eee fff
101 ccc ddd
102 sss hhh 102 kkk rrr(所影响的行数为 3 行)
*/
结帖方式:管理帖子->给分->输入密码->结帖谢谢.