假设我有序列号、测试名称和几个其他列,我想写一个条件,如果特定序列号为TESTNAME is null,则将TESTNAME设置为空,否则执行内连接
SELECT
(A.PTNUMBER + '-' +A.SL_NO) AS ENUMBER,
D.ENGINEER AS REQ, D.DATETIME as "DATE",
(select Value
from DROPDOWN
where B.TEST_NAME=CONVERT(VARCHAR,DropdownID)) TESTNAME,
TABLE_NAME AS TABLETD
FROM INSPECTION D
INNER JOIN TABLEA A ON D.ENGID = CONVERT(VARCHAR,A.EN_ID)
INNER JOIN TABLEB B ON B.ENGID = CONVERT(VARCHAR,A.EN_ID)
INNER JOIN TABLEC C ON C.ENGID = CONVERT(VARCHAR,A.EN_ID)发布于 2017-04-01 03:44:33
我不确定将testname设置为空是什么意思,但是如果您打算使用SELECT查询,那么可以这样做
select *,
case when TESTNAME is null and serial_number = some_value then '' end as TESTNAME
from mytable发布于 2017-04-01 03:46:06
select serial_number, ...
,case when coalesce(testname,'') <> ''
then t2.testname
else coalesce(testname,'') end
from t
inner join t2
on ...可以在sql server中使用isnull()或coalesce()返回不同的值来替换null。
select isnull(testname,'')或
select coalesce(testname,'')这两者之间的更多区别是answered here.
select coalesce(testname,testname2,'')coalesce()也是标准的ANSI sql,所以您可以在大多数关系数据库中找到它。isnull()是特定于sql server的。
参考资料:
发布于 2017-04-01 04:03:28
SELECT (A.PTNUMBER + '-' + A.SL_NO) AS ENUMBER,
D.ENGINEER AS REQ,
D.DATETIME as "DATE",
case
when SerialNo = xxx and TESTNAME is null then ''
else (select Value from DROPDOWN where B.TEST_NAME = CONVERT(VARCHAR, DropdownID))
end AS TESTNAME,
TABLE_NAME AS TABLETD
FROM INSPECTION D
INNER JOIN TABLEA A ON D.ENGID = CONVERT(VARCHAR, A.EN_ID)
INNER JOIN TABLEB B ON B.ENGID = CONVERT(VARCHAR, A.EN_ID)
INNER JOIN TABLEC C ON C.ENGID = CONVERT(VARCHAR, A.EN_ID);https://stackoverflow.com/questions/43148471
复制相似问题