用于根据来自不同表的条件从多个表中选择行的功能

Can anyone please help with this query?

任何人都可以请求帮助这个查询?

I’m using SQL server 2008 . Objective is to select rows from multiple tables based on condition and values from different tables .

我正在使用SQL Server 2008。目标是根据不同表中的条件和值从多个表中选择行。

I have table1, table2, tableN with columns as ID,ColumnName,ColumnValue . These are the table I need to select rows based on conditions from below table

我有table1,table2,tableN,列为ID,ColumnName,ColumnValue。这些是我需要根据下表中的条件选择行的表

Control table with columns Number,Function and Enable

带有列号,功能和启用的控制表

Repository table with columns Function and tableName

具有列Function和tableName的存储库表

I need pass Number and ID as parameters and get details of all Function values from Control table which has Enable value = 1 and by using these Function values collect tableNames from Repository table . And for each tableName returned from Repository table get all rows by using ID value.

我需要传递Number和ID作为参数,并获取Control table中具有Enable value = 1的所有Function值的详细信息,并使用这些函数值从Repository表中收集tableNames。对于从Repository表返回的每个tableName,使用ID值获取所有行。

1 个解决方案

#1

The way I understand it you have two tables with schema like this:

我理解它的方式你有两个表格,如下所示:

table Control (Number int, Function nvarchar, Enable bit)
table Repository (Function nvarchar, TableName nvarchar)

Control and Repositories are related via Function column.

控制和存储库通过“功能”列相关联。

You also have a number of other tables and names of those tables are saved in Repositories tables. All those tables have ID column.

您还有许多其他表,这些表的名称保存在存储库表中。所有这些表都有ID列。

You want to get those table names based on a number and then select from all those tables by their ID column.

您希望根据数字获取这些表名,然后按ID列从所有这些表中进行选择。

If that indeed is what you are trying to do, code bellow should be enough to solve your problem.

如果这确实是你想要做的,那么代码应该足以解决你的问题。

declare
    -- arguments
    @id int = 123,
    @number int = 123456,
    -- helper variables we'll use along the way
    @function nvarchar(4000),
    @tableName nvarchar(256),
    @query nvarchar(4000)

-- create cursor to iterate over every returned row one by one
declare cursor #tables readonly fast_forward
for
select
    c.Function,
    r.TableName
from [Control] as c
join [Repository] as r on r.Function = c.Function
where c.Number = @number
and c.Enable = 1

-- initialise cursor
open #tables
-- get first row into variables
fetch next from #tables
    into @function, @tableName

-- will be 0 as long as fetch next returns new values
while @@fetch_status = 0
begin
    -- build a dynamic query
    set @query = 'select * from ' + @tableName + ' where ID = ' + @id

    -- execute dynamic query. you might get permission problems
    -- dynamic queries are best to avoid, but I don't think there's another solution for this
    exec(@query)

    -- get next row
    fetch next from #tables
        into @function, @tableName
end

-- destroy cursor
close #tables
deallocate #tables

#1