如果business_code是多个记录。。。

伊凡yifan 2003-09-29 03:54:23
如果business_code是单条记录。。
select price from serivce_id where in(select service_id from business where business_code = @business_code)

如果business_code是多条记录。。
select price from serivce_id where in(select service_id from business where business_code in @business_code)


应该这样写吗?

...全文
125 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
伍子V5 2003-09-29
  • 打赏
  • 举报
回复
忘了在最外面加一个括号了
伊凡yifan 2003-09-29
  • 打赏
  • 举报
回复
数组@business_code 在select price中可以形成节点方式出来

比如说
exec 'select price(1) as SMS_NORMAL_PRICE,price(2) as SMS_KING_PRICE from serivce_id where in(select service_id from business where business_code in '+@business_code+')'
伊凡yifan 2003-09-29
  • 打赏
  • 举报
回复
关键是我需要这条语句直接 for xml auto出来的

那么形成的xml文件格式为

<PRICES xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="E:\Project\XMLDefine\Prices.xsd">
<SMS_NORMAL_PRICE>123</SMS_NORMAL_PRICE>
<SMS_KING_PRICE>234</SMS_KING_PRICE>
<SMS_FLASH_PRICE>345</SMS_FLASH_PRICE>
<MMS_TEXT_PRICE>456</MMS_TEXT_PRICE>
<MMS_PIC_PRICE>567</MMS_PIC_PRICE>
<MMS_ANI_PRICE>678</MMS_ANI_PRICE>
<MMS_SOUND_PRICE>789</MMS_SOUND_PRICE>
</PRICES>
aierong 2003-09-29
  • 打赏
  • 举报
回复
declare @business_code varchar(1000),@sql nvarchar(1000)
set @business_code='111,222,333'
select @sql='select price from serivce_id where service_id in (select service_id from business where business_code in ('+@business_code+'))'
exec sp_executesql @sql
pengdali 2003-09-29
  • 打赏
  • 举报
回复
举例:

declare @business_code varchar(100)
set @business_code='''sdfs'',''sdfas'',''sdf'''

exec('select price from serivce_id where in(select service_id from business where business_code in ('+@business_code+'))')
pengdali 2003-09-29
  • 打赏
  • 举报
回复
exec('select price from serivce_id where in(select service_id from business where business_code in ('+@business_code+'))')
txlicenhe 2003-09-29
  • 打赏
  • 举报
回复
1:单条
select price from serivce_id where in(select service_id from business where business_code = @business_code)

select price,bussiness_code from serivce_id a
where in(select service_id from business where business_code = a.business_code)

2:多条
select price,bussiness_code from serivce_id a
where in(select service_id from business where business_code = a.business_code)

3:不管单条还是多条都可以:
select price,bussiness_code from serivce_id a
where in(select service_id from business where business_code = a.business_code)
txlicenhe 2003-09-29
  • 打赏
  • 举报
回复
1:单条
select price from serivce_id where in(select service_id from business where business_code = @business_code)

select price,bussiness_code from serivce_id a
where in(select service_id from business where business_code = a.business_code)

2:多条
select price,bussiness_code from serivce_id a
where in(select service_id from business where business_code = a.business_code)

3:不管单条还是多条都可以:
select price,bussiness_code from serivce_id a
where in(select service_id from business where business_code = a.business_code)
viptiger 2003-09-29
  • 打赏
  • 举报
回复
如果business_code是多条记录。。

select price from serivce_id where in(select service_id from business where business_code in (@business_code,@a,@b,@c,@d,.....)
伍子V5 2003-09-29
  • 打赏
  • 举报
回复
如果business_code是多条记录。。

declare @business_code varchar(1000)
set @business_code='(111,222,333)'
exec 'select price from serivce_id where in(select service_id from business where business_code in '+@business_code+')'

反正最终你要组合成符合规范的语句就行

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧