在select语句中使用Case

Consider the following table

请考虑下表

create table temp (id int, attribute varchar(25), value varchar(25))

And values into the table

并将价值纳入表中

insert into temp select 100, 'First', 234
insert into temp select 100, 'Second', 512
insert into temp select 100, 'Third', 320
insert into temp select 101, 'Second', 512
insert into temp select 101, 'Third', 320

I have to deduce a column EndResult which is dependent on 'attribute' column. For each id, I have to parse through attribute values in the order First, Second, Third and choose the very 1st value which is available i.e. for id = 100, EndResult should be 234 for the 1st three records.

我必须推断出一个依赖于'attribute'列的EndResult列。对于每个id,我必须按顺序解析第一,第二,第三顺序中的属性值,并选择可用的第一个值,即对于id = 100,对于前三个记录,EndResult应为234。

Expected result:

|  id | EndResult |
|-----|-----------|
| 100 |       234 |
| 100 |       234 |
| 100 |       234 |
| 101 |       512 |
| 101 |       512 |

I tried with the following query in vain:

我尝试使用以下查询是徒劳的:

select id, case when isnull(attribute,'') = 'First'
                then value
            when isnull(attribute,'') = 'Second'
                then value         
            when isnull(attribute,'') = 'Third'
                then value    
            else '' end as EndResult
from
temp

Result

|  id | EndResult |
|-----|-----------|
| 100 |       234 |
| 100 |       512 |
| 100 |       320 |
| 101 |       512 |
| 101 |       320 |

Please suggest if there's a way to get the expected result.

请建议是否有办法获得预期的结果。

4 个解决方案

#1

Here is how you can achieve this using ROW_NUMBER():

以下是使用ROW_NUMBER()实现此目的的方法:

WITH t
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY id ORDER BY (CASE attribute WHEN 'First' THEN 1 
                                                     WHEN 'Second' THEN 2 
                                                     WHEN 'Third' THEN 3 
                                      ELSE 0 END)
            ) rownum
    FROM TEMP
    )
SELECT id
    ,(
        SELECT value
        FROM t t1
        WHERE t1.id = t.id
            AND rownum = 1
        ) end_result
FROM t;

For testing purpose, please see SQL Fiddle demo here:

出于测试目的,请在此处查看SQL Fiddle演示:

SQL Fiddle Example

SQL小提琴示例

#2

You can use analytical function like dense_rank to generate a numbering, and then select those rows that have the number '1':

您可以使用dense_rank等分析函数生成编号,然后选择编号为“1”的行:

select
  x.id,
  x.attribute,
  x.value
from
  (select
    t.id,
    t.attribute,
    t.value,
    dense_rank() over (partition by t.id order by t.attribute) as priority
  from
    Temp t) x
where
  x.priority = 1

In your case, you can conveniently order by t.attribute, since their alphabetical order happens to be the right order. In other situations you could convert the attribute to a number using a case, like:

在您的情况下,您可以通过t.attribute方便地订购,因为它们的字母顺序恰好是正确的顺序。在其他情况下,您可以使用案例将属性转换为数字,例如:

order by 
  case t.attribute 
    when 'One' then 1 
    when 'Two' then 2 
    when 'Three' then 3 
  end

#3

In case the attribute column have different values which are not in alphabetical order as is the case above you can write as:

如果属性列具有不同于上面字母顺序的值,则可以写为:

with cte as
(
  select id,
         attribute,
         value,
         case attribute when 'First' then 1
                        when 'Second' then 2 
                        when 'Third' then 3 end as seq_no 
         from temp
  )
  , cte2 as 
  ( 
    select id,
           attribute,
           value,
           row_number() over ( partition by id order by seq_no asc) as rownum
     from cte
    ) 
    select T.id,C.value as EndResult
    from temp T
    join cte2 C on T.id = C.id and C.rownum = 1

DEMO

#4

keep it simple

把事情简单化

;with cte as
(
    select  row_number() over (partition by id order by (select 1)) row_num, id, value
    from    temp
)
select  t1.id, t2.value
from    temp t1
            left join cte t2
            on t1.Id = t2.id
where   t2.row_num = 1

Result

#1