求一条sql语句

Question

(select * from table1 )union all 
(select 编号,'平均',日期,数量1=avg(数量1),数量2=avg(数量2) from table1 group by 编号,日期)

Answer 1

select * from (
select 编号,代码,日期,数量1,数量2
from tb
union all
select 编号+(平均),'',日期,sum(数量1),sum(数量2)
from tb group by  编号,日期)aa order by 编号,日期

Answer 2

经测试准确
declare  @b table(编号 varchar(10),代码 VARCHAR(10),日期 datetime,数量1 real,数量2 real)
insert into @b 
select '001','001001','2005-9-1',1, 2 union
select '001','001002','2005-9-1',2, 3 union
select '001','001003','2005-9-1',2, 4 union
select '001','001001','2005-9-3',1, 2 union
select '001','001002','2005-9-3',2, 3 union
select '001','001003','2005-9-3',2, 4 union
select '002','002001','2005-9-1',4, 3 union
select '002','002002','2005-9-1',3, 3 union
select '002','002001','2005-9-20',4,3 union
select '002','002002','2005-9-20',3,3select * from @b  a union all 
(select 编号,'平均',日期,数量1=sum(数量1)/2,数量2=sum(数量2)/2 from @b group by 编号,日期 ) 
order by 编号,日期,代码

Answer 3

declare  @b table(编号 varchar(10),代码 VARCHAR(10),日期 datetime,数量1 real,数量2 real)
insert into @b 
select '001','001001','2005-9-1',1, 2 union
select '001','001002','2005-9-1',2, 3 union
select '001','001003','2005-9-1',2, 4 union
select '001','001001','2005-9-3',1, 2 union
select '001','001002','2005-9-3',2, 3 union
select '001','001003','2005-9-3',2, 4 union
select '002','002001','2005-9-1',4, 3 union
select '002','002002','2005-9-1',3, 3 union
select '002','002001','2005-9-20',4,3 union
select '002','002002','2005-9-20',3,3--查询
select *
from (
        select * from @b
        union
        select 编号
               ,'平均'
               ,日期
               ,sum(数量1)/2 
               ,sum(数量2)/2 
        from @b
        group by 编号,日期
     )t
order by 编号,日期--结果
/*编号         代码         日期                         数量1        数量2   
---------- ---------- ----------------------------------------------------
001        001001     2005-09-01 00:00:00.000          1.0         2.0
001        001002     2005-09-01 00:00:00.000          2.0         3.0
001        001003     2005-09-01 00:00:00.000          2.0         4.0
001        平均         2005-09-01 00:00:00.000        2.5         4.5
001        001001     2005-09-03 00:00:00.000          1.0         2.0
001        001002     2005-09-03 00:00:00.000          2.0         3.0
001        001003     2005-09-03 00:00:00.000          2.0         4.0
001        平均         2005-09-03 00:00:00.000        2.5         4.5
002        002001     2005-09-01 00:00:00.000          4.0         3.0
002        002002     2005-09-01 00:00:00.000          3.0         3.0
002        平均         2005-09-01 00:00:00.000        3.5         3.0
002        002001     2005-09-20 00:00:00.000          4.0         3.0
002        002002     2005-09-20 00:00:00.000          3.0         3.0
002        平均         2005-09-20 00:00:00.000        3.5         3.0（所影响的行数为 14 行）
*/