Question

怎么在where子句中用case when + exists

Answer 1

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

Answer 2

case when一般是用在from的前面：

--1.
select case when col1='a' then 'col1 value is a' when col1='b' then col1 value is b' else 'col1 has other values' end,col2 from table1 where exists
(select 1 from table2 where id=table1.id);
--2.
select case col1 when 'a' then 'col1 value is a' when 'b' then col1 value is b' else 'col1 has other values' end,col2 from table1 where exists
(select 1 from table2 where id=table1.id);

真要用在where后面的话：

select col1,col2 from table1 where exists
(select 1 from table2 where id=table1.id)
and col2=
(
select case col2 when 'a' then 'AAAAA' when 'b' then 'BBBBB' else 'null' end
from table3 where id=table1.id
);

Answer 3

case-单词讲解（释义、例句）

Answer 4

UPDATE stuMarks1
SET writtenExam=
case
when exists(select stuno from stuMarks1 where writtenExam>80)
then writtenExam-2
else
writtenExam-5
end;

相当于如下两句
UPDATE stuMarks1
SET writtenExam=writtenExam+2
where exists (select stuno from stuMarks1 where writtenExam>80);

UPDATE stuMarks1
SET writtenExam=writtenExam+5
where not exists (select stuno from stuMarks1 where writtenExam>80);