从几个位变量中选择Min和Max

I have table of teachers with 9 columns

我有9列的教师表

TeacherID, TeacherName, Reception, Year1, Year2, Year3, Year4, Year5, ALevel

I need to concatenate the smallest and largest bit values (Reception - Year5) with 1, then display it in the select statement so its output as (in this case, Year 1, 2 and 3 are ticked): Y1 - Y3.

我需要将最小和最大位值（接收 - 年份5）连接到1，然后将其显示在select语句中，使其输出为（在这种情况下，年份1,2和3被勾选）：Y1 - Y3。

SELECT 
    TeacherID, TeacherName, 
    Reception + Year1 + Year2 + Year3 + Year4 + Year5 + ALevel as 'KeyStage' 
FROM Teachers

Any ideas on how to do this?

关于如何做到这一点的任何想法？

Many thanks, Matt

非常感谢，马特

4 个解决方案

#1

SELECT 
    TeacherID,
    TeacherName,
    CASE
        WHEN Reception > 0 THEN 'Re'
        WHEN Year1 > 0 THEN 'Y1'
        WHEN Year2 > 0 THEN 'Y2'
        WHEN Year3 > 0 THEN 'Y3'
        WHEN Year4 > 0 THEN 'Y4'
        WHEN Year5 > 0 THEN 'Y5'
        WHEN ALevel > 0 THEN 'AL'
        ELSE '' END 
    + ' - ' +
    CASE
        WHEN ALevel > 0 THEN 'AL'
        WHEN Year5 > 0 THEN 'Y5'
        WHEN Year4 > 0 THEN 'Y4'
        WHEN Year3 > 0 THEN 'Y3'
        WHEN Year2 > 0 THEN 'Y2'
        WHEN Year1 > 0 THEN 'Y1'
        WHEN Reception > 0 THEN 'Re'
        ELSE '' END AS Range
FROM
    Teachers

#2

Try this; First Case starting from Reception for MinYear and Second Case starting from ALevel for MaxYear. Also add else for each case to avoid getting null if none of them are 1

尝试这个;第一个案例从MinYear的接收开始，第二个案例从ALevel为MaxYear开始。还要为每个案例添加else，以避免在没有为1的情况下获取null

SELECT TeacherID, TeacherName, Reception,
       Case when Reception = 1 then 'R'
            when Year1 = 1 then 'Y1'
            ...
            when ALevel = 1 then 'AL'
            else ''
       End + ' - '
       Case when ALevel = 1 then 'AL'
            when Year5 = 1 then 'Y5'
            ...
            when Reception= 1 then 'R'
            else '' 
       End As KeyStage  
FROM Teachers

#3

I wouldn't. Do it in your client code (php, c# whatever). Your spec is not clear either, what if years 1, 3 and 5 are ticked and 2 and 4 are not?

我不会。在您的客户端代码（php，c＃whatever）中执行此操作。你的规格也不清楚，如果第1,3和5年被勾选，2和4不是？

#4

This should work for you, I haven't tested it and you may need to make some changes. Note that if there is no bit checked then you will get a null value. I would use the null because if nothing is checked then you don't have this weird dash there and you could use an isnull to change the value to what you want for no years selected.

这应该适合你，我没有测试它，你可能需要做一些改变。请注意，如果没有检查位，那么您将获得一个空值。我会使用null，因为如果没有检查，那么你没有这个奇怪的短划线，你可以使用isnull将值更改为你想要的没有选择的年份。

SELECT TeacherID, 
   TeacherName,
   case when Reception = 1 then 'R'
        when Year1 = 1 then 'Y1'
        when Year2 = 1 then 'Y2'
        when Year3 = 1 then 'Y3'
        when Year4 = 1 then 'Y4'
        when Year5 = 1 then 'Y5'
        when ALevel = 1 then 'AL'
        else null end + ' - ' + 
   case when Reception = 1 then 'R'
        when Year1 = 1 then 'Y1'
        when Year2 = 1 then 'Y2'
        when Year3 = 1 then 'Y3'
        when Year4 = 1 then 'Y4'
        when Year5 = 1 then 'Y5'
        when ALevel = 1 then 'AL'
        else null end as 'KeyStage' 
FROM Teachers

#1

SELECT 
    TeacherID,
    TeacherName,
    CASE
        WHEN Reception > 0 THEN 'Re'
        WHEN Year1 > 0 THEN 'Y1'
        WHEN Year2 > 0 THEN 'Y2'
        WHEN Year3 > 0 THEN 'Y3'
        WHEN Year4 > 0 THEN 'Y4'
        WHEN Year5 > 0 THEN 'Y5'
        WHEN ALevel > 0 THEN 'AL'
        ELSE '' END 
    + ' - ' +
    CASE
        WHEN ALevel > 0 THEN 'AL'
        WHEN Year5 > 0 THEN 'Y5'
        WHEN Year4 > 0 THEN 'Y4'
        WHEN Year3 > 0 THEN 'Y3'
        WHEN Year2 > 0 THEN 'Y2'
        WHEN Year1 > 0 THEN 'Y1'
        WHEN Reception > 0 THEN 'Re'
        ELSE '' END AS Range
FROM
    Teachers

#2