表test 字段 a1 a2 a3 a4 a5 a6 a7 或更多
测试数据：
a1 a2 a3 a4 a5 a6 a7
a c d e 1 2 3
a c d e 2 3 4
a b v e 1 2 3
c c d e 1 2 3
c c d e 2 4 3
b b d e 1 3 4
b b d e 2 3 4
按照 a1 a2 a3 a4 分组 统计 a5 a6 a7 总和
结果按a5 a6 a7所得的值高低排序
a1 a2 a3 a4 a5总和 a6总和 a7总和
a c d e 3 5 7
a b v e 1 2 3
b b d e 3 5 7
c c d e 3 5 7
寻求一些这种类似的分组统计计算sql写法，减少编程时大量的代码处理
9 个解决方案
#1
这样？
select a1,a2,a3,a4,sum(a5+a6+a7) a567
from test
group by a1,a2,a3,a4
order by a567 desc
select a1,a2,a3,a4,sum(a5+a6+a7) a567
from test
group by a1,a2,a3,a4
order by a567 desc
#2
还是这样？
select a1,a2,a3,a4,sum(a5) a5,sum(a6) a6,sum(a7) a7
from test
group by a1,a2,a3,a4
order by a5 desc , a6 desc , a7 desc
select a1,a2,a3,a4,sum(a5) a5,sum(a6) a6,sum(a7) a7
from test
group by a1,a2,a3,a4
order by a5 desc , a6 desc , a7 desc
#3
select a1,a2,a3,a4,sum(a5) [a5总和],sum(a6) [a6总和],sum(a7) [a7总和]
from [table]
group by a1,a2,a3,a4
order by [a5总和] desc,[a6总和] desc,[a7总和] desc
#4
类似这样，不过这是简单的实例，能有复杂些的么，能够充分发挥 group by， order by,having,where等的
#5
看明白了，是这样吧？
create table tb(a1 varchar(10),a2 varchar(10),a3 varchar(10),a4 varchar(10),a5 int,a6 int,a7 int)
insert into tb values('a', 'c' ,'d', 'e', 1, 2, 3)
insert into tb values('a', 'c' ,'d', 'e', 2, 3, 4)
insert into tb values('a', 'b' ,'v', 'e', 1, 2, 3)
insert into tb values('c', 'c' ,'d', 'e', 1, 2, 3)
insert into tb values('c', 'c' ,'d', 'e', 2, 4, 3)
insert into tb values('b', 'b' ,'d', 'e', 1, 3, 4)
insert into tb values('b', 'b' ,'d', 'e', 2, 3, 4)
go
select a1,a2,a3,a4,sum(a5) a5,sum(a6) a6,sum(a7) a7
from TB
group by a1,a2,a3,a4
order by sum(a5+a6+A7) desc
drop table tb
/*
a1 a2 a3 a4 a5 a6 a7
---------- ---------- ---------- ---------- ----------- ----------- -----------
b b d e 3 6 8
c c d e 3 6 6
a c d e 3 5 7
a b v e 1 2 3
（所影响的行数为 4 行）
*/
#6
在这上面改些条件看看，比如论坛的积分制度，管理员怎么去管理积分表，怎么看比较清晰明了
#7
具体点，论坛有不同的用户组，不同的版块，不同的栏目，不同栏目有不同的基础分数，这样看看
#8
-- z=这个?
create table #t(a int,b int,c int,d int,e int)
insert into #t values(1,2,3,4,5)
insert into #t values(1,2,3,4,6)
insert into #t values(1,2,3,4,7)
insert into #t values(1,2,3,4,8)
insert into #t values(1,3,3,4,5)
insert into #t values(1,3,3,4,6)
insert into #t values(1,3,3,4,8)
insert into #t values(1,3,3,4,7)
insert into #t values(2,2,2,4,5)
insert into #t values(2,2,3,4,6)
insert into #t values(2,2,4,4,7)
insert into #t values(2,2,5,4,8)
insert into #t values(2,3,6,4,5)
insert into #t values(2,3,3,4,6)
insert into #t values(2,3,3,4,8)
insert into #t values(2,3,3,4,7)
select
case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, sum(c) as c,sum(d) as d,sum(e) as e
from
#t
group by
a,b
with rollup
having grouping(b)=0 or grouping(a)=1
select
case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, c, sum(d) as d,sum(e) as e
from
#t
group by
a,b,c
with rollup
having grouping(c)=0 or grouping(a)=1
select
case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b,
case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c,
sum(d) as d,sum(e) as e
from
#t
group by
a,b,c
with rollup
having grouping(a)=1 or grouping(b)=0
select
case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b, sum(c) as c, sum(d) as d,sum(e) as e from #t
group by
a,b,c
with rollup
having grouping(a)=1 or grouping(b)=1 or grouping(c)=0
drop table #t
/*a b c d e
------------------------------ ----------- ----------- ----------- -----------
1 2 12 16 26
1 3 12 16 26
2 2 14 16 26
2 3 15 16 26
合计 NULL 53 64 104
（所影响的行数为 5 行）
a b c d e
------------------------------ ----------- ----------- ----------- -----------
1 2 3 16 26
1 3 3 16 26
2 2 2 4 5
2 2 3 4 6
2 2 4 4 7
2 2 5 4 8
2 3 3 12 21
2 3 6 4 5
合计 NULL NULL 64 104
（所影响的行数为 9 行）
a b c d e
------------------------------ ----------- ------------------------------ ----------- -----------
1 2 3 16 26
1 2 小计 16 26
1 3 3 16 26
1 3 小计 16 26
2 2 2 4 5
2 2 3 4 6
2 2 4 4 7
2 2 5 4 8
2 2 小计 16 26
2 3 3 12 21
2 3 6 4 5
2 3 小计 16 26
合计 NULL NULL 64 104
（所影响的行数为 13 行）
a b c d e
------------------------------ ------------------------------ ----------- ----------- -----------
1 2 12 16 26
1 3 12 16 26
1 小计 24 32 52
2 2 2 4 5
2 2 3 4 6
2 2 4 4 7
2 2 5 4 8
2 3 9 12 21
2 3 6 4 5
2 小计 29 32 52
合计 NULL 53 64 104
（所影响的行数为 11 行）
*/
DECLARE @T TABLE (序号 int,数据 varchar(3))
INSERT INTO @T
SELECT 1,'x1' UNION ALL
SELECT 2,'x2' UNION ALL
SELECT 3,'x3' UNION ALL
SELECT 4,'x4' UNION ALL
SELECT 5,'x5' union all
select 6,'x6' union all
SELECT 7,'x7' UNION ALL
SELECT 8,'x8' UNION ALL
SELECT 9,'x9' UNION ALL
SELECT 10,'x10' UNION ALL
SELECT 11,'x11' UNION ALL
SELECT 12,'x12'
SELECT
*
FROM
(SELECT * FROM @T WHERE ((序号 - 1) / 3) % 2 = 0) AS A
LEFT JOIN
(SELECT * FROM @T WHERE ((序号 - 1) / 3) % 2 = 1 ) AS B
ON
(A.序号 - 1) / 3 = (B.序号 - 1) / 3 -1 AND (A.序号 - 1) % 3 = (B.序号 - 1) % 3
/*序号 数据 序号 数据
----------- ---- ----------- ----
1 x1 4 x4
2 x2 5 x5
3 x3 6 x6
7 x7 10 x10
8 x8 11 x11
9 x9 12 x12
(6 行受影响)
*/
#9
该回复于2010-11-10 15:21:42被版主删除
#1
这样？
select a1,a2,a3,a4,sum(a5+a6+a7) a567
from test
group by a1,a2,a3,a4
order by a567 desc
select a1,a2,a3,a4,sum(a5+a6+a7) a567
from test
group by a1,a2,a3,a4
order by a567 desc
#2
还是这样？
select a1,a2,a3,a4,sum(a5) a5,sum(a6) a6,sum(a7) a7
from test
group by a1,a2,a3,a4
order by a5 desc , a6 desc , a7 desc
select a1,a2,a3,a4,sum(a5) a5,sum(a6) a6,sum(a7) a7
from test
group by a1,a2,a3,a4
order by a5 desc , a6 desc , a7 desc
#3
select a1,a2,a3,a4,sum(a5) [a5总和],sum(a6) [a6总和],sum(a7) [a7总和]
from [table]
group by a1,a2,a3,a4
order by [a5总和] desc,[a6总和] desc,[a7总和] desc
#4
类似这样，不过这是简单的实例，能有复杂些的么，能够充分发挥 group by， order by,having,where等的
#5
看明白了，是这样吧？
create table tb(a1 varchar(10),a2 varchar(10),a3 varchar(10),a4 varchar(10),a5 int,a6 int,a7 int)
insert into tb values('a', 'c' ,'d', 'e', 1, 2, 3)
insert into tb values('a', 'c' ,'d', 'e', 2, 3, 4)
insert into tb values('a', 'b' ,'v', 'e', 1, 2, 3)
insert into tb values('c', 'c' ,'d', 'e', 1, 2, 3)
insert into tb values('c', 'c' ,'d', 'e', 2, 4, 3)
insert into tb values('b', 'b' ,'d', 'e', 1, 3, 4)
insert into tb values('b', 'b' ,'d', 'e', 2, 3, 4)
go
select a1,a2,a3,a4,sum(a5) a5,sum(a6) a6,sum(a7) a7
from TB
group by a1,a2,a3,a4
order by sum(a5+a6+A7) desc
drop table tb
/*
a1 a2 a3 a4 a5 a6 a7
---------- ---------- ---------- ---------- ----------- ----------- -----------
b b d e 3 6 8
c c d e 3 6 6
a c d e 3 5 7
a b v e 1 2 3
（所影响的行数为 4 行）
*/
#6
在这上面改些条件看看，比如论坛的积分制度，管理员怎么去管理积分表，怎么看比较清晰明了
#7
具体点，论坛有不同的用户组，不同的版块，不同的栏目，不同栏目有不同的基础分数，这样看看
#8
-- z=这个?
create table #t(a int,b int,c int,d int,e int)
insert into #t values(1,2,3,4,5)
insert into #t values(1,2,3,4,6)
insert into #t values(1,2,3,4,7)
insert into #t values(1,2,3,4,8)
insert into #t values(1,3,3,4,5)
insert into #t values(1,3,3,4,6)
insert into #t values(1,3,3,4,8)
insert into #t values(1,3,3,4,7)
insert into #t values(2,2,2,4,5)
insert into #t values(2,2,3,4,6)
insert into #t values(2,2,4,4,7)
insert into #t values(2,2,5,4,8)
insert into #t values(2,3,6,4,5)
insert into #t values(2,3,3,4,6)
insert into #t values(2,3,3,4,8)
insert into #t values(2,3,3,4,7)
select
case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, sum(c) as c,sum(d) as d,sum(e) as e
from
#t
group by
a,b
with rollup
having grouping(b)=0 or grouping(a)=1
select
case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, c, sum(d) as d,sum(e) as e
from
#t
group by
a,b,c
with rollup
having grouping(c)=0 or grouping(a)=1
select
case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b,
case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c,
sum(d) as d,sum(e) as e
from
#t
group by
a,b,c
with rollup
having grouping(a)=1 or grouping(b)=0
select
case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b, sum(c) as c, sum(d) as d,sum(e) as e from #t
group by
a,b,c
with rollup
having grouping(a)=1 or grouping(b)=1 or grouping(c)=0
drop table #t
/*a b c d e
------------------------------ ----------- ----------- ----------- -----------
1 2 12 16 26
1 3 12 16 26
2 2 14 16 26
2 3 15 16 26
合计 NULL 53 64 104
（所影响的行数为 5 行）
a b c d e
------------------------------ ----------- ----------- ----------- -----------
1 2 3 16 26
1 3 3 16 26
2 2 2 4 5
2 2 3 4 6
2 2 4 4 7
2 2 5 4 8
2 3 3 12 21
2 3 6 4 5
合计 NULL NULL 64 104
（所影响的行数为 9 行）
a b c d e
------------------------------ ----------- ------------------------------ ----------- -----------
1 2 3 16 26
1 2 小计 16 26
1 3 3 16 26
1 3 小计 16 26
2 2 2 4 5
2 2 3 4 6
2 2 4 4 7
2 2 5 4 8
2 2 小计 16 26
2 3 3 12 21
2 3 6 4 5
2 3 小计 16 26
合计 NULL NULL 64 104
（所影响的行数为 13 行）
a b c d e
------------------------------ ------------------------------ ----------- ----------- -----------
1 2 12 16 26
1 3 12 16 26
1 小计 24 32 52
2 2 2 4 5
2 2 3 4 6
2 2 4 4 7
2 2 5 4 8
2 3 9 12 21
2 3 6 4 5
2 小计 29 32 52
合计 NULL 53 64 104
（所影响的行数为 11 行）
*/
DECLARE @T TABLE (序号 int,数据 varchar(3))
INSERT INTO @T
SELECT 1,'x1' UNION ALL
SELECT 2,'x2' UNION ALL
SELECT 3,'x3' UNION ALL
SELECT 4,'x4' UNION ALL
SELECT 5,'x5' union all
select 6,'x6' union all
SELECT 7,'x7' UNION ALL
SELECT 8,'x8' UNION ALL
SELECT 9,'x9' UNION ALL
SELECT 10,'x10' UNION ALL
SELECT 11,'x11' UNION ALL
SELECT 12,'x12'
SELECT
*
FROM
(SELECT * FROM @T WHERE ((序号 - 1) / 3) % 2 = 0) AS A
LEFT JOIN
(SELECT * FROM @T WHERE ((序号 - 1) / 3) % 2 = 1 ) AS B
ON
(A.序号 - 1) / 3 = (B.序号 - 1) / 3 -1 AND (A.序号 - 1) % 3 = (B.序号 - 1) % 3
/*序号 数据 序号 数据
----------- ---- ----------- ----
1 x1 4 x4
2 x2 5 x5
3 x3 6 x6
7 x7 10 x10
8 x8 11 x11
9 x9 12 x12
(6 行受影响)
*/
#9
该回复于2010-11-10 15:21:42被版主删除