sql-server-2008 - SQL 条件选择案例逻辑

Question

我怀疑这与我在 SELECT 语句中有 case 语句然后我试图对此执行 GROUP BY 和 ORDER BY 语句有关。

我有一个基于两个表之间的连接的结果集。一个是学生表，其中 ks2en、ks2ma 和 Ks2av 列的值为 2c、2b、2a、3c、3b 等，第二个是每个学生的结果表，例如 A+、A、A-、B+ 等。值在ks2en、ks2ma 和 Ks2av 列被分组并对结果求和，这给出了一个总计网格，其中水平在左边，结果在顶部。然而Ks2en、Ks2ma和Ks2av这三种level应该根据特定的主题带来结果:

 Ks2en - English Result
 Ks2ma - Maths Result
 Ks2av - Any other subject Result

如果 ks2en、ks2ma 或 ks2av 列中没有值，我希望分组依据显示为“No KS2”。

我在底部的代码非常适合英语和 Ks2en，但我正在努力找出根据主题是英语、数学还是其他任何内容来更改结果的逻辑。

主题通过名为@SubjectName 的变量填充。

下面是我希望我的 sql 执行的伪代码:

If @SubjectName = 'English' then
    If ks2en ='' then
        display 'No KS2'
    else Ks2en
    endif
Else if @SubjectName = 'Mathematics' then
    If ks2ma ='' then
        display 'No KS2'
    else Ks2ma
    endif
Else if @SubjectName <> 'English' and @SubjectName <> 'Mathematics' then
    If ks2av ='' then
        display 'No KS2'
    else Ks2av
    endif

这是我目前为止在@SubjectName = 'English' 时有效的代码:

DECLARE 
@DataCollection varchar(50) = '2013/14 - Autumn 1 - Targets',
@StuYear VARCHAR(2) = '11',
@SubjectName varchar(100) ='English',
@TeachingGroup varchar(25) = 'Select All',
@SubGroup varchar(10) = 'Select All'
SELECT CASE WHEN Ks2en = '' and @SubjectName = 'English' THEN 'No KS2' ELSE ks2en END AS 'KS2',
      nullif(count(CASE WHEN result = '' THEN 1 END),0) AS 'No Level/Grade',
      nullif(count(CASE WHEN result IN ('U', '1a', '1b', '1c') THEN 1 END),0) AS '1/U',
      nullif(count(CASE WHEN result IN ('U', '2a', '2b', '2c') THEN 1 END),0) AS '2/U',
      nullif(count(CASE WHEN result IN ('G-','3c') THEN 1 END),0) AS '3c/G-',
      nullif(count(CASE WHEN result IN ('G', '3b') THEN 1 END),0) AS '3b/G',
      nullif(count(CASE WHEN result IN ('G+','3a') THEN 1 END),0) AS '3a/G+',
      nullif(count(CASE WHEN result IN( 'F-','4c') THEN 1 END),0) AS '4c/F-',
      nullif(count(CASE WHEN result IN( 'F', '4b') THEN 1 END),0) AS '4b/F',
      nullif(count(CASE WHEN result IN( 'F+', '4a') THEN 1 END),0) AS '4a/F+',
      nullif(count(CASE WHEN result IN( 'E-', '5c') THEN 1 END),0) AS '5c/E-',
      nullif(count(CASE WHEN result IN( 'E', '5b') THEN 1 END),0) AS '5b/E',
      nullif(count(CASE WHEN result IN( 'E+', '5a') THEN 1 END),0) AS '5a/E+',
      nullif(count(CASE WHEN result IN( 'D-', '6c') THEN 1 END),0) AS '6c/D-',
      nullif(count(CASE WHEN result IN( 'D', '6b') THEN 1 END),0) AS '6b/D',
      nullif(count(CASE WHEN result IN( 'D+', '6a') THEN 1 END),0) AS '6a/D+',      
      nullif(count(CASE WHEN result IN( 'C-', '7c') THEN 1 END),0) AS '7c/C-',      
      nullif(count(CASE WHEN result IN( 'C', '7b') THEN 1 END),0) AS '7b/C',     
      nullif(count(CASE WHEN result IN( 'C+', '7a') THEN 1 END),0) AS '7a/C+',         
      nullif(count(CASE WHEN result IN( 'B-', '8c') THEN 1 END),0) AS '8c/B-',  
      nullif(count(CASE WHEN result IN( 'B', '8b') THEN 1 END),0) AS '8b/B',
      nullif(count(CASE WHEN result IN( 'B+', '8a') THEN 1 END),0) AS '8a/B+', 
      nullif(count(CASE result WHEN 'A-' THEN 1 END),0) AS 'A-',
      nullif(count(CASE result WHEN 'A' THEN 1 END),0) AS 'A',
      nullif(count(CASE result WHEN 'A+' THEN 1 END),0) AS 'A+',
      nullif(count(CASE result WHEN 'A*-' THEN 1 END),0) AS 'A*-',
      nullif(count(CASE result WHEN 'A*' THEN 1 END),0) AS 'A*'
  FROM student JOIN subject 
    ON subject.upn=student.upn 
WHERE
    [StuYear] = @StuYear AND
    [DataCollection] = @DataCollection AND
    [Name] = @SubjectName AND (
        @TeachingGroup = 'Select All' OR 
        [TeachingGroup] = @TeachingGroup
    ) AND (
       @SubGroup = 'Select All' OR
       Gender = CASE 
               WHEN @SubGroup = 'GenF' THEN 'F'
               WHEN @SubGroup = 'GenM' THEN 'M'
           END
    )
 GROUP BY ks2en
 ORDER BY
    CASE WHEN ks2en = 'W' THEN 0 ELSE 1 END,
    LEFT(ks2en, 1),
    RIGHT(ks2en, 1) DESC

如果@SubjectName = 'Mathematics' 那么该语句将有效地执行与以下相同的操作:

DECLARE 
@DataCollection varchar(50) = '2013/14 - Autumn 1 - Targets',
@StuYear VARCHAR(2) = '11',
@SubjectName varchar(100) ='Mathematics',
@TeachingGroup varchar(25) = 'Select All',
@SubGroup varchar(10) = 'Select All'
SELECT CASE WHEN Ks2ma = '' and @SubjectName = 'Mathematics' THEN 'No KS2' ELSE ks2ma END AS 'KS2',
      nullif(count(CASE WHEN result = '' THEN 1 END),0) AS 'No Level/Grade',
**SNIP**
      nullif(count(CASE result WHEN 'A*' THEN 1 END),0) AS 'A*'
  FROM student JOIN subject 
    ON subject.upn=student.upn 
WHERE
    [StuYear] = @StuYear AND
    [DataCollection] = @DataCollection AND
    [Name] = @SubjectName AND (
        @TeachingGroup = 'Select All' OR 
        [TeachingGroup] = @TeachingGroup
    ) AND (
       @SubGroup = 'Select All' OR
       Gender = CASE 
               WHEN @SubGroup = 'GenF' THEN 'F'
               WHEN @SubGroup = 'GenM' THEN 'M'
           END
    )
 GROUP BY ks2ma
 ORDER BY
    CASE WHEN ks2ma = 'W' THEN 0 ELSE 1 END,
    LEFT(ks2ma, 1),
    RIGHT(ks2ma, 1) DESC

如果@SubjectName 等于任何其他内容，例如 Science、Art 或 Technology，那么 SQL 会喜欢以下内容:

DECLARE 
@DataCollection varchar(50) = '2013/14 - Autumn 1 - Targets',
@StuYear VARCHAR(2) = '11',
@SubjectName varchar(100) ='Science',
@TeachingGroup varchar(25) = 'Select All',
@SubGroup varchar(10) = 'Select All'
SELECT CASE WHEN Ks2av = '' and @SubjectName <> 'Mathematics' and @SubjectName <> 'English'  THEN 'No KS2' ELSE ks2av END AS 'KS2',
      nullif(count(CASE WHEN result = '' THEN 1 END),0) AS 'No Level/Grade',
**SNIP**
      nullif(count(CASE result WHEN 'A*' THEN 1 END),0) AS 'A*'
  FROM student JOIN subject 
    ON subject.upn=student.upn 
WHERE
    [StuYear] = @StuYear AND
    [DataCollection] = @DataCollection AND
    [Name] = @SubjectName AND (
        @TeachingGroup = 'Select All' OR 
        [TeachingGroup] = @TeachingGroup
    ) AND (
       @SubGroup = 'Select All' OR
       Gender = CASE 
               WHEN @SubGroup = 'GenF' THEN 'F'
               WHEN @SubGroup = 'GenM' THEN 'M'
           END
    )
 GROUP BY ks2av
 ORDER BY
    CASE WHEN ks2av = 'W' THEN 0 ELSE 1 END,
    LEFT(ks2av, 1),
    RIGHT(ks2av, 1) DESC

我的 SQL 生成的网格类似于下图。结果集看起来很简单，但是背景数据将基于上面讨论的不同标准，并且会在每个单元格中产生不同的总数:

KS2 No Result   1/U     2/U     3c/G-   3b/G    3a/G+   4c/F-   4b/F    **snip**
No  KS2         1       NULL    NULL    NULL    NULL    NULL    NULL    **snip**
2a  NULL        NULL    NULL    NULL    NULL    NULL    2       1       **snip**
3c  1           NULL    NULL    NULL    NULL    NULL    NULL    NULL    **snip**
3b  NULL        NULL    NULL    NULL    NULL    NULL    1       NULL    **snip**
3a  1           NULL    NULL    NULL    NULL    NULL    NULL    NULL    **snip**
4c  NULL        1       1       NULL    NULL    NULL    NULL    NULL    **snip**
4b  NULL        NULL    NULL    NULL    NULL    NULL    NULL    NULL    **snip**
4a  NULL        1       1       NULL    NULL    NULL    NULL    NULL    **snip**
5c  NULL        NULL    NULL    NULL    NULL    NULL    NULL    NULL    **snip**
5b  NULL        NULL    NULL    NULL    NULL    NULL    NULL    NULL    **snip**

Answer 1

如果我没看错的话，你其实可以自己处理任务，除了过滤。我的意思是，除了通过“英语/数学/其他”对结果进行分支/调节外，您的问题在于它们几乎相同，只是它们来自不同的列并且您希望将它们统一串接。

对不起，如果这不是您的实际问题，但我是这样阅读/理解的。

由于多种原因，我目前无法给您一个准确的完整答案(即我不知道 F+ G- 等成绩与 3a 6b 有何关系values )，但我至少可以给你一些帮助/启动你如何解决这类问题。

你想要的效果:

     If @SubjectName = 'English' then    If ks2en ='' then    ....
else If @SubjectName = 'Maths'   then    If ks2ma ='' then    ....
else                                     If ks2av ='' then    ....

这很容易通过一些初步的重新排列您的输入表来完成。简单地说，只需将有趣的部分过滤成 block 并添加一些常量值，这些值将充当有关该 block 的方便元数据:

          select "English"     as topic, ks2en as ks2
          from YourTable
          where ks2en <> ''
union all
          select "Mathematics" as topic, ks2ma as ks2
          from YourTable
          where ks2ma <> ''
union all
          select "Other"       as topic, ks2av as ks2
          from YourTable
          where ks2av <> ''

请注意，我添加了一个名为 topic 的额外计算键值，这些数据现在重新排列为 2 列。您现在无法区分 ks2en 和 ks2ma，但是您有一个 ks2 并且您可以通过常量 topic 检测它来自什么 列。

现在:

select
    case when subq.ks2 = '' then 'No KS2'
    else subq.ks2
    end
from
(
          select "English"     as topic, ks2en as ks2 from YourTable where ks2en <> ''
union all select "Mathematics" as topic, ks2ma as ks2 from YourTable where ks2ma <> ''
union all select "Other"       as topic, ks2av as ks2 from YourTable where ks2av <> ''
) subq
where subq.topic = @SubjectName

(为了整体可读性，我压缩了选择/联合部分)

将为您返回 KS2 以获得您想要的实际 SubjectName。除了 Other 部分显然不会匹配任何内容，因为“Physics”与“Other”不匹配。您可以通过检查 SubjectName 的已知值并将其设置为“其他”来轻松解决这个问题。或者其他什么。

要点在于，多亏了初步的过滤描述和粘附，您可以按照您想要的任何方式 reshape 数据，然后轻松地将其分组/投影到您真正想要的内容中。

编辑:我不知道它是否存在于 sql2008 级别，但我认为您还可以检查可以转置列的 PIVOT/UNPIVOT 运算符<->行。它们使用起来很棘手，有一些限制使它们远不如它们应该的那样方便，但值得检查。当然，假设我很好地理解了你问题的核心。对不起，如果没有。