select '产品一',名称,计量单位,单价,数量,销售ID,备注 from A union all select '产品二',名称,计量单位,单价,数量,销售ID,备注 from B union all select '产品三',名称,计量单位,单价,数量,销售ID,备注 from C
select '产品一',数量 from A where 销售id='' union all select '产品二',数量 from B where 销售id='' union all select '产品三',数量from C where 销售id=''
select t.销售ID, sum(case when pn=1 then 1 else 0 end) '产品一数量', sum(case when pn=2 then 1 else 0 end) '产品二数量', sum(case when pn=3 then 1 else 0 end) '产品三数量' from (select 1 'pn',* from 产品一 union all select 2 'pn',* from 产品二 union all select 3 'pn',* from 产品三) t group by t.销售ID
select 销售ID,sum(产品一数量) as 产品一数量,sum(产品二数量) as 产品二数量,sum(产品三数量) as 产品三数量 from ( select 销售ID,sum(数量) as 产品一数量,cast(0 as int) as 产品二数量,cast(0 as int) as 产品三数量 from 产品一 group by 销售ID union all select 销售ID,0 as 产品一数量,sum(数量) as 产品二数量,cast(0 as int) as 产品三数量 from 产品二 group by 销售ID union all select 销售ID,0 as 产品一数量,cast(0 as int) as 产品二数量,sum(数量) as 产品三数量 from 产品三 group by 销售ID ) as t group by 销售ID
union all
select '产品二',名称,计量单位,单价,数量,销售ID,备注 from B
union all
select '产品三',名称,计量单位,单价,数量,销售ID,备注 from C
union all
select '产品二',数量 from B where 销售id=''
union all
select '产品三',数量from C where 销售id=''
select t.销售ID,
sum(case when pn=1 then 1 else 0 end) '产品一数量',
sum(case when pn=2 then 1 else 0 end) '产品二数量',
sum(case when pn=3 then 1 else 0 end) '产品三数量'
from
(select 1 'pn',* from 产品一
union all
select 2 'pn',* from 产品二
union all
select 3 'pn',* from 产品三) t
group by t.销售ID
(
select 销售ID,sum(数量) as 产品一数量,cast(0 as int) as 产品二数量,cast(0 as int) as 产品三数量 from 产品一
group by 销售ID
union all
select 销售ID,0 as 产品一数量,sum(数量) as 产品二数量,cast(0 as int) as 产品三数量 from 产品二
group by 销售ID
union all
select 销售ID,0 as 产品一数量,cast(0 as int) as 产品二数量,sum(数量) as 产品三数量 from 产品三
group by 销售ID
) as t
group by 销售ID