SQL-使用同一表中现有的两行之和插入新行

Question

我正在努力编写一个有效的SQL脚本，该脚本从同一个表中提取两行，并通过复制除使用两行之和的一列之外的任何原始行中的值，将新行插入同一表中。

表看起来像这样，结果插入我想得到

--------------------------------------------------           
| activity | place | begin_date | end_date | price |        
--------------------------------------------------         -------------------------------------------
| running  | gym   | 2020-6-1  | 2020-7-1  |  10   |   >   | sports | gym | 2020-6-1 | 2020-7-1 | 30 |
---------------------------------------------------        -------------------------------------------
| jumping  | gym   | 2020-6-1  | 2020-7-1  |  20   |
---------------------------------------------------

问题是我需要按3列(地点、开始日期和结束日期(分组，因为我有很多不同的地点以及开始和结束日期。我只想把跑步和跳跃活动结合到体育运动中，所有其他活动都不受影响。

Answer 1

关键是您要将一个case语句放入"activity"的select语句中，然后使用SUM((函数聚合价格。它看起来像这个

INSERT INTO dbo.Table
( activity
, place
, begin_date
, end_date
, price
)
SELECT CASE WHEN activity = 'running' OR activity = 'jumping' THEN 'sports' ELSE activity END
, place
, begin_date
, end_date
, SUM(price)
FROM dbo.Table t
GROUP BY CASE WHEN activity = 'running' OR activity = 'jumping' THEN 'sports' ELSE activity END
, place
, begin_date
, end_date

Answer 2

这应该可以做到：

INSERT INTO table (activity, place, begin_date, end_date, price)
SELECT 'sport', [place], [begin_date], [end_date], SUM([price])
FROM table
WHERE activity = 'jumping' OR activity = 'running'
GROUP BY place, begin_date, end_date