id 是汇总的依据,其中,a,b,c为一些信息字段,
也要求输出这两个似乎有点矛盾
也要求输出这两个似乎有点矛盾
解决方案 »
- sql2000+sql2005开发版附加数据库时失败
- 求助
- 分组统计
- 多对一 查询(简单,本人愚钝,散分了啊 )
- SQL求数据统计问题
- VS2008 ADO 连接 SQL Server 2005 字符串问题
- 比较头痛的问题,麻烦大家帮忙
- SQLServer2000 + Win2003 cpu不稳定,到100%就不动了。求救!!
- SQL server 中我的资源数据都是年月日小时分钟秒,如何转化为该天0时0分0秒?
- 有需要Delphi 5开发人员指南的吗??
- 又是``分组``` 聚合, 临时表中的聚合,想了好几天了,没解~?(50分)
- 我在查询分析器里执行一个存储过程,系统提示警告,请问什么原因引起了?
1 11 111 12 13 15 a1 b1 c1
1 11 111 15 15 16 a1 b1 c1
2 22 222 14 15 6 a2 b2 c2
3 33 333 22 33 55 a3 b3 c3汇总,
id3汇总一次,
取出总值,
然后,在此基础上,或者重新汇总,
按id2汇总,输出结果,
同理,再汇总id1结果为:
id1, id2, id3, num1, num2, num3, a, b, c
1 27 28 31
1 11 27 28 31
1 11 111 12 13 15 a1 b1 c1
1 11 111 15 15 16 a1 b1 c1
2 14 15 6
2 22 14 15 6
2 22 222 14 15 6 a2 b2 c2
3 22 33 55
3 33 22 33 55
3 33 333 22 33 55 a3 b3 c3如果能把abc也输出,是最好了,
SELECT * from TDEMOid1, id2, id3, num1, num2, num3, a, b, c id
1 11 111 12 13 15 a1 b1 c1 1
1 11 111 15 15 16 a1 b1 c1 2
2 22 222 14 15 6 a2 b2 c2 3
3 33 333 22 33 55 a3 b3 c3 4(所影响的行数为 4 行)
其中列“id”为自增长Key字段查询语句:
Select * from
(Select id, id1, id2, id3, num1 = sum(num1), num2 = sum(num2),
num3 = sum(num3), a, b, c
from TDemo
where id2 Is Not Null
group by a, b, c, id1, id2, id3, id with rollup) v
where id1 is not null and a is not null and b is not null and c is not null
and (id2 is not null and id2 is not null)结果为:
id, id1, id2, id3, num1, num2, num3, a, b, c
1 1 11 111 12 13 15 a1 b1 c1
2 1 11 111 12 13 15 a1 b1 c1
1 11 111 27 28 31 a1 b1 c1
1 11 27 28 31 a1 b1 c1
3 2 22 222 14 15 6 a2 b2 c2
2 22 222 14 15 6 a2 b2 c2
2 22 14 15 6 a2 b2 c2
4 3 33 333 22 33 55 a3 b3 c3
3 33 333 22 33 55 a3 b3 c3
3 33 22 33 55 a3 b3 c3
Select * from
(Select id, id1, id2, id3, num1 = sum(num1), num2 = sum(num2), num3 = sum(num3), a, b, c
from TDemo
group by a, b, c, id1, id2, id3, id with rollup) v
where id1 is not null and a is not null and b is not null and c is not null那如果num1取当前最大值时且定长时,
取汇总值也可以用这个方法。
如果不定长,那就要动态执行SQL语句了,
如果是这样,语句写出来就很哆嗦,建议重新设计表的结构,将表拆分
不明白为什么要这么写...
select *,3 as type from tablename
union all
select id1,id2,null as id3,sum(num1),sum(num2),sum(num3),'' as a,'' as b,'' as c,2 as type from tablename group by id1,id2
union all
select id1,null as id2,null as id3,sum(num1),sum(num2),sum(num3),'' as a,'' as b,'' as c,1 as type from tablename group by id1
) as x
order by id1,type,id2,id3