使用案例陈述 [英] using case statements

问题描述

嗨


i有两个表可以叫它表a



表A有以下栏目

Hi
i have two tables lets call it table a

table A has the following columns

id(int)<br />
symptom(varchar)<br />
weight(int)<br />

表B有以下

Table B has the following

id<br />
TableA_id(int)<br />
Comment<br />
ProductCode<br />

i需要创建一个我可以在其中显示带有产品代码的所有记录的视图

但产品代码的症状与表A相关联。



所以1 ProductCode可能有5种症状



我想要做的是选择产品代码，症状，具有最高权重的症状以显示高优先级，以及所有其他显示低优先级。

i need to create a view in which i can display all the records with productcode
but the product code has a symptom linked to it from Table A.

So 1 ProductCode could have 5 symptoms

What i want to do is select the productcode, the symptom , the symptom with the highest weight to show High Priority , and all the others to show low priority.

推荐答案

试试这个......

SELECT B.productcode, B.comment, B.symptom, 
CASE WHEN A.weight BETWEEN 0 AND 30 THEN 'Low' 
     WHEN A.weight BETWEEN 31 AND 60 THEN 'mEDIUM' 
     WHEN A.weight > 60 THEN 'High' 
END 'Priority'
FROM table1 A
INNER JOIN table2 B ON A.id = A.table1_id
WHERE A.productcode IS NOT NULL
ORDER BY B.productcode ASC, A.symptom DESC

问候，

GVPrabu

Regards,
GVPrabu

select b.productcode, a.symptom
from table_a a
inner join table_b b on a.id = b.tablea_id
where not productcode is null
order by b.productcode asc, a.symptom desc

新版本：

New version:

select
    b.productcode,
    b.comment,
    a.symptom,
    case
        when a.weight = (select max(weight) from table_a) then 'High'
        else 'Low'
    end as 'Priority'
from
    table_a a
inner join
    table_b b on a.id = b.tablea_id
(...)

这篇关于使用案例陈述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！