sql - SQL Server 中动态创建的表 JOIN
问题描述
我有 3 种类型的桌子
- 主要表如下
CREATE TABLE #InitialTable
(
Id int PRIMARY KEY IDENTITY(1,1),
RP varchar(20)
)
INSERT INTO #InitialTable
VALUES ('R1', 'R2', 'R3')
GO
- 表包含动态创建的表信息如下
CREATE TABLE #DynamicTablesInfo
(
Id int PRIMARY KEY IDENTITY(1,1),
RPId int FOREIGN KEY REFERENCES #InitialTable(Id),
TableName varchar(100)
)
GO
INSERT INTO #DynamicTablesInfo
VALUES (1, 'Table_X1'), (2, 'Table_X2'), (3, 'Table_X3')
GO
- 动态创建的表格这些表格可以是任意数量的表格,表格信息在上表中可用。
CREATE TABLE #Table_X1
(
Id int PRIMARY KEY IDENTITY,
Version_Value varchar(100)
)
GO
INSERT INTO #Table_X1
VALUES ('Val_X1_1'), ('Val_X1_2'), ('Val_X1_3')
GO
CREATE TABLE #Table_X2
(
Id int PRIMARY KEY IDENTITY,
Version_Value varchar(100)
)
GO
INSERT INTO #Table_X2
VALUES ('Val_X2_1'), ('Val_X2_2'), ('Val_X2_3')
GO
CREATE TABLE #Table_X3
(
Id int PRIMARY KEY IDENTITY,
Version_Value varchar(100)
)
GO
INSERT INTO #Table_X3
VALUES ('Val_X3_1'), ('Val_X3_2'), ('Val_X3_3')
GO
现在我想在表的帮助下加入InitialTable
动态创建的表( ....) - 怎么做?Table_X1, Table_X2, Table_X3,
DynamicTablesInfo
注意:为了方便更新、删除、插入,我将它们创建为临时表,但在我的应用程序中都是真实表。
解决方案
而不是这样做:
CREATE TABLE #Table_X1
(
Id int PRIMARY KEY IDENTITY
,Version_Value varchar(100)
)
GO
CREATE TABLE #Table_X2
(
Id int PRIMARY KEY IDENTITY
,Version_Value varchar(100)
)
GO
CREATE TABLE #Table_X3
(
Id int PRIMARY KEY IDENTITY
,Version_Value varchar(100)
)
GO
这样做一次:
CREATE TABLE Table_X
(
Id int PRIMARY KEY IDENTITY
,Version_Value varchar(100)
,X_number INT
)
GO
然后不要这样做:
INSERT INTO #Table_X1 VALUES ('Val_X1_1'),('Val_X1_2'),('Val_X1_3')
INSERT INTO #Table_X2 VALUES ('Val_X2_1'),('Val_X2_2'),('Val_X2_3')
INSERT INTO #Table_X3 VALUES ('Val_X3_1'),('Val_X3_2'),('Val_X3_3')
做这个:
INSERT INTO Table_X VALUES ('Val_X1_1',1),('Val_X1_2',1),('Val_X1_3',1)
INSERT INTO Table_X VALUES ('Val_X2_1',2),('Val_X2_2',2),('Val_X2_3',2)
INSERT INTO Table_X VALUES ('Val_X3_1',3),('Val_X3_2',3),('Val_X3_3',3)
在没有动态的情况下更容易查询:
--no
SELECT * FROM Table_X1
--yes
SELECY * FROM Table_X WHERE X_Number = 1
您已经表明您对它的现状感到困惑,因此您也需要动态地创建和运行查询。这是 c#/vb 风格的伪代码:
string sql = "SELECT * FROM sometable"
for int x = 1 to 3
sql = sql + " table_x{x} on sometable.id = table_x{x}.id"
或者也许建立一个联盟:
string sql = "WITH allx AS (SELECT * FROM table_x1"
for int x = 2 to 10
sql = sql + " UNION ALL SELECT * FROM table_x{x}"
sql = sql + ") select * from sometable inner join allx on..."
但我在评论中回应了 larnu 的观点。如果您真的无法更改创建的表,请考虑以与上面的 UNION 代码类似的方式创建一个 VIEW,它将与 X 个表并列,并提供一种查询方式没有动态:
CREATE VIEW AllX AS(
SELECT x.*, 1 as Which FROM TABLE_X1 x
UNION ALL SELECT x.*, 2 as Which FROM TABLE_X2 x
UNION ALL SELECT x.*, 3 as Which FROM TABLE_X3 x
...
使用创建 10 个表的相同技术将查看 10 个表的 CREATE VIEW 语句串在一起,然后您可以在不生成动态 sql 的情况下查询视图