mysql - 如何将相同ID的总和放入一个新列并显示在该ID的每一行上？

Question

我有如下表，称为 text_book ：

Ord_ID--- book_name--- 金额--- reg_time
01 --- abc1 --- 100 --- 2020/4/22 09:00
01 --- abc2 --- 50 --- 2020/4/22 09:01
01 --- abc3 --- 200 --- 2020/4/22 09:02
02 --- abc1 --- 100 --- 2020/4/22 09:00
02 --- abc2 --- 50 --- 2020/4/22 09:01
|
|...

我想要如下 sql 的输出：

Ord_ID--- book_name--- amount--- reg_time ---------Total
01 --- abc1 --- 100 --- 2020/4/22 09:00---350
01 --- abc2 --- 50 --- 2020/4/22 09:01
01 --- abc3 --- 200 --- 2020/4/22 09:02
02 --- abc1 --- 100 --- 2020/4/22 09:00---150
02 --- abc2 --- 50 --- 2020/4/22 09:01
|
|...

标签： mysql

Answer 1

首先生成一个行号，然后加入一个总数，然后根据行号决定打印什么

DROP TABLE IF EXISTS T;

CREATE TABLE T (Ord_ID int, book_name varchar(10), amount int, reg_time datetime);
insert into t values
(01    , 'abc1'      , 100   , str_to_date('2020/04/22 09:00', '%Y/%m/%d %H:%i')),
(01    , 'abc2'      ,  50   , str_to_date('2020/04/22 09:01', '%Y/%m/%d %H:%i')),
(01    , 'abc3'      , 200   , str_to_date('2020/04/22 09:02', '%Y/%m/%d %H:%i')),
(02    , 'abc1'      , 100   , str_to_date('2020/04/22 09:00', '%Y/%m/%d %H:%i')),
(02    , 'abc2'      ,  50   , str_to_date('2020/04/22 09:01', '%Y/%m/%d %H:%i'));

select a.ord_id,a.book_name,a.amount,a.reg_time,
         case when a.rn = 1 then a.total
         else '' 
         end as total
from
(
select t.*, s.total
         , row_number() over (partition by ord_id  order by book_name) rn
from t 
join
(select ord_id ,sum(amount) total from t group by ord_id) s on s.ord_id = t.ord_id
) a;

+--------+-----------+--------+---------------------+-------+
| ord_id | book_name | amount | reg_time            | total |
+--------+-----------+--------+---------------------+-------+
|      1 | abc1      |    100 | 2020-04-22 09:00:00 | 350   |
|      1 | abc2      |     50 | 2020-04-22 09:01:00 |       |
|      1 | abc3      |    200 | 2020-04-22 09:02:00 |       |
|      2 | abc1      |    100 | 2020-04-22 09:00:00 | 150   |
|      2 | abc2      |     50 | 2020-04-22 09:01:00 |       |
+--------+-----------+--------+---------------------+-------+
5 rows in set (0.002 sec)