IF (OBJECT_ID('Dictionary') IS NOT NULL)
DROP TABLE Dictionary
CREATE TABLE Dictionary
(
ID INT IDENTITY(1,1) PRIMARY KEY,
RelatedID INT,
TableName VARCHAR(20), --表名称
Realname VARCHAR(20) --真实字段名称
)
INSERT INTO Dictionary
SELECT 1,'DedicatedMeas','RxLevFull' UNION ALL
SELECT 1,'DedicatedMeas','RxLevSub' UNION ALL
SELECT 1,'ServingCellInfo','T3212' UNION ALL
SELECT 1,'ServingCellInfo','BCCH' UNION ALL
SELECT 1,'GPRSMeasCommon','RLCThrDL' UNION ALL
SELECT 1,'GPRSMeasCommon','RLCThrUL'
--SELECT * FROM Dictionary d--1 问题:使用dictinct函数时如何在第一列加入自动增长列
--SELECT ROW_NUMBER() OVER (ORDER BY tablename),DISTINCT(tablename) FROM Dictionary d (错误)
--2. dictinct函数 和字符串拼接
-- DECLARE @tables VARCHAR(2000) SET @tables=''
-- SELECT @tables=@tables+','+DISTINCT(tablename) FROM Dictionary d DECLARE @tables VARCHAR(2000),@fields VARCHAR(6000),@sql VARCHAR(6000)
SELECT @fields='',@tables='',@sql='select tb.*,{fields} from tb t {tables}'最后 字符串显示的结果为
select tb.*,a.RxLevFull,a.RxLevSub,c.T3212,c.BCCH,b.RLCThrDL,b.RLCThrUL from tb t
left join DedicatedMeas a on a.relatedid=t.id
left join GPRSMeasCommon b on b.relatedid=t.id
left join ServingCellInfo c on c.relatedid=t.id
我虽然已经写出来了,但是 感觉 不是很简单
代码如下IF (OBJECT_ID('tempdb..#temp') IS NOT NULL)
DROP TABLE #temp
SELECT rid,tablename tablename,SUBSTRING('abcdefghijklmn',rid,1) AS alias INTO #temp FROM (
SELECT ROW_NUMBER() OVER (ORDER BY tablename) AS rid,b2.tablename FROM (
SELECT DISTINCT(tablename) FROM Dictionary d
) b2
) b3
--SELECT * FROM #temp
--表名拼接
SELECT @tables=@tables+'left join '+tablename+' '+alias+' on '+alias+'.relatedid=t.id ' FROM #temp
--字段名拼接
SELECT @fields=@fields+b4.alias+'.'+d.Realname+',' FROM Dictionary d INNER JOIN #temp b4 ON d.TableName=b4.tablename
SET @fields=LEFT(@fields,LEN(@fields)-1)
SET @sql=REPLACE(@sql,'{fields}',@fields)
SET @sql=REPLACE(@sql,'{tables}',@tables)
print @sql
不知有没有其他一些更好更简单的方法。
请各位 高手 赐教!!
解决方案 »
- sql 触发器问题
- 针对这个表要怎么查?
- 重装了win2003系统和sqlserver2000数据库,不知道为什么原来的数据库里时间字段变成 2006-2-21 下午 01:49:06 这种格式了,怎么回事?
- 求一条语句,非常简单的一条.
- 修改数据库表字段报 不能创建大小为 8070 的行,该大小大于所允许的最大行大小 8060。
- 请问group by在union中如何使用
- --------------数据库的一个设计问题------------------
- 游标与循环如何互相转换?
- 简单的SQL问题,谢谢了
- EXCEL导入至SQLSERVER中时,因某字段数据量过大而导致“作为源列的数据对于指定的缓冲区来讲太大”,如何解决
- 为什么总有一行所有字段均为 NULL 的行呢???
- sql server 2000 怎么存储大量图片和大量文献资料
@fields=isnull(@fields+',','')+'t'+ltrim(px)+'.'+realname
from
(select px=(select count(distinct tablename)
from Dictionary where id<=t1.id),*
from Dictionary t1
) t2
select
@tables=isnull(@tables+' ','')+' left join '+TableName+' t'+ltrim(px)+' on t'+ltrim(px)+'.relatedid=t.id'+char(10)
from
(select distinct
px=(select count(distinct tablename) from Dictionary where id<=t1.id),TableName
from Dictionary t1
) t2set @sql='select tb.*,'+@fields+' from tb t '+char(10)+@tablesselect @sql
/**-----------------------------------------------------------------------------------------------------------------------------------------
select tb.*,t1.RxLevFull,t1.RxLevSub,t2.T3212,t2.BCCH,t3.RLCThrDL,t3.RLCThrUL from tb t
left join DedicatedMeas t1 on t1.relatedid=t.id
left join ServingCellInfo t2 on t2.relatedid=t.id
left join GPRSMeasCommon t3 on t3.relatedid=t.id
(1 行受影响)**/
@fields=isnull(@fields+',','')+char(96+px)+'.'+realname
from
(select px=(select count(distinct tablename)
from Dictionary where id<=t1.id),*
from Dictionary t1
) t2
select
@tables=isnull(@tables+' ','')+' left join '+TableName+' '+char(96+px)+' on '+char(96+px)+'.relatedid=t.id'+char(10)
from
(select distinct
px=(select count(distinct tablename) from Dictionary where id<=t1.id),TableName
from Dictionary t1
) t2set @sql='select tb.*,'+@fields+' from tb t '+char(10)+@tablesselect @sql
/**
select tb.*,a.RxLevFull,a.RxLevSub,b.T3212,b.BCCH,c.RLCThrDL,c.RLCThrUL from tb t
left join DedicatedMeas a on a.relatedid=t.id
left join ServingCellInfo b on b.relatedid=t.id
left join GPRSMeasCommon c on c.relatedid=t.id
**/
IF (OBJECT_ID('Dictionary') IS NOT NULL)
DROP TABLE Dictionary
CREATE TABLE Dictionary
(
ID INT IDENTITY(1,1) PRIMARY KEY,
RelatedID INT,
TableName VARCHAR(20), --表名称
Realname VARCHAR(20) --真实字段名称
)
INSERT INTO Dictionary
SELECT 1,'DedicatedMeas','RxLevFull' UNION ALL
SELECT 1,'DedicatedMeas','RxLevSub' UNION ALL
SELECT 1,'ServingCellInfo','T3212' UNION ALL
SELECT 1,'ServingCellInfo','BCCH' UNION ALL
SELECT 1,'GPRSMeasCommon','RLCThrDL' UNION ALL
SELECT 1,'GPRSMeasCommon','RLCThrUL' declare @sql varchar(1000),@tables varchar(1000),@fields varchar(1000)
set @fields = ''
set @tables = ( select distinct '' + ' left join ' + TableName + ' on ' + TableName + '.relatedid=t.id' + CHAR(10)
from Dictionary for xml path('') )select @fields = @fields + ',' + TableName + '.' + Realname + CHAR(10) from Dictionaryset @sql = 'select tb.*' + @fields + @tables
print @sql