时间戳

首页 > 解决方案 > 概括 CASE 语句（结果列的动态名称？）

sql - 概括 CASE 语句（结果列的动态名称？）

问题描述

我目前有这样的查询-

SELECT 
   SUM(CASE month WHEN 'January' THEN 1 ELSE 0 END) AS "Jan", 
   SUM(CASE month WHEN 'February' THEN 1 ELSE 0 END) AS "Feb", 
   SUM(CASE month WHEN 'March' THEN 1 ELSE 0 END) AS "Mar", 
   SUM(CASE month WHEN 'April' THEN 1 ELSE 0 END) AS "April", 
   SUM(CASE month WHEN 'May' THEN 1 ELSE 0 END) AS "May", 
   SUM(CASE month WHEN 'June' THEN 1 ELSE 0 END) AS "June"   
FROM tbl
WHERE start >= '2021-01-01'
AND   start <= '2021-06-30'

此查询需要每月运行过去 6 个月。例如，由于当前月份还没有结束，所以这个查询需要从 1 月 1 日到 6 月 30 日运行。如何自动执行这个查询，这样我就不必每个月都更改CASE语句或子句中的日期。WHERE

我期待的输出

Jan  Feb  Mar  Apr  May  June
2    2    3    6    1    4

标签： sqlpostgresqlpivotaggregatedynamic-sql

解决方案

从现在的日期返回1一个月：结果是结束日期：DATEADD(MONTH, -1, GETDATE())

7几个月后成为开始日期：DATEADD(MONTH, -7, GETDATE())

要计算该月的第一天，请从自身中减去今天：(DAY(CURRENT_TIMESTAMP) - 1)

获取结束日期

    sql => select DATEADD(MONTH, -1, GETDATE()) - (DAY(CURRENT_TIMESTAMP) - 1)
    postgresql => SELECT now() - INTERVAL '1 month' - (extract(day from now()) - 1 || ' day')::INTERVAL;

获取开始日期

    sql => select DATEADD(MONTH, -7, GETDATE()) - (DAY(CURRENT_TIMESTAMP) - 1)
    postgresql => select now() - INTERVAL '7 month' - (extract(day from now()) - 1 || ' day')::INTERVAL;

询问

SELECT 
  SUM(CASE month WHEN 'January' THEN 1 ELSE 0 END) AS "Jan", 
  SUM(CASE month WHEN 'February' THEN 1 ELSE 0 END) AS "Feb", 
  SUM(CASE month WHEN 'March' THEN 1 ELSE 0 END) AS "Mar", 
  SUM(CASE month WHEN 'April' THEN 1 ELSE 0 END) AS "April", 
  SUM(CASE month WHEN 'May' THEN 1 ELSE 0 END) AS "May", 
  SUM(CASE month WHEN 'June' THEN 1 ELSE 0 END) AS "June"   
FROM dateTable
WHERE start >= now() - INTERVAL '7 month' - (extract(day from now()) - 1 || ' day')::INTERVAL
AND start <= now() - INTERVAL '1 month' - (extract(day from now()) - 1 || ' day')::INTERVAL;

dbfiddle中的 postgresql 演示

推荐阅读