Join中的case语句 [英] case statement in Join
本文介绍了Join中的case语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
选择 col1
来自 table1
case @ col2 null 然后 左 外 加入 其他 join end
table2 on ( join 条件)
以上是我的查询,我想要根据1条件选择左外连接或右外连接。
我想知道更好的解决方案来实现上述问题
解决方案
使用动态Q uery ....在Google搜索
如何在sql中编写动态查询 [ ^ ]
DECLARE @ col2 INT , @ join VARCHAR ( 100 ),< span class =code-sdkkeyword> @ query NVARCHAR ( 1000 )
SELECT @ join = CASE WHEN @ col2 IS NULL 那么 ' left outer join' ELSE < span class =code-string>' join' END
< span class =code-keyword> SELECT @ query = ' 从[选择*
来自[ table1]' + @ join +
' [table2] c on [Join condition]'
EXEC SP_EXECUTESQL @ query
两种方式
1。(示例)
声明 @ col2 int
set @ col2 = NULL ; - value为null它将仅为LEFT OUTER JOIN提供结果
- 将值更改为null,只会为JOIN提供结果
为
(选择 1 a
union all
选择 2 as a)
选择 *
来自 a
left 外部 join
(选择 1as a,' a' as b) as b on aa = ba
其中
aa =( case when @ col2 null aa else ba end )
所以,按照解决方案1设置你的查询这样
选择 col1
来自 table1
left outer join
table2 on table1.col1 = table2.co l1
其中 table1.col1 = ( case 何时 @ col2 null 然后 table1.col1 else table2.col1 结束)
2。
if ( @ col2 null )
begin
选择 col1
来自 table1
left outer join
table2 o n table1.col1 = table2.col1
end
else
开始
选择 col1
来自 table1
join
table2 table1.col1 = table2.col1
结束
快乐编码!
:)
select col1
from table1
case when @col2 is null then left outer join else join end
table2 on (join condition)
Above is my query, i want to choose between left outer or right outer join based on 1 condition.
I want to know a better solution for implementing the above problem
解决方案
Hi,
Use Dynamic Query.... Search in Google
how to write a dynamic query in sql[^]
DECLARE @col2 INT,@join VARCHAR(100),@query NVARCHAR(1000) SELECT @join=CASE WHEN @col2 IS NULL THEN 'left outer join' ELSE 'join'END SELECT @query='select * from [table1] '+@join + ' [table2] c on [Join condition]' EXEC SP_EXECUTESQL @query
Two ways
1. (example)
declare @col2 int set @col2 = NULL ; -- value is null it will give result for LEFT OUTER JOIN only --change value null to something else it will give result for JOIN only with a as (select 1 as a union all select 2 as a) select * from a left outer join (select 1 as a, 'a' as b) as b on a.a=b.a where a.a =( case when @col2 is null then a.a else b.a end)
so,as per solution 1 set your query like this
select col1 from table1 left outer join table2 on table1.col1 =table2.col1 where table1.col1 = (case when @col2 is null then table1.col1 else table2.col1 end)
2.
if (@col2 is null) begin select col1 from table1 left outer join table2 on table1.col1=table2.col1 end else begin select col1 from table1 join table2 on table1.col1=table2.col1 End
Happy Coding!
:)
这篇关于Join中的case语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文