表A
ITEM CARD PRICE
A1 3000 108 表B
ITEM CARD BLINE PRICE
A1 3000 0 86.4
A1 3000 1 75.6表C
BLINE CLINE AMOUNT PRICE
0 0 5 75.6
0 1 10 64.8
1 0 15 54
1 1 20 43.2现在的SQL语句
SELECT T1.ITEM,T1.CARD,T1.PRICE,
T2.ITEM,T2.CARD,T2.BLINE,T2.PRICE,
T3.ITEM,T3.CARD,T3.BLINE,T3.CLINE,T3.AMOUNT,T3.PRICE
FROM OSPP T1
LEFT JOIN SPP1 T2 ON T2.CARD = T1.CARD AND T2.ITEM = T1.ITEM
LEFT JOIN SPP2 T3 ON T3.CARD = T2.CARD AND T3.ITEM = T2.ITEM AND T3.BLINE = T2.BLINE这样执行后得到
ITEM CARD PRICE ITEM CARD BLINE PRICE BLINE CLINE AMOUNT PRICE
A1 3000 108 A1 3000 0 86.4 0 0 5 75.6
A1 3000 108 A1 3000 0 86.4 0 1 10 64.8
A1 3000 108 A1 3000 1 75.6 1 0 15 54
A1 3000 108 A1 3000 1 75.6 1 1 20 43.2这样的结果看起来,不直观。
我想变成
ITEM CARD PRICE ITEM CARD BLINE PRICE BLINE CLINE AMOUNT PRICE
A1 3000 108 A1 3000 0 86.4 0 0 5 75.6
0 1 10 64.8
3000 1 75.6 1 0 15 54
1 1 20 43.2
这样可行吗?之前遇到的是两表连接left join,做成这样是会了,但是这三表连接就又蒙了
ITEM CARD PRICE
A1 3000 108 表B
ITEM CARD BLINE PRICE
A1 3000 0 86.4
A1 3000 1 75.6表C
BLINE CLINE AMOUNT PRICE
0 0 5 75.6
0 1 10 64.8
1 0 15 54
1 1 20 43.2现在的SQL语句
SELECT T1.ITEM,T1.CARD,T1.PRICE,
T2.ITEM,T2.CARD,T2.BLINE,T2.PRICE,
T3.ITEM,T3.CARD,T3.BLINE,T3.CLINE,T3.AMOUNT,T3.PRICE
FROM OSPP T1
LEFT JOIN SPP1 T2 ON T2.CARD = T1.CARD AND T2.ITEM = T1.ITEM
LEFT JOIN SPP2 T3 ON T3.CARD = T2.CARD AND T3.ITEM = T2.ITEM AND T3.BLINE = T2.BLINE这样执行后得到
ITEM CARD PRICE ITEM CARD BLINE PRICE BLINE CLINE AMOUNT PRICE
A1 3000 108 A1 3000 0 86.4 0 0 5 75.6
A1 3000 108 A1 3000 0 86.4 0 1 10 64.8
A1 3000 108 A1 3000 1 75.6 1 0 15 54
A1 3000 108 A1 3000 1 75.6 1 1 20 43.2这样的结果看起来,不直观。
我想变成
ITEM CARD PRICE ITEM CARD BLINE PRICE BLINE CLINE AMOUNT PRICE
A1 3000 108 A1 3000 0 86.4 0 0 5 75.6
0 1 10 64.8
3000 1 75.6 1 0 15 54
1 1 20 43.2
这样可行吗?之前遇到的是两表连接left join,做成这样是会了,但是这三表连接就又蒙了
如果要产生一个树状结构,需要在上面的程序中处理。
insert into @A
select 'A1',3000,108
declare @B table(ITEM varchar(2),CARD int, BLINE int, PRICE float(1))
insert into @B
select 'A1', 3000, 0, 86.4 union all
select 'A1', 3000, 1, 75.6
declare @C table(BLINE int, CLINE int, AMOUNT int, PRICE float(1))
insert into @C
select 0, 0, 5, 75.6 union all
select 0, 1, 10, 64.8 union all
select 1, 0, 15, 54 union all
select 1, 1, 20, 43.2
;with cte as
(
SELECT row_number() over(partition by a.ITEM ,a.CARD,a.PRICE,b.ITEM,b.CARD order by a.ITEM) rn1,
row_number() over(partition by a.ITEM ,a.CARD,a.PRICE,b.ITEM,b.CARD,b.BLINE order by a.ITEM) rn2,
a.ITEM ITEM_a,a.CARD CARD_a,a.PRICE PRICE_a,
b.ITEM ITEM_b,b.CARD CARD_b,b.BLINE BLINE_b,b.PRICE PRICE_b,
c.BLINE BLINE_c,c.CLINE CLINE_c,c.AMOUNT AMOUNT_c,c.PRICE PRICE_c
FROM @A a
LEFT JOIN @B b ON a.CARD = b.CARD AND a.ITEM = b.ITEM
LEFT JOIN @C c ON b.BLINE = c.BLINE
)
--select * from cte
select ITEM_a,ltrim(CARD_a) CARD_a,ltrim(PRICE_a) PRICE_a,ITEM_b,ltrim(CARD_b) CARD_b,
ltrim(BLINE_b) BLINE_b,ltrim(PRICE_b) PRICE_b,ltrim(BLINE_c) BLINE_c,CLINE_c,AMOUNT_c,PRICE_c
from cte where rn1=1 and rn2=1
union all
select '','','','','',ltrim(BLINE_b) BLINE_b,ltrim(PRICE_b) PRICE_b,BLINE_c,CLINE_c,AMOUNT_c,PRICE_c
from cte where rn1>1 and rn2=1
union all
select '','','','','','','',BLINE_c,CLINE_c,AMOUNT_c,PRICE_c
from cte where rn1>1 and rn2>1
/*
ITEM_a CARD_a PRICE_a ITEM_b CARD_b BLINE_b PRICE_b BLINE_c CLINE_c AMOUNT_c PRICE_c
------ ------------ ------------ ------ ------------ ------------ ----------------------- ----------- ----------- ----------- -------------
A1 3000 108 A1 3000 0 86.4 0 0 5 75.6
1 75.6 1 0 15 54
0 1 10 64.8
1 1 20 43.2*/