如何根据条件设置列值

本文介绍了如何根据条件设置列值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

亲爱的所有成员， 我在SQL查询中遇到以下问题 表结构的值为：

Dear All members, I have the following problem in the SQL Query Table Structure with Values are:

RequestId(Bigint) FileType(Varchar) FileName(varchar) Status 1 DRA 1_DRA.pdf Available 1 DRA Training

问题是，我必须根据以下条件将Column值设置为'Available'或'Not Available'： 1）如果文件类型是'DRA'并且文件名存在或文件类型是'DRA培训'且文件名不存在我必须更新将列状态设置为'可用' 2）如果文件类型为'DRA'且文件名不存在或文件类型为'DRA培训'且文件名存在，我必须更新将列状态设置为'可用' 3）如果文件类型为'CRA'且文件名不存在或文件类型是'CRA培训'而文件名不存在我必须更新将列状态设置为'不可用' 我尝试过： 我为同样的代码编写了以下代码： 1）

Problem is, I have to set the Column value to 'Available' or 'Not Available' based on the following condition: 1) If File Type is 'DRA' and File name is present or File Type is 'DRA Training' and Filename is not present I have to update set the column status as 'Available' 2) If File Type is 'DRA' and File name is not present or File Type is 'DRA Training' and Filename is present I have to update set the column status as 'Available' 3) If File Type is 'CRA' and File name is not present or File Type is 'CRA Training' and Filename is not present I have to update set the column status as ' Not Available' What I have tried: I have written the following code for the same which is not working: 1)

(SELECT TOP 1 CASE WHEN COALESCE([FileName],'') <> '' THEN 'AVAILABLE' ELSE 'NOT AVAILABLE' END FROM tblScannedFileDetails WHERE RequestID = 6904 AND FileType = 'DRA' or FileType='CRA Training' ) AS 'CRA Upload Status'

上面总是显示不可用。 2）

The above always shows Not Available. 2)

SELECT (CASE WHEN ((COALESCE(C.FileName,'') <> '' and C.FileType = 'DRA') OR (COALESCE(C.FileName,'') = '' and C.FileType='DRA Training')) THEN 'AVAILABLE' WHEN ((COALESCE(C.FileName,'') = '' and C.FileType = 'DRA') OR (COALESCE(C.FileName,'') <> '' and C.FileType='DRA Training')) THEN 'AVAILABLE' WHEN ((COALESCE(C.FileName,'') = '' and C.FileType = 'DRA') OR (COALESCE(C.FileName,'') = '' and C.FileType='DRA Training')) THEN 'NOT AVAILABLE' --ELSE 'NOT AVAILABLE' END ) FROM tblScannedFileDetails C WHERE RequestID = 6904

这也不起作用并返回null或Not Available。

This is also not working and returns null or Not Available.

推荐答案

检查： Check this: DECLARE @tblScannedFileDetails TABLE(RequestId Bigint, FileType Varchar(155), [FileName] Varchar(155), [Status] VARCHAR(30)) INSERT INTO @tblScannedFileDetails (RequestId, FileType, [FileName], [Status]) VALUES(1, 'DRA', '1_DRA.pdf', 'Available'), (1, 'DRA Training', NULL, NULL), (2, 'CRA', '1_CRA.pdf', NULL), (2, 'CRA', NULL, NULL) SELECT RequestId, FileType, [FileName], [Status], [NewStatus] = CASE WHEN (FileType = 'DRA' AND NOT [FileName] IS NULL) OR (FileType = 'DRA Training' AND [FileName] IS NULL) THEN 'Available' WHEN (FileType = 'DRA' AND [FileName] IS NULL) OR (FileType = 'DRA Training' AND NOT [FileName] IS NULL) THEN 'Available' WHEN (FileType = 'CRA' AND NOT [FileName] IS NULL) OR (FileType = 'CRA Training' AND NOT [FileName] IS NULL) THEN 'Available' ELSE 'Not Available' END FROM @tblScannedFileDetails

返回值：

Returned values:

RequestId FileType FileName Status NewStatus 1 DRA 1_DRA.pdf Available Available 1 DRA Training NULL NULL Available 2 CRA 1_CRA.pdf NULL Available 2 CRA NULL NULL Not Available

这就是你要找的东西吗？ /> 如果您想在单个语句中更新表，请检查：使用SQL Server从SELECT更新 - Stack Overflow [ ^ ]