求一个使用mysql进行小计的语句

[桾楉叁杄]

现有表如下

UserID GroupName limits1 limits2 limit3
1 组1 1 2 3
2 组1 4 5 6
3 组2 7 8 9
4 组2 10 11 12

然后用语句执行后的结果为

UserID GroupName limits1 limits2 limit3
1 组1 1 2 3
2 组1 4 5 6
小计 组1 5 7 9
3 组2 7 8 9
4 组2 10 11 12
小计 组2 17 19 21

求此语句 要Mysql的

[桾楉叁杄]

[Quote=引用 1 楼 acmain_chm 的回复:]

select * from 现有表
union all
select 'UserID',GroupName,sum(limits1),sum(limits2),sum(limits3) from 现有表
[/Quote]

这个只是算总计的，不能算小计吧？

[vipper23]

mysql> select ifnull(userid,'小计') ,groupname,sum(limits1),sum(limits2),sum(limit3) from tt where groupname='组1' group by userid with rollup
    -> union all
    -> select ifnull(userid,'小计') ,groupname,sum(limits1),sum(limits2),sum(limit3) from tt where groupname='组2' group by userid with rollup
    -> ;
+-----------------------+-----------+--------------+--------------+-------------+
| ifnull(userid,'小计')    | groupname | sum(limits1) | sum(limits2) | sum(limit3)|
+-----------------------+-----------+--------------+--------------+-------------+
| 1                     | 组1         |            1 |            2 |           3 |
| 2                     | 组1         |            4 |            5 |           6 |
| 小计                     | 组1         |            5 |            7 |           9 |
| 3                     | 组2         |            7 |            8 |           9 |
| 4                     | 组2         |           10 |           11 |          12 |
| 小计                     | 组2         |           17 |           19 |          21 |
+-----------------------+-----------+--------------+--------------+-------------+
6 rows in set (0.00 sec)

这样？

[桾楉叁杄]

[Quote=引用 3 楼 vipper23 的回复:]

SQL code
mysql> select ifnull(userid,'小计') ,groupname,sum(limits1),sum(limits2),sum(limit3) from tt where groupname='组1' group by userid with rollup
-> union all
-> select ifnull(userid,'小计'……
[/Quote]

怎的特接近，但是如果我都不知道我有多少个组，都叫什么的话 我该怎么求呢？

oracle 能求出来我知道

[shine333]

SELECT UserID, IF(ord=2,'',GroupName) AS GroupName, limits1, limits2, limits3
  FROM
(
SELECT UserID, GroupName, limits1, limits2, limits3, 0 AS ord
  FROM tbl
 UNION ALL
SELECT '小计' AS UserID, GroupName, sum(limits1) AS limits1, sum(limits2) AS limits2, sum(limits3) AS limits3, 1 AS ord
  FROM tbl
 GROUP BY GroupName
 UNION ALL
SELECT '合计' AS UserID, MAX(GroupName), sum(limits1) AS limits1, sum(limits2) AS limits2, sum(limits3) AS limits3, 2 AS ord
  FROM tbl
) x
 ORDER BY x.GroupName, ord, UserID

'1', '组1', 1, 2, 3
'2', '组1', 4, 5, 6
'小计', '组1', 5, 7, 9
'3', '组2', 7, 8, 9
'4', '组2', 10, 11, 12
'小计', '组2', 17, 19, 21
'合计', '', 22, 26, 30
帮你加了个合计

[ACMAIN_CHM]

select * from 现有表
union all
select 'UserID',GroupName,sum(limits1),sum(limits2),sum(limits3) from 现有表