如何有条件地将内部联接添加到 select 语句?

发布于 2024-11-16 11:24:36 字数 433 浏览 1 评论 0原文

我有一个变量,我需要检查它的值是什么,并且我需要根据该值对不同的表进行内部联接。这是我要问的一个例子...

Declare @category nvarchar(100)

select *  
from tableA a  
if (@category = 'all)  
begin  
    inner join tableB b on b.ID = a.ID  
end  
else if (@category = 'open')  
begin  
    inner join tableC c on c.ID = a.ID  
end  

更新:我想我应该包括我正在使用通用表表达式,并且我已经尝试使用动态sql,甚至使用 if 语句来调用不同的 CTE,但似乎CTE 不喜欢任何东西,只喜欢简单的 select 语句。

感谢您对我能得到的任何建议。

I have a variable that I need to check to see what the value is and I need to do an inner join to different tables based on what that value is. Here's an example of what I'm asking...

Declare @category nvarchar(100)

select *  
from tableA a  
if (@category = 'all)  
begin  
    inner join tableB b on b.ID = a.ID  
end  
else if (@category = 'open')  
begin  
    inner join tableC c on c.ID = a.ID  
end  

UPDATE: I think I should have included that I'm using Common Table Expressions and I've tried using dynamic sql, and even doing if statements to call different CTE's, but it seems that CTE's do not like anything but a simple select statement.

Thanks for any advice I can get on this.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

暗恋未遂 2024-11-23 11:24:36

一种方法是:

select * 
from tableA a 
inner join tableB b on b.ID = a.ID
where @category = 'all'
union all
select * 
from tableA a 
inner join tableC c on c.ID = a.ID  
where @category = 'open'

One approach is:

select * 
from tableA a 
inner join tableB b on b.ID = a.ID
where @category = 'all'
union all
select * 
from tableA a 
inner join tableC c on c.ID = a.ID  
where @category = 'open'
蓝礼 2024-11-23 11:24:36

将整个查询设为 varchar,然后使用动态执行。

declare @sqltorun as varchar(max)
set @sqltorun = 'select *  from tableA a '
if(@Category=='a')
@sqltorun = @sqltorun + 'inner join tableB b on b.ID = a.ID   '
else
@sqltorun = @sqltorun + 'inner join tableB b on b.ID = a.ID '
sp_executesql @sqltorun

Make your whole query a varchar, then use dynamic execution.

declare @sqltorun as varchar(max)
set @sqltorun = 'select *  from tableA a '
if(@Category=='a')
@sqltorun = @sqltorun + 'inner join tableB b on b.ID = a.ID   '
else
@sqltorun = @sqltorun + 'inner join tableB b on b.ID = a.ID '
sp_executesql @sqltorun
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文