[商品表]
sp_id 商品名
1 A
2 b
3 c
4 a
.........
26 z=============================================[已卖出商品表]
mc_id 商品名 价格
1 A 100
2 c 600
3 A 300
4 B 100
5 B 150
6 X 900=============================================
我如何得到以下格式数据????[统计表]
商品名 累记价格
X 900
C 600
A 400
B 250
D 0 <----D在[已卖出商品表]没有,所以为0
E 0
........比如商品名一共是 A-Z 个(要以“累记价格”从大到小排列,)
sp_id 商品名
1 A
2 b
3 c
4 a
.........
26 z=============================================[已卖出商品表]
mc_id 商品名 价格
1 A 100
2 c 600
3 A 300
4 B 100
5 B 150
6 X 900=============================================
我如何得到以下格式数据????[统计表]
商品名 累记价格
X 900
C 600
A 400
B 250
D 0 <----D在[已卖出商品表]没有,所以为0
E 0
........比如商品名一共是 A-Z 个(要以“累记价格”从大到小排列,)
from [商品表] a left join [已卖出商品表] b
on a.商品名=b.商品名
group by a.商品名
select a.商品名,isnull(sum(价格),0) as '累记价格'
from 商品表 a
left join 统计表 b on a.商品名=b.商品名
group by a.商品名
order by isnull(sum(价格),0) desc
from [商品表] a left join [已卖出商品表] b
on a.商品名=b.商品名
group by a.商品名
order by isnull(sum(b.价格),0) desc
left join
(select 商品名 , sum(价格) 累记价格 from 已卖出商品表 group by 商品名) n
on m.商品名 = n.商品名
order by n.累记价格 desc
insert into @a select 1,'a'
insert into @a select 2,'b'
insert into @a select 3,'c'
insert into @a select 4,'d'
insert into @a select 5,'e'
insert into @a select 6,'f'
insert into @a select 7,'g'
insert into @a select 8,'h'
insert into @a select 9,'i'
insert into @a select 10,'j'
insert into @a select 11,'x'declare @b table (mc_id int,name varchar(1),price int)
insert into @b select 1,'a',100
insert into @b select 2,'c',600
insert into @b select 3,'a',300
insert into @b select 4,'b',100
insert into @b select 5,'b',150
insert into @b select 6,'x',900--select * from @a a
select a.name,isnull(b.price,0) as price from @a a left join (
select name,sum(price) as price from @b b group by name)b
on a.name = b.name order by price desc
name price
x 900
c 600
a 400
b 250
d 0
e 0
f 0
g 0
h 0
i 0
j 0
from (
select 商品名,价格 from 已卖出商品表
union all
select 商品名,0 from 商品表
) a group by a.商品名
from [商品表] a left join [已卖出商品表] b
on a.商品名=b.商品名
group by a.商品名