可以实现。 但不建议用SQL来实现,这个功能应该是用户界面层的,应该由程序来完成。mysql> select * from mytest; +----+--------+-------+ | id | userid | total | +----+--------+-------+ | 7 | 10 | 12 | | 8 | 10 | 20 | | 9 | 10 | 40 | | 10 | 22 | 23 | | 11 | 22 | 28 | | 12 | 22 | 30 | +----+--------+-------+ 6 rows in set (0.00 sec)mysql> select userid,total,totalsum -> from ( -> select a.userid as k1,a.id as k2,if(a.id=b.min_id,a.userid,'') as userid,total,'' as totalsum ,0 as k3 -> from mytest a,(select userid,min(id) as min_id from mytest group by userid) b -> where a.userid=b.userid -> union all -> select userid,null,'','',sum(total) as totalsum,1 -> from mytest -> group by userid -> ) t -> order by k1,k3,k2; +--------+-------+----------+ | userid | total | totalsum | +--------+-------+----------+ | 10 | 12 | | | | 20 | | | | 40 | | | | | 72 | | 22 | 23 | | | | 28 | | | | 30 | | | | | 81 | +--------+-------+----------+ 8 rows in set (0.00 sec)mysql>
SELECT userid1,total,totalsum FROM ( SELECT IF(b.mi IS NULL,'',a.userid) AS userid1,a.userid,a.total,NULL AS totalsum,a.id FROM mytest a LEFT JOIN (SELECT userid,MIN(id) AS mi FROM mytest GROUP BY userid) b ON a.userid=b.userid AND a.id=b.mi UNION SELECT '',userid,'',SUM(total) ,10000 AS id FROM mytest GROUP BY userid) a ORDER BY userid,id
但不建议用SQL来实现,这个功能应该是用户界面层的,应该由程序来完成。mysql> select * from mytest;
+----+--------+-------+
| id | userid | total |
+----+--------+-------+
| 7 | 10 | 12 |
| 8 | 10 | 20 |
| 9 | 10 | 40 |
| 10 | 22 | 23 |
| 11 | 22 | 28 |
| 12 | 22 | 30 |
+----+--------+-------+
6 rows in set (0.00 sec)mysql> select userid,total,totalsum
-> from (
-> select a.userid as k1,a.id as k2,if(a.id=b.min_id,a.userid,'') as userid,total,'' as totalsum ,0 as k3
-> from mytest a,(select userid,min(id) as min_id from mytest group by userid) b
-> where a.userid=b.userid
-> union all
-> select userid,null,'','',sum(total) as totalsum,1
-> from mytest
-> group by userid
-> ) t
-> order by k1,k3,k2;
+--------+-------+----------+
| userid | total | totalsum |
+--------+-------+----------+
| 10 | 12 | |
| | 20 | |
| | 40 | |
| | | 72 |
| 22 | 23 | |
| | 28 | |
| | 30 | |
| | | 81 |
+--------+-------+----------+
8 rows in set (0.00 sec)mysql>
SELECT IF(b.mi IS NULL,'',a.userid) AS userid1,a.userid,a.total,NULL AS totalsum,a.id FROM mytest a LEFT JOIN (SELECT userid,MIN(id) AS mi FROM mytest GROUP BY userid) b
ON a.userid=b.userid AND a.id=b.mi
UNION
SELECT '',userid,'',SUM(total) ,10000 AS id FROM mytest GROUP BY userid) a
ORDER BY userid,id