declare @a table(id int,code varchar(20), provide varchar(45))-- (说明:这是AA表里的记录,分别表示自动编号ID、代号、公司名) insert @a select 1, '001', '天山公司' union all select 2 ,'002', '白云公司' union all select 3 ,'003' ,'天山公司' union all select 5 ,'002' ,'白云公司'declare @b table(id int ,productname varchar(20), total float) --(说明:这是BB表里的记录,分别表示来自AA表的ID号、商品名称、小计) insert @b select 1 ,'可乐', 10.50 union all select 1 ,'雪碧', 29.50 union all select 2 ,'白少软', 30.00 union all select 3 ,'暴花米', 10.00 union all select 5 ,'小糊涂仙', 50.00 union all select 3 ,'可乐', 25.00 union all select 3 ,'雪碧', 10.00select code,case when code is null then null else provide end provide,productname,num from (select top 100 * from (select top 100 percent min(code) as code,provide,null productname,null num from @a a group by provide union all select top 100 percent null code, a.provide,productname,sum(total) num from @a a Inner Join @b b on a.id=b.id group by a.provide,productname order by a.provide) c order by provide desc,productname) d
TO chuifengde() 为什么去掉top 100 percent 和 top 100 语句出错了,不需要这个TOP的,请指点
insert @a select 1, '001', '天山公司'
union all select 2 ,'002', '白云公司'
union all select 3 ,'003' ,'天山公司'
union all select 5 ,'002' ,'白云公司'declare @b table(id int ,productname varchar(20), total float) --(说明:这是BB表里的记录,分别表示来自AA表的ID号、商品名称、小计)
insert @b select 1 ,'可乐', 10.50
union all select 1 ,'雪碧', 29.50
union all select 2 ,'白少软', 30.00
union all select 3 ,'暴花米', 10.00
union all select 5 ,'小糊涂仙', 50.00
union all select 3 ,'可乐', 25.00
union all select 3 ,'雪碧', 10.00select code,case when code is null then null else provide end provide,productname,num from (select top 100 * from (select top 100 percent min(code) as code,provide,null productname,null num from @a a group by provide
union all
select top 100 percent null code, a.provide,productname,sum(total) num from @a a Inner Join @b b on a.id=b.id group by a.provide,productname order by a.provide) c
order by provide desc,productname) d