设置用于多个SQL select查询的条件

Need to set criteria for running multiple queries, but only want to change once. For example, want to set year, period, document so that...

需要为运行多个查询设置条件，但只需要更改一次。例如，要设置年份、期间、文档以便……

select * from tbl1 where tbl1.yr = year and ...
select * from tbl2 where tbl2.yr = year, and ...

3 个解决方案

#1

Create a view:

创建一个视图:

CREATE VIEW yourview AS
SELECT * from tbl1
UNION ALL
SELECT * from tbl2

Then query it:

然后查询:

 SELECT * FROM yourview
 WHERE tbl1.yr = year AND ...

You may also want to know from which table each row comes. This can be achieved by adding an extra column to the view:

您可能还想知道每行来自哪个表。这可以通过向视图添加额外的列来实现:

CREATE VIEW yourview AS
SELECT 'tbl1' AS src, * from tbl1
UNION ALL
SELECT 'tbl2' AS src, * from tbl2

#2

If they're truly different queries, not related, you may have to resort to Dynamic SQL, e.g.

如果它们是真正不同的查询，而不是相关的查询，您可能需要使用动态SQL，例如。

DECLARE @sCondition VARCHAR(50)

SET @sCondition = 'yr = 2012'

DECLARE @sQuery1 VARCHAR(1000)

SET @sQuery1 = 'select * from tbl1 where ' + @sCondition

-- DECLARE other queries in similar faction OR combine multiple queries into single variable

EXEC (@sQuery1)

#3

With CTE; Note: you should have same number of columns and matching datatypes from both tables as you are doing a blind union of select *

CTE;注意:在执行select *的盲目联合时，您应该拥有来自两个表的相同数量的列和匹配数据类型

;with cte (myYear)
as (
   select @year as myYear
)
select * from table1 t1 where t1.year  in (select myYear from cte)
union all
select * from table2 t2 where t2.year  in (select myYear from cte)

#1