一个表中存的是表名和对应的id，根据表名和id，求该表明的其他字段,该怎么解决

一个表中存的是表名和对应的id，根据表名和id，求该表明的其他字段

SQL code

CREATE TABLE table_yuwen(    table_id int,    table_chengji int,    table_type varchar(10))INSERT INTO table_yuwen select 1,70,'语文' union select 2,80,'语文'create table table_shuxue(    table_id int,    table_chengji int,    table_type varchar(10))insert into table_shuxueselect 1,70,'数学' union select 2,90,'数学'create table lock_table(    lock_id int,    lock_table varchar(20),    --表名    table_id int            --表名id)insert into lock_tableselect 1,'table_yuwen',1 union select 2,'table_shuxue',1/*要求结果：lock_id     lock_table           table_id    table_type table_chengji----------- -------------------- ----------- ---------- -------------1           table_yuwen          1           语文         702           table_shuxue         1           数学         70(2 行受影响)*/

SQL code

drop table table_yuwendrop table table_shuxuedrop table lock_tableCREATE TABLE table_yuwen(    table_id int,    table_chengji int,    table_type nvarchar(10))INSERT INTO table_yuwen select 1,70,N'语文' union select 2,80,N'语文'create table table_shuxue(    table_id int,    table_chengji int,    table_type nvarchar(10))insert into table_shuxueselect 1,70,N'数学' union select 2,90,N'数学'create table lock_table(    lock_id int,    lock_table varchar(20),    --表名    table_id int            --表名id)insert into lock_tableselect 1,'table_yuwen',1 union select 2,'table_shuxue',1declare @sql nvarchar(max)select @sql=isnull(@sql,'')+stuff((select ' union all '+('select *,'''+lock_table+''' lock_table from '+lock_table+' where table_id='+ltrim(table_id)) from lock_tablefor xml path('')),1,10,'')select @sql='select lock_id,tt.* from lock_table t,('+@sql+')tt where t.table_id=tt.table_id and tt.lock_table=t.lock_table 'exec(@sql)/*lock_id     table_id    table_chengji table_type lock_table----------- ----------- ------------- ---------- ------------1           1           70            语文         table_yuwen2           1           70            数学         table_shuxue*/
[解决办法]
SQL code
DECLARE @row INT,@i INT,@sql VARCHAR(MAX)SET @i=1IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #tSELECT DISTINCT id=IDENTITY(INT,1,1),lock_table INTO #t FROM lock_table SET @row=@@ROWCOUNTWHILE @i<=@row    BEGIN         SELECT  @sql=ISNULL(@sql+CHAR(10)+'UNION ALL'+CHAR(10),'')+'SELECT * FROM lock_table AS a JOIN '+lock_table+                    ' AS b ON a.table_id=b.table_id AND lock_table='''+lock_table+''''        FROM #t WHERE id=@i        SET @i=@i+1    END EXEC(@sql)