代码之家 › 专栏 › 技术社区 › Chenna

SQL Server:按自定义值分组到行

sql-server sql

Chenna · 技术社区 · 7 年前

我需要有关SQL查询的帮助

DELCARE @V_DATE DATETIME = CAST(GETDATE() AS DATE)

SELECT 
    CASE
       WHEN CAST(UT.ScheduledDate AS DATE) = @V_DATE AND UT.Status = 1) 
          THEN 'TODAY'
       WHEN CAST(UT.ScheduledDate AS DATE) > @V_DATE AND UT.Status = 1) 
          THEN 'PENDING'
       WHEN CAST(UT.ScheduledDate AS DATE) < @V_DATE AND UT.Status = 1) 
          THEN 'UPCOMING'
       WHEN UT.Status = 2 
          THEN 'COMPLETED'
       WHEN UT.Status = 3 
          THEN 'SKIPPED' 
    END,
    COUNT_BIG(*) AS COUNT
FROM 
    USERTABLE UT
GROUP BY 
    UT.ScheduledDate, UT.Status

假设我有这样的数据,其中status=1-pending,2-complete,3-skipped

ScheduledDate  | Status
---------------+-------
7/31/2018      | 1
8/01/2018      | 1
7/28/2018      | 1
7/28/2018      | 2
7/26/2018      | 3

如何获取这样的数据

Name        | Count
------------+----------
TODAY       |  1
PENDING     |  1
UPCOMING    |  1
COMPLETED   |  1
SKIPPED     |  1

2 回复 | 直到 7 年前

Gordon Linoff 7 年前

GROUP BY APPLY

SELECT name, COUNT_BIG(*) AS COUNT
FROM USERTABLE UT OUTER APPLY
     (VALUES (CASE WHEN CAST(UT.ScheduledDate AS DATE) = @V_DATE AND UT.Status = 1) THEN 'TODAY'
                   WHEN CAST(UT.ScheduledDate AS DATE) > @V_DATE AND UT.Status = 1) THEN 'PENDING'
                   WHEN CAST(UT.ScheduledDate AS DATE) < @V_DATE AND UT.Status = 1) THEN 'UPCOMING'
                   WHEN UT.Status = 2 THEN 'COMPLETED'
                   WHEN UT.Status = 3 THEN 'SKIPPED' 
              END)
      ) v(name)
GROUP BY name;

Yogesh Sharma 7 年前

subquery cte case

WITH t AS (
     SELECT CASE WHEN CAST(UT.ScheduledDate AS DATE) = @V_DATE AND UT.Status = 1) THEN 'TODAY'
                 WHEN CAST(UT.ScheduledDate AS DATE) > @V_DATE AND UT.Status = 1) THEN 'PENDING'
                 WHEN CAST(UT.ScheduledDate AS DATE) < @V_DATE AND UT.Status = 1) THEN 'UPCOMING'
                 WHEN UT.Status = 2 THEN 'COMPLETED'
                 WHEN UT.Status = 3 THEN 'SKIPPED' 
            END as Name
     FROM USERTABLE UT
)

SELECT Name, COUNT_BIG(*) AS cnt
FROM t
GROUP BY Name;

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

9 月前

Shukurullox Komiljonov · 从记录中获得相互和解。使用SQL

10 月前

John D · 需要为NULL或NOT NULL的WHERE子句

10 月前

ojek · 如何对SQL结果进行分组和编号?

10 月前

senek · 如何在PL/SQL中将选择结果(列)放入数组中

10 月前

Sax · 规范化Google表格(第一步)

10 月前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

10 月前

Jatin · 检索卷计数的动态sql抛出错误语法错误[关闭]

10 月前

Andrus · 如何在sql中查找第二个匹配项

10 月前

Nebula Tech · 在SQL Server中搜索字符串中单词的多次出现

10 月前