累积200分再次发问!!
主 题: 如果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
忘了在最外面加一个括号了