case-when....then....else....end

关于case?when....then....else....end的讨论正在各大平台持续发酵，我们精心筛选了最新资讯，希望能为您带来实质性的帮助。

case when?case when?ABC

sql

UPDATE ?

SET B = (CASE WHEN A = 1 THEN 1 ELSE 0 END),

C = (CASE WHEN A = 1 THEN 0 ELSE 1 END)

sql

SELECT

(CASE WHEN A = 1 THEN 1 ELSE 0 END) AS B,

(CASE WHEN A = 1 THEN 0 ELSE 1 END) AS C

FROM ?

简单的用法比如：

selelct user_id, user_name

from tb_users

where user_id in (select user_id from tb_course)

这个查询是查询在tb_course表中存在的user记录。

使用exists，写成

select user_id, user_name

from tb_users

where exists (select 1 from tb_course where tb_course.user_id = tb_users.user_id)

exists用法是把主查询中的字段传入到子查询中去。如果有符合的条件，会停止全表检索，返回TRUE。所以效率才要高于IN，IN是要进行完全表检索得到集合才会结束执行。而EXISTS遇到符合的 条件，就会停止执行子查询。

SELECT (CASE WHEN MASTERJOB = '' THEN JOBNO ELSE MASTERJOB END)WO,JOBNO,CHILDJOB,CODE,BDESC,P_CODE,

(SELECT TOP 1 BDESC FROM PRO WHERE CODE=RESVT.P_CODE)CBDESC,QTY,UNIT,LOC,

(CASE WHEN exists(SELECT top 1 TAXUP

FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)

WHERE B.CODE=RESVT.CODE AND A.WO like @gd AND B.TAXUP > 0 )

THEN

(SELECT top 1 TAXUP

FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)

WHERE B.CODE=RESVT.CODE AND B.TAXUP > 0 ) --AND charindex(@gd,WO)>0

WHEN exists(

SELECT TOP 1 B.TAXUP

FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)

WHERE B.CODE=RESVT.CODE AND B.TAXUP > 0 order by B.[DATETIME] desc )

THEN

(SELECT TOP 1 B.TAXUP

FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)

WHERE B.CODE=RESVT.CODE AND B.TAXUP > 0 order by B.[DATETIME] DESC)

ELSE

(select TOP 1 TAXUP from VQUT WHERE VQUT.CODE=RESVT.CODE order by [DATETIME] DESC)

END)DJ,

(CASE WHEN CHILDJOB='' THEN JOBNO ELSE CHILDJOB END)W,

(SELECT TOP 1 SUM(QTY) FROM MRT WHERE JOBNO=RESVT.JOBNO AND CODE=RESVT.CODE AND P_CODE=RESVT.P_CODE)T,

(SELECT TOP 1 SUM(QTY) FROM MRT WHERE JOBNO=RESVT.JOBNO AND CODE=RESVT.CODE AND P_CODE=RESVT.P_CODE AND MRNO LIKE '%HCBL%' )BL,

(select TOP 1 SUM(BADQTY+PBADQTY+RETUQTY) from PRT Left JOIN PRTUDF ON PRT.PRNO=PRTUDF.PRNO AND PRT.ITEMNO=PRTUDF.ITEMNO WHERE JOBNO=RESVT.JOBNO AND CODE=RESVT.CODE AND P_CODE=RESVT.P_CODE)TL,

(SELECT TOP 1 VEN FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)WHERE B.CODE=RESVT.CODE AND A.WO like @gd )VEN,

(SELECT TOP 1 qty_nee from #tree where #tree.code=RESVT.CODE AND #tree.p_code=RESVT.P_CODE)QTY_NEE,

(SELECT TOP 1 sh from #tree where #tree.code=RESVT.CODE AND #tree.p_code=RESVT.P_CODE)SH

FROM RESVT WHERE 1=1 and (CASE WHEN MASTERJOB = '' THEN JOBNO ELSE MASTERJOB END

关于case?when....then....else....end的探讨就到这里，您是否还有其他想了解的内容？欢迎在评论区留言告诉我们，同时别忘了点击关注哦！