表以及如下
code order_id cl_sort
=========================
A1 1 11
A2 2 11
A3 3 11
A4 4 11
A5 5 11
B1 1 21
B2 2 22
B3 3 23
B5 5 21
============
我想将数据如下显示:
//1
以A为主 cl_sort显示A对应的值
code order_id cl_sort
=========================
1 A1 B1 11
2 A2 B2 11
3 A3 B3 11
4 A4 11
5 A5 B5 11
=========================
//2
以B为主 cl_sort显示B对应的值
code order_id cl_sort
=========================
1 A1 B1 21
2 A2 B2 22
3 A3 B3 23
4 A4 11
5 A5 B5 21
请问SQL语句应该怎么写??
code order_id cl_sort
=========================
A1 1 11
A2 2 11
A3 3 11
A4 4 11
A5 5 11
B1 1 21
B2 2 22
B3 3 23
B5 5 21
============
我想将数据如下显示:
//1
以A为主 cl_sort显示A对应的值
code order_id cl_sort
=========================
1 A1 B1 11
2 A2 B2 11
3 A3 B3 11
4 A4 11
5 A5 B5 11
=========================
//2
以B为主 cl_sort显示B对应的值
code order_id cl_sort
=========================
1 A1 B1 21
2 A2 B2 22
3 A3 B3 23
4 A4 11
5 A5 B5 21
请问SQL语句应该怎么写??
go
create table [tb]([code] varchar(2),[order_id] int,[cl_sort] int)
insert [tb]
select 'A1',1,11 union all
select 'A2',2,11 union all
select 'A3',3,11 union all
select 'A4',4,11 union all
select 'A5',5,11 union all
select 'B1',1,21 union all
select 'B2',2,22 union all
select 'B3',3,23 union all
select 'B5',5,21--//1
select
a.order_id,
a.code,
isnull(b.code,''),
a.cl_sort
from
(select * from tb where code like 'A%') a
left join
(select * from tb where code like 'B%') b
on
a.order_id=b.order_id/*
order_id code cl_sort
----------- ---- ---- -----------
1 A1 B1 11
2 A2 B2 11
3 A3 B3 11
4 A4 11
5 A5 B5 11(所影响的行数为 5 行)
*/--//2
select
a.order_id,
a.code,
isnull(b.code,'') ,
isnull(b.cl_sort,a.cl_sort)
from
(select * from tb where code like 'A%') a
left join
(select * from tb where code like 'B%') b
on
a.order_id=b.order_id
/**
order_id code
----------- ---- ---- -----------
1 A1 B1 21
2 A2 B2 22
3 A3 B3 23
4 A4 11
5 A5 B5 21(所影响的行数为 5 行)
**/