现有表如下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的
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的
union all
select 'UserID',GroupName,sum(limits1),sum(limits2),sum(limits3) from 现有表
-> 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)
这样?
怎的特接近,但是如果我都不知道我有多少个组,都叫什么的话 我该怎么求呢?oracle 能求出来我知道
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
帮你加了个合计