接着wycfgq大哥的例子问个查询

Question

表里的数据
  
  机台名   产量
    aa     100
    aa     100
    aa     150
    bb     100
    bb     120
    cc     100要求查询结果
    aa     100
    aa     100
    aa     150
    aa     350
    bb     100
    bb     120
    bb     220
    cc     100
    cc     100就是在每个行后面加上一个总数的统计
create table test(机台号 varchar(100),产量 int)
insert test select 'aa',100
insert test select 'aa',100
insert test select 'aa',150
insert test select 'bb',100
insert test select 'bb',120
insert test select 'cc',100

Answer 1

create table test(机台号 varchar(100),产量 int)
insert test select 'aa',100
insert test select 'aa',100
insert test select 'aa',150
insert test select 'bb',100
insert test select 'bb',120
insert test select 'cc',100select 机台号,产量 from (
select *,0 as lev from test
union all
select 机台号,sum(产量), 1 as lev from test group by 机台号
) a
order by 机台号,lev机台号                                                                                                           产量
---------------------------------------------------------------------------------------------------- -----------
aa                                                                                                           100
aa                                                                                                           100
aa                                                                                                           150
aa                                                                                                           350
bb                                                                                                           100
bb                                                                                                           120
bb                                                                                                           220
cc                                                                                                           100
cc                                                                                                           100(9 行受影响)

Answer 2

啊 学习了 
呵呵 太感谢了发散下考虑 Group能不能做到呢

Answer 3

补充下 我是想说直接用grouping 在一个select中能否做到

Answer 4

select 机台号,产量 from (
select 机台号,sum(产量) as 产量,grouping(产量) as gnt
from  test
group by 机台号,产量
with rollup) a
where 机台号 is not null
order by 机台号,gnt机台号                                                                                                           产量
---------------------------------------------------------------------------------------------------- -----------
aa                                                                                                           200
aa                                                                                                           150
aa                                                                                                           350
bb                                                                                                           100
bb                                                                                                           120
bb                                                                                                           220
cc                                                                                                           100
cc                                                                                                           100(8 行受影响)

Answer 5

create table test(机台号 varchar(100),产量 int)
insert test select 'aa',100
insert test select 'aa',100
insert test select 'aa',150
insert test select 'bb',100
insert test select 'bb',120
insert test select 'cc',100
goselect * from test
union all
select 机台号 , sum(产量) 产量 from test group by 机台号
order by 机台号drop table test
/*
机台号                                                                                                  产量          
---------------------------------------------------------------------------------------------------- ----------- 
aa                                                                                                   100
aa                                                                                                   100
aa                                                                                                   150
aa                                                                                                   350
bb                                                                                                   220
bb                                                                                                   100
bb                                                                                                   120
cc                                                                                                   100
cc                                                                                                   100（所影响的行数为 9 行）*/