SQL中如何对别名列final_price实现两种形式的汇总统计

现有如下SQL查询语句：

select 
   product_name, 
   (select bid_price 
    from bids 
    where bid_id = current_bid_id) as final_price 
from items 
where close_date > '2023/01/01' 
  and close_date < '2023/02/01';

当前输出结果：

product_name final_price
  ball          20
  bat           30
hockey_stick    50

期望得到两种输出形式之一：
形式一（添加总计行）：

product_name final_price
  ball          20
  bat           30
hockey_stick    50
  total         100

形式二（每行显示总计）：

product_name final_price total
  ball          20        100
  bat           30        100
hockey_stick    50        100

由于final_price是别名列，不清楚如何实现上述需求。

形式一：添加总计行 #

方法1：UNION ALL 拼接总计行 #

先查询基础数据，再单独计算总计行并拼接：

-- 查询基础数据
select 
   product_name, 
   (select bid_price from bids where bid_id = current_bid_id) as final_price 
from items 
where close_date > '2023/01/01' 
  and close_date < '2023/02/01'

union all

-- 计算总计行
select 
   'total' as product_name, 
   sum((select bid_price from bids where bid_id = current_bid_id)) as final_price 
from items 
where close_date > '2023/01/01' 
  and close_date < '2023/02/01';

方法2：使用ROLLUP（适用于MySQL 8+、PostgreSQL、SQL Server等） #

先用CTE封装基础数据，再通过ROLLUP自动生成总计行：

with base_data as (
    select 
       product_name, 
       (select bid_price from bids where bid_id = current_bid_id) as final_price 
    from items 
    where close_date > '2023/01/01' 
      and close_date < '2023/02/01'
)
select 
    coalesce(product_name, 'total') as product_name,
    sum(final_price) as final_price
from base_data
group by rollup(product_name);

ROLLUP会自动生成总计行，coalesce用于将总计行的product_name字段的NULL值替换为'total'。

形式二：每行显示总计值 #

使用窗口函数SUM() OVER()直接计算全局总和，无需重复子查询：

with base_data as (
    select 
       product_name, 
       (select bid_price from bids where bid_id = current_bid_id) as final_price 
    from items 
    where close_date > '2023/01/01' 
      and close_date < '2023/02/01'
)
select 
    product_name,
    final_price,
    sum(final_price) over() as total
from base_data;

sum(final_price) over()会对整个结果集计算总和，因此每行都会显示这个总计值。如果不想用CTE，也可以直接在原查询中重复子查询逻辑：

select 
   product_name, 
   (select bid_price from bids where bid_id = current_bid_id) as final_price,
   sum((select bid_price from bids where bid_id = current_bid_id)) over() as total
from items 
where close_date > '2023/01/01' 
  and close_date < '2023/02/01';

内容的提问来源于stack exchange，提问作者Rezzy