IF使用Transact SQL语句

问题描述：

IF (SELECT model_id FROM request_unit where request_id = '4357') IS NULL 
    SELECT part_id FROM request_unit WHERE request_id = '4357' 
ELSE 
    SELECT model_id FROM request_unit where request_id = '4357'

我收到以下错误：

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

答

如何只使用COALESCE()？

SELECT COALESCE(model_id, part_id) 
FROM request_unit 
WHERE request_id = '4357';

IF是不需要的。

错误本身似乎很明显。有一个在request_unit多行，其中request_id是4357.

它很好用，谢谢，但结果表的标题没有显示列的名称（model_id或part_id）。它显示'没有列名' – Ces1919

@NaderLaroussi只是别名，就像（model_id ModelID，part_id PartID） –

答

尝试......

这意味着有几个model_id为request_id = '4357'

IF Not Exists (SELECT model_id FROM request_unit where request_id = '4357') 
    SELECT part_id FROM request_unit WHERE request_id = '4357' 
ELSE 
    SELECT model_id FROM request_unit where request_id = '4357'

答

IF (SELECT top 1 model_id FROM request_unit where request_id = '4357') IS NULL 
    SELECT part_id FROM request_unit WHERE request_id = '4357' 
ELSE 
    SELECT model_id FROM request_unit where request_id = '4357'

这也能成为使用，但@Gordon Lioff的回答是首选

答

随着@ GordonLinoff的答案你可以请写下列内容以获得列名：

SELECT COALESCE(model_id, part_id) as 'YOURCOLUMNNAME' 
FROM request_unit 
WHERE request_id = '4357';

答

您可以这样做;

COALESCE

SELECT COALESCE(model_id, part_id) FROM request_unit WHERE request_id = '4357'

ISNULL

SELECT ISNULL(model_id, part_id) FROM request_unit WHERE request_id = '4357'

CASE WHEN

SELECT CASE WHEN model_id IS NULL THEN part_id ELSE model_id END FROM request_unit WHERE request_id = '4357'

IIFStarting with 2012

SELECT IIF(model_id IS NULL, part_id, model_id) THEN part_id ELSE model_id END FROM request_unit WHERE request_id = '4357'

IF使用Transact SQL语句

相关推荐