累积200分再次发问!!

[伊凡yifan]

主　　题： 如果business_code是多个记录。。。
作　　者： Yifan (最爱黑夜的鸟)
等　　级：
信 誉 值： 100
所属论坛： MS-SQL Server 基础类
问题点数： 100
回复次数： 10
发表时间： 2003-09-29 15:48:08



如果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)


应该这样写吗？





回复人： letsflytogether(恨！不能拥有天下所有的财富，然后平分) ( ) 信誉：100 2003-09-29 16:00:00 得分:0


如果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+')'

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


Top

回复人： viptiger(六嘎) ( ) 信誉：100 2003-09-29 16:00:00 得分:0


如果business_code是多条记录。。

select price from serivce_id where in(select service_id from business where business_code in (@business_code,@a,@b,@c,@d,.....)


Top

回复人： txlicenhe(马可) ( ) 信誉：102 2003-09-29 16:02:00 得分:0


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)



Top

回复人： txlicenhe(马可) ( ) 信誉：102 2003-09-29 16:02:00 得分:0


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)



Top

回复人： pengdali(大力 V3.0) ( ) 信誉：541 2003-09-29 16:09:00 得分:0


exec('select price from serivce_id where in(select service_id from business where business_code in ('+@business_code+'))')


Top

回复人： pengdali(大力 V3.0) ( ) 信誉：541 2003-09-29 16:14:00 得分:0


举例：

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+'))')


Top

回复人： aierong(皑婀瑢-数据库XML.NET联盟会处长) ( ) 信誉：100 2003-09-29 16:17:00 得分:0


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



Top

回复人： Yifan(最爱黑夜的鸟) ( ) 信誉：100 2003-09-29 16:19:00 得分:0


关键是我需要这条语句直接 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>


Top

回复人： Yifan(最爱黑夜的鸟) ( ) 信誉：100 2003-09-29 16:21:00 得分:0


数组@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+')'



Top

回复人： letsflytogether(恨！不能拥有天下所有的财富，然后平分) ( ) 信誉：100 2003-09-29 16:41:00 得分:0


忘了在最外面加一个括号了

[yujohny]

楼主还没得到答案吗？

[伊凡yifan]

是啊，困或啊...

[txlicenhe]

不外乎上面几种写法了。

[pengdali]

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+')) for xml auto')

[yujohny]

那就是你的需求没说清楚
马可兄已经说的够详细了
你还要什么要求？？？

[伊凡yifan]

关键是我需要这条语句直接 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>


Top

回复人： Yifan(最爱黑夜的鸟) ( ) 信誉：100 2003-09-29 16:21:00 得分:0


数组@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]

因为 SMS_NORMAL_PRICE就是business_code的值，那么
还可以写成这样吗？

exec 'select business_code.price from serivce_id where in(select service_id from business where business_code in '+@business_code+')'

[伊凡yifan]

for xml auto 是生成xml文件，就是我帖上来的那段xml文件格式