SQL技术问询：如何将CONCAT函数中的31替换为所有不同州的计数

你需要将固定数字31替换为所有不同州的总数量，可以通过嵌套子查询动态获取这个总数，无需手动输入。修改后的SQL代码如下：

select
  concat(ceiling((count(prac.state)*100)/(select count(distinct state) from dbo.users)),'%') as Percentage
from(
    select
      us.state,
      round(sum (case when month(ord.order_date) <=6 and year(ord.order_date) = '2022' then prod.amount * ord.quantity else 0 end), 1) as rev_H1_22, 
      round(sum (case when month(ord.order_date) <=6 and year(ord.order_date) = '2023' then prod.amount * ord.quantity else 0 end), 1) as rev_H1_23
    from ((dbo.users as us
    join dbo.orders as ord on us.id = ord.user_id)
    join dbo.prods as prod on ord.product_id = prod.prod_id)
    group by us.state
) as Prac
where prac.rev_H1_22 > prac.rev_H1_23

关键改动说明 #

• 将原代码中的31替换为(select count(distinct state) from dbo.users)，该子查询会自动统计dbo.users表中所有不重复的州的数量，作为百分比计算的分母。
• 此方式能确保当州的数量发生变化时，无需手动修改SQL中的数字，始终保持统计结果的准确性。

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