select o.confirmation_no,o.car_no,v.fname,v.vip_card_no,case o.pickup_point when 0 then o.pickup_address else cs.abbr_name end as pickup_address,
case o.dropoff_point when 0 then o.dropoff_address else cs1.abbr_name end as dropoff_address,from_date,to_date,d.fname as pickup_dr_name,d1.fname as dropoff_dr_name,o.status
from operator_self o(nolock)
left outer join vip v(nolock) on o.vip_no=v.vip_no
left outer join car_storehouse cs(nolock) on o.pickup_point=cs.id
left outer join car_storehouse cs1(nolock) on o.dropoff_point=cs1.id
left outer join driver d(nolock) on o.pickup_dr_id=d.dr_id
left outer join driver d1(nolock) on o.dropoff_dr_id=d1.dr_id where 1=1
and (case o.pickup_point when 0 then o.pickup_city in (select city from user_store_right where [user_id]=1)
or case o.dropoff_point when 0 then o.dropoff_city in (select city from user_store_right where [user_id]=1))报错:消息 156,级别 15,状态 1,第 9 行
Incorrect syntax near the keyword 'in'.
消息 156,级别 15,状态 1,第 10 行
Incorrect syntax near the keyword 'or'.我用的是 case when 条件筛选, 当o.pickup_point =0时, 筛选条件o.pickup_city
,或者当o.dropoff_point=0时,筛选条件o.dropoff_city 。
大伙看看那里错了。
case o.dropoff_point when 0 then o.dropoff_address else cs1.abbr_name end as dropoff_address,from_date,to_date,d.fname as pickup_dr_name,d1.fname as dropoff_dr_name,o.status
from operator_self o(nolock)
left outer join vip v(nolock) on o.vip_no=v.vip_no
left outer join car_storehouse cs(nolock) on o.pickup_point=cs.id
left outer join car_storehouse cs1(nolock) on o.dropoff_point=cs1.id
left outer join driver d(nolock) on o.pickup_dr_id=d.dr_id
left outer join driver d1(nolock) on o.dropoff_dr_id=d1.dr_id where 1=1
and (case o.pickup_point when 0 then o.pickup_city in (select city from user_store_right where [user_id]=1)
or case o.dropoff_point when 0 then o.dropoff_city in (select city from user_store_right where [user_id]=1))报错:消息 156,级别 15,状态 1,第 9 行
Incorrect syntax near the keyword 'in'.
消息 156,级别 15,状态 1,第 10 行
Incorrect syntax near the keyword 'or'.我用的是 case when 条件筛选, 当o.pickup_point =0时, 筛选条件o.pickup_city
,或者当o.dropoff_point=0时,筛选条件o.dropoff_city 。
大伙看看那里错了。
这是什么写法。。
where 1=1
and (case o.pickup_point when 0 then o.pickup_city else in (select city from user_store_right where [user_id]=1)
or case o.dropoff_point when 0 then o.dropoff_city else in (select city from user_store_right where [user_id]=1))
你不能用在"根据不同的条件返回一个不同的SQL条件语句(以用于执行)"如
case when State=1 and Joinstate=0 then 2 when State=1 end另外
case when 最后还要有end结束符.
then 后面可以跟表达式吗?我想实现的效果是当o.pickup_point =0执行一个子查询!
不能这样,
你可以用存储过程
如
if xxx=1
select ... from table where 条件A
else
select .... from table Where 条件B或者用拼接SQL的办法
declare @sql nvarchar(200)
set @sql=N'select * from tab where '
if xxx=1
set @sql=@sql+'条件A'
else
set @sql=@sql+'条件B'
EXECUTE sp_executesql @Sql
其实这本来是个存储过程,为了演示代码我把后面的筛选条件省去了点,只剩前面的筛选条件了。还有我把原来存储过程中那个[user_id]=参数变量写成[user_id]=1是为了演示方便。这本来就是个存储过程。
目前有个难题是这个条件是根据查询的列中来的,也就是说,当查询的列o.pickup_point的值为0时筛选条件是。。
当查询的列o.pickup_point不为0时,筛选的条件是
所以我才这样写了。
掉了一个)号
where
(o.pickup_point=0 and o.pickup_city in(....))
or
( o.dropoff_point =0 and o.dropoff_city in(....))
select
o.confirmation_no,
o.car_no,
v.fname,
v.vip_card_no,
(case o.pickup_point when 0 then o.pickup_address else cs.abbr_name end ) as pickup_address,
(case o.dropoff_point when 0 then o.dropoff_address else cs1.abbr_name end ) as dropoff_address,
from_date,
to_date,
d.fname as pickup_dr_name,
d1.fname as dropoff_dr_name,
o.status
from operator_self o(nolock)
left outer join vip v(nolock) on o.vip_no=v.vip_no
left outer join car_storehouse cs(nolock) on o.pickup_point=cs.id
left outer join car_storehouse cs1(nolock) on o.dropoff_point=cs1.id
left outer join driver d(nolock) on o.pickup_dr_id=d.dr_id
left outer join driver d1(nolock) on o.dropoff_dr_id=d1.dr_id
where 1=1
and
(
pickup_point =
(
case o.pickup_point when 0 then
(
select city from user_store_right where [user_id]=1
)
else
pickup_point
end
or
dropoff_point =
case o.dropoff_point when 0 then
(
select city from user_store_right where [user_id]=1
)
else
dropoff_point
end
)
where 1=1
and
(
pickup_point =
(
case o.pickup_point when 0 then
(
select city from user_store_right where [user_id]=1
)
else
pickup_point
end
)
or
dropoff_point =
(
case o.dropoff_point when 0 then
(
select city from user_store_right where [user_id]=1
)
else
dropoff_point
end
)
)