case 语句基于 1 个表中的 3 列 [英] case statement based on 3 columns in 1 table

问题描述

我正在尝试在我的 sql 代码中创建一个字段，如果第 1 行和第 2 行中的 MRN 值相同并且相应的 TF_GROUP 相同但两行的 PathId 字段不同，那么新字段应该有一个 '是"否则它应该默认为否".我可以在 excel 中创建这个字段，但我正在努力在 sql 中创建类似的东西.我已经包含了我写的 excel 语句，我希望在下面的 sql 语句中复制它.

I am trying to create a field in my sql code where if the MRN value in row 1 and 2 are the same and the corresponding TF_GROUP is identical but the PathId field for both rows is different then the new field should have a 'Yes' otherwise it should default to 'No'. I can create this field in excel but am struggling to create something similar in sql. I have included the excel statement that i have written which i would ideally like to replicate in a sql statement below.

=IF(AND(B2=B3,D2=D3,A2<>A3),"Yes","No")

我基本上想要一个类似于上面的 excel 函数的 sql 语法，我可以在 sql server 中使用它来创建我想要的列.

I basically want a sql syntax similar to the excel function above that I could use in sql server to create the column that i desire.

下面是我用 3 个字段(PathID、MRN、TF_GROUP)编写的 sql 代码.

Below is the sql code that i have written with the 3 fields (PathID, MRN, TF_GROUP).

 select DISTINCT 
[Pathway ID] PathID,
MRN,
DENSE_RANK() OVER(PARTITION BY mrn ORDER BY [Pathway ID]) RK,
[Treatment Function Group] TF_GROUP,
'' [Flag]

from [HRS_RTT].[dbo].[tbl_PMM_IncompletePTL_CG_Snapshot] 
where convert(date,censusdate) between '03-jun-19' and '09-jun-19'
AND MRN IS NOT NULL
AND [Treatment Function Group] IS NOT NULL

order by
MRN,
[Pathway ID]

这是运行嵌入的 sql 代码后的部分输出(见下面的链接).名为Flag"的列是我在上面的 sql 代码中努力复制的内容.目前，我必须将输出复制到 excel 中，然后使用我之前在上一篇文章中包含的函数填充Flag"列.

Here is part of the output (see link below) after running the embedded sql code. The column called 'Flag' is what I am struggling to replicate in my sql code above. At the moment, i am having to copy the output into excel and then populate the 'Flag' column using the function i previously included in my previous post.

推荐答案

SELECT
    CASE
        WHEN B2 = B3 AND D2 = D3 AND A2 <> A3 THEN 'Y'
        ELSE 'N'
    END AS SomeColumnName
FROM SomeTable

来源:https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017

这篇关于case 语句基于 1 个表中的 3 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！