select case when type=1 then Code else null end as Code,
case when type=1 then name else null end as name,
case when type=1 then size else null end as size,
case when type=1 then ut else null end as ut,
number1,comandy1,
number2,comandy2
from (
select distinct
Code,name,size,ut,
cast(null as int) as number1,cast(null as varchar(100)) as comandy1,
cast(null as int) as number2,cast(null as varchar(100)) as comandy2,
1 as type
from aa
union all
select
Code,name,size,ut,
sum(number1) as number1,comandy1,
null as number2,null as comandy2,
2 as type
from bb
group by Code,name,size,ut,comandy1
union all
select
Code,name,size,ut,
null as number1,null as comandy1
sum(number2) as number2,comandy2,
3 as type
from cc
group by Code,name,size,ut,comandy2
) as t
order by code,type
case when type=1 then name else null end as name,
case when type=1 then size else null end as size,
case when type=1 then ut else null end as ut,
number1,comandy1,
number2,comandy2
from (
select distinct
Code,name,size,ut,
cast(null as int) as number1,cast(null as varchar(100)) as comandy1,
cast(null as int) as number2,cast(null as varchar(100)) as comandy2,
1 as type
from aa
union all
select
Code,name,size,ut,
sum(number1) as number1,comandy1,
null as number2,null as comandy2,
2 as type
from bb
group by Code,name,size,ut,comandy1
union all
select
Code,name,size,ut,
null as number1,null as comandy1
sum(number2) as number2,comandy2,
3 as type
from cc
group by Code,name,size,ut,comandy2
) as t
order by code,type
case when type=1 then name else null end as name,
case when type=1 then size else null end as size,
case when type=1 then ut else null end as ut,
number1,comandy1,
number2,comandy2
from (
select
Code,name,size,ut,
cast(null as int) as number1,cast(null as varchar(100)) as comandy1,
cast(null as int) as number2,cast(null as varchar(100)) as comandy2,
1 as type
from aa
group by Code,name,size,ut
union all
select
Code,name,size,ut,
sum(number1) as number1,comandy1,
null as number2,null as comandy2,
2 as type
from bb
group by Code,name,size,ut,comandy1
union all
select
Code,name,size,ut,
null as number1,null as comandy1
sum(number2) as number2,comandy2,
3 as type
from cc
group by Code,name,size,ut,comandy2
) as t
order by code,type
不好意思,我的结果中忘了去掉number2 comandy2真正的结果如下
Code name size ut number1 comandy1
785100 开心果 250G 包
null null null null 10 供应商名1
00125 统一方便面 150g 包 null null
null null null null 20 供应商名2
null null null null 5 客户名1
null null null null 3 客户名2
F643433 恰恰香瓜子 150G 包 null null
null null null null 30 供应商名1
null null null null 3 客户名2
你的语句出来的结果就有点出入,这里看到的结果不对位,请把结果COPY到记事本一看是排得很整齐的,麻烦再帮改进一下,3Q
select case when type=1 then Code else null end as Code,
case when type=1 then name else null end as name,
case when type=1 then size else null end as size,
case when type=1 then ut else null end as ut,
number1,comandy1
from (
select
Code,name,size,ut,
cast(null as int) as number1,cast(null as varchar(100)) as comandy1,
1 as type
from aa
group by Code,name,size,ut
union all
select
Code,name,size,ut,
sum(number1) as number1,comandy1,
2 as type
from bb
group by Code,name,size,ut,comandy1
union all
select
Code,name,size,ut,
sum(number2) as number2,comandy2,
3 as type
from cc
group by Code,name,size,ut,comandy2
) as t
order by code,type
*
from (
select
Code,name,size,ut,
cast(null as int) as number1,cast(null as varchar(100)) as comandy1,
code as type1,
1 as type2
from aa
group by Code,name,size,ut
union all
select
null,null,null,null,
sum(number1) as number1,comandy1,
code as type1,
2 as type2
from bb
group by Code,name,size,ut,comandy1
union all
select
null,null,null,null,
sum(number2) as number2,comandy2,
code as type1,
3 as type2
from cc
group by Code,name,size,ut,comandy2
) as t
order by type1,type2
785100 开心果 250G 包
null null null null 10 供应商名1
00125 统一方便面 150g 包 null null
null null null null 20 供应商名2
null null null null 5 客户名1
null null null null 3 客户名2
F643433 恰恰香瓜子 150G 包 null null
null null null null 30 供应商名1
null null null null 3 客户名2
看到最后的语句出来的结果我改了改接近了,有个疑问请教,为什么BB和CC语句里,没有
BB.code = AA.code 和 CC.code = AA.code 这样的条件做关联,你只是只order by type1把code的相同记录排一起而以,这样好像不对吧?请教
你的语句为什么BB和CC语句里,没有BB.code = AA.code 和 CC.code = AA.code 这样的条件做关联,你只是只order by type1把code的相同记录排一起而以,这样出来的结果不对的问题没解决,请zicxc和大家再帮帮
*
from (
select
Code,name,size,ut,
cast(null as int) as number1,cast(null as varchar(100)) as comandy1,
code as type1,
1 as type2
from aa
group by Code,name,size,ut
union all
select
null,null,null,null,
number1,comandy1,
code as type1,
2 as type2
from bb
union all
select
null,null,null,null,
number2,comandy2,
code as type1,
3 as type2
from cc
) as t
order by type1,type2