从不同的表中检索列名? [英] Retrieve column names from a different table?
问题描述
我有一个数据转储"表，其中包含一堆与性能相关的混合数据.类似的东西:
I have a "datadump" table that has a bunch of mixed performance-related data. Something like:
MachID TestDate MachType Value1 Value2 ...
00001 01/01/09 Server 15 48
00001 01/02/09 Server 16 99
19999 01/01/09 Switch 32 4.9880
19999 01/02/09 Switch 32 5.8109
诀窍在于，对于不同类型的机器，值"列MEAN 不同.所以我们有一个xRef"表，看起来像:
The trick is that the "values" columns MEAN different things for different types of machines. So we have a "xRef" table that looks like:
MachType Column Description
Server Value1 Users Connected
Server Value2 % CPU _total
Switch Value1 Number of Ports
Switch Value2 packets/ms
...
我知道，奇怪的结构，但我没有做到，也无法改变它.
I know, weird structure, but I didn't make it, and can't change it.
我想以某种方式内部连接"这些，以便我可以根据数据类型查询适当的列标题.服务器是这样的:
I'd like to somehow "inner join" these so I can query the appropriate column headers based on the type of data. Something like this for the servers:
MachID TestDate MachType Users Connected % CPU _total Total RAM
00001 01/01/09 Server 15 48 4096
00001 01/02/09 Server 16 99 4096
这对于开关:
MachID TestDate MachType Number of Ports packets/ms Total Cumulative kb
19999 01/01/09 Switch 32 4.9880 1024547
19999 01/02/09 Switch 32 5.8109 1029450
有没有办法在不为每种类型进行单独的硬编码查询的情况下做到这一点?
Is there a way to do this without doing individual hard-coded queries for each type?
注意:我一次只需要查询一种类型的对象.如果有帮助，我很可能只会查看单个 MachID 特定日期之间的所有结果.这是 MS SQL 2000.
Note: I will only need to query one type of object at a time. Most likely, I'll only be looking at all results between particular dates for a single MachID, if that helps. This is MS SQL 2000.
谢谢！
推荐答案
动态 sql 选项将是(写出作为查询而不是制作成一个过程):
A dynamic sql option would be (written out as a query rather than made into a proc):
declare @machtype varchar(40) --stored proc parameter?
set @machtype = 'Switch' --or 'Server'
declare @sql nvarchar(4000)
set @sql = 'select
MachID,
TestDate,
MachType,
Value1 as ''' + (select [Description] from dbo.xref where machtype = @machtype and [Column] = 'Value1') + ''',
Value2 as ''' + (select [Description] from dbo.xref where machtype = @machtype and [Column] = 'Value2') + ''',
Value3 as ''' + (select [Description] from dbo.xref where machtype = @machtype and [Column] = 'Value3') + '''
from
dbo.datadump
where
machtype = ''' + @machtype + ''''
exec sp_executesql @sql
如果你觉得这对你来说太难看了，那么将获取列名的逻辑包装在一个函数中会整理一下:
If you find that simply too ugly for you then wrapping the logic for getting the column name in a function would tidy it up:
create function dbo.ColNameForDataDump(
@machtype varchar(40),
@column varchar(40)
)
RETURNS varchar(40)
as
begin
declare @col_desc varchar(40)
select
@col_desc = [description]
from
dbo.xref
where
machtype = @machtype
and [column] = @column
return @col_desc
end
那么您的动态 SQL 将看起来更像:
Then your dynamic SQL will look more like:
declare @machtype varchar(40) --stored proc parameter?
set @machtype = 'Switch' --or 'Server'
declare @sql nvarchar(4000)
set @sql = 'select
MachID,
TestDate,
MachType,
Value1 as ''' + dbo.ColNameForDataDump(@machtype, 'Value1') + ''',
Value2 as ''' + dbo.ColNameForDataDump(@machtype, 'Value2') + ''',
Value3 as ''' + dbo.ColNameForDataDump(@machtype, 'Value3') + '''
from
dbo.datadump
where
machtype = ''' + @machtype + ''''
exec sp_executesql @sql
最后是对上面代码的传递点/评论:您提到您在 SQL Server 2000 上，因此请确保何时必须编写一些动态 sql 将其定义为 nvarchar 并使用 sp_executesql 调用它...从而消除了必须动态化的一些性能痛苦.
Finally a passing point / comment on the code above: you mentioned that you are on SQL Server 2000 so make sure when you do have to write some dynamic sql to define it as an nvarchar and use sp_executesql to call it...thereby negating some of the performance pain of having to go dynamic.
这篇关于从不同的表中检索列名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
