SQL:GROUP BY子句中的条件语句 [英] SQL: conditional statement in GROUP BY clause

问题描述

当变量@SubjectName在英语"，数学"和任何其他学科(例如科学)之间更改时，我希望下面的案例声明更改结果集.

I would like the below case statement to change the resultset when the variable @SubjectName is changed between 'English', 'Mathematics' and any other subject eg science.

当变量是英语"的学生叫Ks2en应包括在结果集列，但在数学"选择列更改为Ks2ma，当它是别的Ks2av应选择.

When the variable is 'English' the column in students called Ks2en should be included in the results set, but when 'Mathematics' is selected the column changes to Ks2ma and when it is anything else Ks2av should be selected.

如果结果集中有空白值('')，则将其替换为'No KS2'.

Where there are any blank values ('') in the results set they are replaced with 'No KS2'.

这是我到目前为止无法使用的内容:

Here is what I have so far which doesn't work:

@SubjectName varchar(100) ='English'

SELECT CASE WHEN (student.Ks2en = '' and @SubjectName = 'English') OR 
    (student.Ks2ma = '' and @SubjectName = 'Mathematics') OR 
    (student.Ks2ma = '' AND @SubjectName <> 'Mathematics' AND
    @SubjectName <> 'English') 
            THEN 'No KS2' ELSE student.ks2en 
                OR student.Ks2ma OR student.Ks2av END AS 'KS2'
  FROM student JOIN subject 
    ON subject.upn=student.upn 
  WHERE
    [subject.Name] = @SubjectName

这是学生表的示例:

Ks2en    Ks2ma    Ks2av
4b       3c       3a
4a       4a       4a
3c                3c
         2c       2c
4c       3a       4c
4b       4b       4b

5a       3a       4a

因此，当@SubjectName ='English'时，结果集如下所示:

So when @SubjectName = 'English' the result set looks like:

Ks2    
4b       
4a       
3c
No KS2     
4c       
4b
No KS2        
5a           

当@SubjectName ='Mathematics'时，结果集如下所示:

And when @SubjectName = 'Mathematics' the result set looks like:

KS2
3c
4a
No KS2
2c
3a
4b
No KS2 
3a

当@SubjectName ='Science'时，结果集如下所示:

And when @SubjectName = 'Science' the result set looks like:

KS2
3a
4a
3c
2c
4c
4b
No KS2
4a

推荐答案

此代码将根据主题动态返回三列之一.

This code will dynamically return one of three columns depending upon subject.

这就是您想要的吗?如果需要，可以在语句末尾添加where子句.

Is that what you want? You can tack you where clause at the end of the statement if needed.

约翰

-- dynamically create column
SELECT 
   CASE 
      WHEN lower(coalesce(SubjectName, 'unknown')) = 'english' THEN
        [Ks2en]
      WHEN lower(coalesce(SubjectName, 'unknown')) = 'mathematics' THEN
        [Ks2ma]
      ELSE
        [Ks2av]
   END as KS2
FROM [student] INNER JOIN [subject] ON subject.upn=student.upn 
WHERE [subject].Subjectname LIKE @SubjectName;

我假设您的表架构如下所示.我正在使用元组符号.

I am assuming your table schema is like the following. I am using tuple notation.

student (upn, student name, ...)
subject (upn, subject name, ks2en, ks2ma, ks2av, ...)

如果不是，则该代码将不起作用.

If it is not, the code will not work.

这篇关于SQL:GROUP BY子句中的条件语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！