怎样在where条件中使用case语句??

[lsp69]

以下这些SQL语句怎样用case语句来完成?也就是说怎么把那些if条件,用case写在where中?

if exists(select * from v_tempbasevoyage2 where services='TP3-maersk'
and POL is not null
and pol in(select portfuName from v_poltrade where stshortname='qd' )
and b_portcode<>0)
begin
select * from v_tempbasevoyage2
where services='TP3-maersk'
and POL is not null
and (pol in(select portfuName from v_poltrade where stshortname='qd' )
or (POL is not null and flag=0 and b_flag=1))
end
else
begin
select * from v_tempbasevoyage2
where services='TP3-maersk'
and POL is not null
and pol in(select portfuName from v_poltrade where stshortname='qd' )
end

[dawugui]

where 里面不行.

可尝试:

select col1 , col2 = (case col3 when value1 then .. else .. end) from tb

[-狙击手-]

顶龟丫的

[浩方软件HFWMS]

各位大哥,那该怎么写呀

[wzy_love_sly]

靠，真不行，累死我

[tim_spac]

推演: ==>
if exists(select * from v_tempbasevoyage2 where services='TP3-maersk'
and POL is not null
and pol in(select portfuName from v_poltrade where stshortname='qd' )
and b_portcode <> 0)
begin
select * from v_tempbasevoyage2 where services='TP3-maersk' and POL is not null
and (pol in(select portfuName from v_poltrade where stshortname='qd' ) or (POL is not null and flag=0 and b_flag=1))
end
else
begin
select * from v_tempbasevoyage2 where services='TP3-maersk' and POL is not null
and pol in(select portfuName from v_poltrade where stshortname='qd' )
end
==>
{A} 表示逻辑表达式 exists(select * from ...)
{B} 表示逻辑表达式 and (pol in(select portfuName from v_poltrade where stshortname='qd' ) or (POL is not null and flag=0 and b_flag=1))
{C} 表示逻辑表达式 and pol in(select portfuName from v_poltrade where stshortname='qd' )
==>
select * from v_tempbasevoyage2 where services='TP3-maersk' and POL is not null
and case when {A} then {B} else {C} end
==>
select * from v_tempbasevoyage2 where services='TP3-maersk' and POL is not null
and case
when {A} and {B} then True
when not {A} and {C} then True
else False end
==>
select * from v_tempbasevoyage2 where services='TP3-maersk' and POL is not null
and case
when (exists(select * from v_tempbasevoyage2 where services='TP3-maersk'
and POL is not null
and pol in(select portfuName from v_poltrade where stshortname='qd' )
and b_portcode <> 0))
and (pol in(select portfuName from v_poltrade where stshortname='qd' ) or (POL is not null and flag=0 and b_flag=1))
then 1=1

when not (exists(select * from v_tempbasevoyage2 where services='TP3-maersk'
and POL is not null
and pol in(select portfuName from v_poltrade where stshortname='qd' )
and b_portcode <> 0))
and (pol in(select portfuName from v_poltrade where stshortname='qd' ) )
then 1=1

else 1=2 end

[fcuandy]

可以的,正在补充说明中..请稍后.

[中国风]

select 
	*
from 
	v_tempbasevoyage2
where
	(case when exists
			(select   *   from   v_tempbasevoyage2   where   services='TP3-maersk'   
			and   POL   is   not   null   
			and   pol   in(select   portfuName   from   v_poltrade   where   stshortname='qd'   ) 
			and   b_portcode <> 0) 
		then 
			case when  services='TP3-maersk'   
			and   POL   is   not   null   
			and   (pol   in(select   portfuName   from   v_poltrade   where   stshortname='qd'   ) 
			or   (POL   is   not   null     and   flag=0   and   b_flag=1)) then 1 else 0 end
		else
			case when  services='TP3-maersk'   
			and   POL   is   not   null   
			and   pol   in(select   portfuName   from   v_poltrade   where   stshortname='qd'   ) 
			then 1 else 0 end
	end)=1

[中国风]

刚写了一个例子：

create table T(Col int)
insert T select 50
go

select 
    *
from 
    T
where 
    (case when exists(select 1 from T where Col between 20 and 30) then 
        case when Col between 20 and 30 then 1 else 0 end
        when exists(select 1 from T where Col between 18 and 32) then     
        case when Col between 18 and 32 then 1 else 0 end            --这里改一下
        when exists(select 1 from T where Col between 10 and 50) then 
        case when Col between 10 and 50 then 1 else 0 end end)=1
/*
Col         
----------- 
50

（所影响的行数为 1 行）

*/

insert T select 32
/*

Col         
----------- 
32

（所影响的行数为 1 行）

*/
insert T select 20

/*
Col         
----------- 
20

（所影响的行数为 1 行）


*/
--drop table T

[fcuandy]

楼主语句原型为

--条件判断
if   exists(
		select *   from   v_tempbasevoyage2   
			where   services='TP3-maersk'   
				and   POL   is   not   null   
				and   pol   in(
							select   portfuName from   v_poltrade   where   stshortname='qd'   
						) 
				and   b_portcode <> 0
		) 
	--第一分支
	begin 
		select   *   from   v_tempbasevoyage2 
			where   services='TP3-maersk'   
				and   POL   is   not   null   
				and   (
						pol   in(select   portfuName   from   v_poltrade   where   stshortname='qd'   ) 
						or   
						(POL   is   not   null     and   flag=0   and   b_flag=1)
						--可将上行换为下行
						--(flag=0   and   b_flag=1)
					) 
	end   


else 
	--第二分支
	begin 
		select   *   from   v_tempbasevoyage2 
			where   services='TP3-maersk'   
				and   POL   is   not   null   
				and   pol   in(select   portfuName   from   v_poltrade   where   stshortname='qd'   ) 
	end   

/*
设表的行集为 S
设 b_postcode<>0 取到的行集为 X
首先三个语句中都有有按照 pol is not null 约束取交集的部分, 所以第一分支的 or 里那个条件,可以去掉 pol is not null这一句. 在那句后的注释里
对比两个分支中取行集的区别:
前两条件相同 设这两条件 servers='tp3-maersk' and pol is not null 共同约束下取的行集为 A 
第二分支在A行集的基出上再进行交集操作,即 A 行集里取 pol in ( .. )的行( pol in (...) 的行集设为 E). 设得到行集为 B
第一分支,在A行集上再进行交集操作,即 A行集里取 ( pol in (...) or (flag=0 and b_flag=1) )的部分,可记为D
D又可以转化为 A与 pol in(...) 这个条件进行交集运算  然后并上  A 与 (flag=0 and b_flag=1)进行交集运算 (设这一部分为C),
那么 可以表示为 
:
A,B,C,D,E为行集. U 为并操作, /\ 为交操作.因为我暂时打不出来那个符号,就用这个代替了,实际上是用 U 水平翻转来表示
B = A /\ E
D = A /\ (E U C)
D = B U C
最后转换为
D = (A /\ E) U (A /\ C)

实际上,我们在取数据结果的时候,需要取出的是两种情况,即第一分支 取 D ,第二分支 取 B ,条件判断部分为 A /\ X

那么语句就可以转化为
IF (A /\ X)的rows>0 
	取 D 或者说取 B U C 也或者说取 ... 看你怎么写语句方便你就怎么进行集合运算吧
ELSE
	取 B 即 A/\E

转化为 case when即



SELECT 所有可能的行 FROM S 
	WHERE 取A的条件 /\ 取E的条件
		U
		取A的条件 /\ CASE WHEN EXISTS( A/\E/\X ) THEN 取C的条件 ELSE 0 END

上面的0代表,输出一个值让它与所对比的列不同,从而让这个条件不成立
当然了 case when 不能输出条件表达式,只能输出值, 同上面0的意义一样,让条件成立

*/

[fcuandy]

吃饭时间到,吃完饭,给你完整的SQL语句表示法.

[fcuandy]

改用case when后语句如下:

exists那里小写部分的语句是照搬你的. 这部分不能与前面的条件或者我分析的集合进行合并, 否则数据不能完全尊照原意

不过,这样改写后,效率极其低下

//设,我理解为你的 flag和b_flag为bit或int型数据,那么它就不可能出现 'a','b'这样的字符值,我要用这样的字符值来构建case when.

SELECT * FROM v_tempbasevoyage2
	WHERE services='TP3-maersk'
		AND POL IS NULL
		AND
		(
			POL IN (SELECT portfuName FROM v_poltrade WHERE stShortName='qd'
			OR
			(
				RTRIM(flag)=
				CASE WHEN  exists(
						select *   from   v_tempbasevoyage2   
							where   services='TP3-maersk'   
								and   POL   is   not   null   
								and   pol   in(
									select   portfuName from   v_poltrade   where   stshortname='qd'   
									) 
								and   b_portcode <> 0
						)
					THEN '0' ELSE 'a' END,

				RTRIM(b_flag)=
					CASE WHEN  exists(
						select *   from   v_tempbasevoyage2   
							where   services='TP3-maersk'   
								and   POL   is   not   null   
								and   pol   in(
									select   portfuName from   v_poltrade   where   stshortname='qd'   
									) 
								and   b_portcode <> 0
						)
					THEN '1' ELSE 'a' END
			
			) 
		)

[fcuandy]

上面少敲了一个扩号:

//设,我理解为你的 flag和b_flag为bit或int型数据,那么它就不可能出现 'a','b'这样的字符值,我要用这样的字符值来构建case when.

SELECT * FROM v_tempbasevoyage2
	WHERE services='TP3-maersk'
		AND POL IS NULL
		AND
		(
			POL IN (SELECT portfuName FROM v_poltrade WHERE stShortName='qd')
			OR
			(
				RTRIM(flag)=
				CASE WHEN  exists(
						select *   from   v_tempbasevoyage2   
							where   services='TP3-maersk'   
								and   POL   is   not   null   
								and   pol   in(
									select   portfuName from   v_poltrade   where   stshortname='qd'   
									) 
								and   b_portcode <> 0
						)
					THEN '0' ELSE 'a' END,

				RTRIM(b_flag)=
					CASE WHEN  exists(
						select *   from   v_tempbasevoyage2   
							where   services='TP3-maersk'   
								and   POL   is   not   null   
								and   pol   in(
									select   portfuName from   v_poltrade   where   stshortname='qd'   
									) 
								and   b_portcode <> 0
						)
					THEN '1' ELSE 'a' END
			
			) 
		)

[-狙击手-]

搞得太复杂了吧

[fcuandy]

若考滤效率,又想用case when来简化你的语句的话,建议采用折衷的办法:

//设,我理解为你的 flag和b_flag为bit或int型数据,那么它就不可能出现 'a','b'这样的字符值,我要用这样的字符值来构建case when.
DECLARE @b BIT 
SET @b=0
IF exists(
		select *   from   v_tempbasevoyage2   
			where   services='TP3-maersk'   
				and   POL   is   not   null   
				and   pol   in(
					select   portfuName from   v_poltrade   where   stshortname='qd'   
					) 
				and   b_portcode <> 0
	)
SET @b=1


SELECT * FROM v_tempbasevoyage2
	WHERE services='TP3-maersk'
		AND POL IS NULL
		AND
		(
			POL IN (SELECT portfuName FROM v_poltrade WHERE stShortName='qd')
			OR
			(
				RTRIM(flag)=
				CASE WHEN  @b=1 THEN '0' ELSE 'a' END,

				RTRIM(b_flag)=
				CASE WHEN  @b=1 THEN '1' ELSE 'a' END
			) 
		)

[fcuandy]

当然按不同的集合运算来取B,D的话,语句还可以变很多种.
case when也可以用 isnull加nullif替换.
又可以组合出来N种写法.

[ghd2004]

太复杂了，看不懂了。晕
高手阿

[ghd2004]

太复杂了，看不懂了。晕
高手阿

[浩方软件HFWMS]

谢谢各位高手大哥