IF (SELECT [column] FROM TABLE1)=1 SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.ID=TABLE2.ID ELSE IF(SELECT [column] FROM TABLE1)=2 SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.ID=TABLE2.ID
用动态语句拼接吧。 declare @sql varchar(500) select @sql='select * from table1' if exists(select 1 from table1 where a=1) begin select @sql=@sql+' inner join' table2 on....' end if exists(select 1 from table1 where a=2) begin select @sql=@sql+' inner join' table2 on....' end exec(@sql)
2楼明白我的意思了。select * from table1 case table1.column when 1 then inner join table2 on... when 2 then inner join table3 on... end 我试试先。
--这样? IF EXISTS(SELECT 1 FROM TABLE1 WHERE 字段=1) SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.ID=TABLE2.ID ELSE IF EXISTS(SELECT 1 FROM TABLE1 WHERE 字段=2) SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.ID=TABLE2.ID
?这样 IF (SELECT [column] FROM TABLE1 where id=2241) select .... if (SELECT [column] FROM TABLE1 where id=2596) select ....
这样的话,case when也没法搞 只能列举了if exists(select 1 from table1 where [column]=1 and [column]=2) select * from table1 left join a,left join b on .... else if exists(select 1 from table1 where [column]=1) select * from table1 join a if exists(select 1 from table1 where [column]=2) select * from table1 join b ......
使用动态语句可否?declare @s varchar(max),@type int set @type=1 set @s='select * from tb '+case when @type=1 then 'join tb1 on tb.id=tb1.id' when @type=2 then 'join tb2 on tb.id=tb2.id'endprint @s 结果: select * from tb join tb1 on tb.id=tb1.iddeclare @s varchar(max),@type int set @type=2 set @s='select * from tb '+case when @type=1 then 'join tb1 on tb.id=tb1.id' when @type=2 then 'join tb2 on tb.id=tb2.id'endprint @s结果: select * from tb join tb2 on tb.id=tb2.id
SELECT DISTINCT p.ID,p.PriceName,p.Transit,p.MoneyType,p.Style--此列用于连接条件 FROM sloa.dbo.Price AS p--如果为1就连接Price1.如果为2就连接price2 INNER JOIN sloa.dbo.Price1 AS p1 ON p1.PriceID=p.ID INNER JOIN sloa.dbo.[Exp] AS e ON e.PriceID=p.ID AND p1.MeterageID=e.MeterageID WHERE p.[type]=0--只查询内部报价和不限内外的报价 AND (p.DateStart<=GETDATE() AND p.DateEnd>=GETDATE())--在有效期内 AND p.validity=1--有效的报价表 AND p.Transit LIKE '%ups%'--指定运输方式的报价表 AND p.companyuse LIKE '%szsl%'--只限查询公司使用的报价表 AND p.priority>0--报价表优先级大于0 测试在输入的国家是否在报价表服务之列,以排除此报价不走的国家的报价表 AND ((EXISTS(SELECT * FROM sloa.dbo.Price1 --国家名存在 WHERE CountryNo='us' AND PriceID=p.ID)) OR(EXISTS(SELECT * FROM sloa.dbo.Region1--或者分区中存在 WHERE RegionID=p.RegionID AND CountryNo='us'))) AND 20>p1.WTStart AND 20<=p1.WTEnd AND p1.PackDoc='wpx' AND p1.CountryNo='us' 贴全部代码吧,太长了。怕大家看到就跑了。
--如果为1就连接Price1.如果为2就连接price2这个是如果什么为1呢 你可以先作判断,用字符串连接的办法查询。 declare @s varchar(8000) if exists(select 1 from table1 where a=1) set @s='SELECT DISTINCT p.ID,p.PriceName,p.Transit,p.MoneyType,p.Style--此列用于连接条件 FROM sloa.dbo.Price AS p--如果为1就连接Price1.如果为2就连接price2 INNER JOIN sloa.dbo.Price1 AS p1 ON p1.PriceID=p.ID INNER JOIN sloa.dbo.[Exp] AS e ON e.PriceID=p.ID AND p1.MeterageID=e.MeterageID ' else if exists(select 1 from table1 where a=2) set @s='SELECT DISTINCT p.ID,p.PriceName,p.Transit,p.MoneyType,p.Style--此列用于连接条件 FROM sloa.dbo.Price AS p--如果为1就连接Price1.如果为2就连接price2 INNER JOIN sloa.dbo.Price2 AS p1 ON p1.PriceID=p.ID INNER JOIN sloa.dbo.[Exp] AS e ON e.PriceID=p.ID AND p1.MeterageID=e.MeterageID ' set @s=@s+' WHERE p.[type]=0--只查询内部报价和不限内外的报价 AND (p.DateStart<=GETDATE() AND p.DateEnd>=GETDATE())--在有效期内 AND p.validity=1--有效的报价表 AND p.Transit LIKE ''%ups%''--指定运输方式的报价表 AND p.companyuse LIKE ''%szsl%''--只限查询公司使用的报价表 AND p.priority>0--报价表优先级大于0 --测试在输入的国家是否在报价表服务之列,以排除此报价不走的国家的报价表 AND ((EXISTS(SELECT * FROM sloa.dbo.Price1 --国家名存在 WHERE CountryNo=''us'' AND PriceID=p.ID)) OR(EXISTS(SELECT * FROM sloa.dbo.Region1--或者分区中存在 WHERE RegionID=p.RegionID AND CountryNo=''us''))) AND 20>p1.WTStart AND 20<=p1.WTEnd AND p1.PackDoc=''wpx'' AND p1.CountryNo=''us''' exec(@s)
SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.ID=TABLE2.ID
ELSE IF(SELECT [column] FROM TABLE1)=2
SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.ID=TABLE2.ID
用动态语句拼接吧。
declare @sql varchar(500)
select @sql='select * from table1'
if exists(select 1 from table1 where a=1)
begin
select @sql=@sql+' inner join' table2 on....'
end
if exists(select 1 from table1 where a=2)
begin
select @sql=@sql+' inner join' table2 on....'
end
exec(@sql)
case table1.column
when 1 then inner join table2 on...
when 2 then inner join table3 on...
end 我试试先。
IF (SELECT [column] FROM TABLE1)=1的方式的话。
这是我table1中的数据
2241 香港UPS UPS RMB 2
2596 香港UPS全区特惠价 UPS RMB 2
2282 香港UPS部分国家特惠价 UPS RMB 2
2131 日本专线特惠价 SL专线 RMB 1
2569 越南专线 SL专线 RMB 1
这样的结果就不止一个。
IF EXISTS(SELECT 1 FROM TABLE1 WHERE 字段=1)
SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.ID=TABLE2.ID
ELSE IF EXISTS(SELECT 1 FROM TABLE1 WHERE 字段=2)
SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.ID=TABLE2.ID
?这样
IF (SELECT [column] FROM TABLE1 where id=2241)
select ....
if (SELECT [column] FROM TABLE1 where id=2596)
select ....
只能列举了if exists(select 1 from table1 where [column]=1 and [column]=2)
select * from table1 left join a,left join b on ....
else
if exists(select 1 from table1 where [column]=1)
select * from table1 join a
if exists(select 1 from table1 where [column]=2)
select * from table1 join b
......
set @type=1
set @s='select * from tb '+case when @type=1 then 'join tb1 on tb.id=tb1.id' when @type=2 then 'join tb2 on tb.id=tb2.id'endprint @s
结果:
select * from tb join tb1 on tb.id=tb1.iddeclare @s varchar(max),@type int
set @type=2
set @s='select * from tb '+case when @type=1 then 'join tb1 on tb.id=tb1.id' when @type=2 then 'join tb2 on tb.id=tb2.id'endprint @s结果:
select * from tb join tb2 on tb.id=tb2.id
FROM sloa.dbo.Price AS p--如果为1就连接Price1.如果为2就连接price2
INNER JOIN sloa.dbo.Price1 AS p1
ON p1.PriceID=p.ID
INNER JOIN sloa.dbo.[Exp] AS e
ON e.PriceID=p.ID AND p1.MeterageID=e.MeterageID
WHERE p.[type]=0--只查询内部报价和不限内外的报价
AND (p.DateStart<=GETDATE() AND p.DateEnd>=GETDATE())--在有效期内
AND p.validity=1--有效的报价表
AND p.Transit LIKE '%ups%'--指定运输方式的报价表
AND p.companyuse LIKE '%szsl%'--只限查询公司使用的报价表
AND p.priority>0--报价表优先级大于0
测试在输入的国家是否在报价表服务之列,以排除此报价不走的国家的报价表
AND ((EXISTS(SELECT * FROM sloa.dbo.Price1 --国家名存在
WHERE CountryNo='us'
AND PriceID=p.ID))
OR(EXISTS(SELECT * FROM sloa.dbo.Region1--或者分区中存在
WHERE RegionID=p.RegionID
AND CountryNo='us')))
AND 20>p1.WTStart
AND 20<=p1.WTEnd
AND p1.PackDoc='wpx'
AND p1.CountryNo='us'
贴全部代码吧,太长了。怕大家看到就跑了。
你可以先作判断,用字符串连接的办法查询。
declare @s varchar(8000)
if exists(select 1 from table1 where a=1)
set @s='SELECT DISTINCT p.ID,p.PriceName,p.Transit,p.MoneyType,p.Style--此列用于连接条件
FROM sloa.dbo.Price AS p--如果为1就连接Price1.如果为2就连接price2
INNER JOIN sloa.dbo.Price1 AS p1
ON p1.PriceID=p.ID
INNER JOIN sloa.dbo.[Exp] AS e
ON e.PriceID=p.ID AND p1.MeterageID=e.MeterageID '
else if exists(select 1 from table1 where a=2)
set @s='SELECT DISTINCT p.ID,p.PriceName,p.Transit,p.MoneyType,p.Style--此列用于连接条件
FROM sloa.dbo.Price AS p--如果为1就连接Price1.如果为2就连接price2
INNER JOIN sloa.dbo.Price2 AS p1
ON p1.PriceID=p.ID
INNER JOIN sloa.dbo.[Exp] AS e
ON e.PriceID=p.ID AND p1.MeterageID=e.MeterageID '
set @s=@s+' WHERE p.[type]=0--只查询内部报价和不限内外的报价
AND (p.DateStart<=GETDATE() AND p.DateEnd>=GETDATE())--在有效期内
AND p.validity=1--有效的报价表
AND p.Transit LIKE ''%ups%''--指定运输方式的报价表
AND p.companyuse LIKE ''%szsl%''--只限查询公司使用的报价表
AND p.priority>0--报价表优先级大于0
--测试在输入的国家是否在报价表服务之列,以排除此报价不走的国家的报价表
AND ((EXISTS(SELECT * FROM sloa.dbo.Price1 --国家名存在
WHERE CountryNo=''us''
AND PriceID=p.ID))
OR(EXISTS(SELECT * FROM sloa.dbo.Region1--或者分区中存在
WHERE RegionID=p.RegionID
AND CountryNo=''us'')))
AND 20>p1.WTStart
AND 20<=p1.WTEnd
AND p1.PackDoc=''wpx''
AND p1.CountryNo=''us'''
exec(@s)
http://topic.csdn.net/u/20081111/07/a6346cdc-0d9f-4f5a-bcb1-00cba3791c9c.htmlhttp://topic.csdn.net/u/20081115/00/17588ccc-68be-4cac-8036-1bc3f78c7b3f.html一个是xml方式一条语句实现(性能较差),一个是动态语句实现。