SQL中带有where条件的案例语句 [英] Case statement with where condition in SQL

问题描述

我正在尝试用SQL编写CASE语句.

I am trying to write a CASE statement in SQL.

数据库结构如下:

==============================================
tender_id  |  file_no  |   subject
==============================================

150001         16/41        Against Shipment
150005         16/42        Pending
150008         16/43        Shipment Clause
1500081        16/43        NULL or Empty
1500082        16/43        NULL or Empty
==============================================

我正在尝试编写一个显示主题的CASE语句.条件是如果主题"为NULL or EMPTY，则应该在使用条件where file_no = file_no输入主题的地方获取主题.在这种情况下，tender_id 1500081和1500082应将主题作为发货条款"，因为这些匹配项的file_no.

I am trying to write a CASE statement which shows the subject. The criteria is if Subject is NULL or EMPTY then it should fetch the subject where subject is entered with the criteria where file_no = file_no. In this case tender_id 1500081 and 1500082 should take the subject as 'Shipment Clause' since the file_no of those matches.

有人可以帮我吗?

推荐答案

您可以使用自连接解决此问题:

You can solve this with a self-join:

SELECT t1.tender_id, t2.file_no,
  CASE t1.subject 
  WHEN 'NULL or Empty' THEN t2.subject 
  ELSE t1.subject END AS subject
FROM YourTableName AS t1
JOIN YourTableName AS t2 
  ON (t2.file_no = t1.file_no AND t2.subject <> 'Null or Empty');

假设每个文件的编号只设置了一行主题.

This assumes there is only one row where the subject is set, for each file_no.

更新:我创建了 SQLFiddle 来演示此查询.据我了解，这可以满足您描述的问题.

Update: I created a SQLFiddle to demo this query. This satisfies the problem you described, as I understand it.

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