时间戳

首页 > 解决方案 > 根据配置表从数据表中查找对应的值

sql - 根据配置表从数据表中查找对应的值

问题描述

我有两张桌子PaymentData和PaymentDataConfig. 有一个共同的专栏id。表PaymentDataConfig有一个configColumn包含表中列的名称PaymentData。

if exists (select 1 from sys.objects o where o.[object_id] = object_id(N'PaymentData'))
begin
    drop table PaymentData 
end

create table PaymentData 
(
    id int,
    payment1 decimal(20,2),
    payment2 decimal(20,2),
    payment3 decimal(20,2),
    payment4 decimal(20,2),
    payment5 decimal(20,2)
)

insert into PaymentData (id, payment1, payment2, payment3, payment4, payment5)
select 1 as id, 1001.00 as payment1, 1002.00 as payment2, 1003.00 as payment3, 1004.00 as payment4, 1005.00 as payment5 union all
select 2 as id, 2001.00 as payment1, 2002.00 as payment2, 2003.00 as payment3, 2004.00 as payment4, 2005.00 as payment5 union all
select 3 as id, 3001.00 as payment1, 3002.00 as payment2, 3003.00 as payment3, 3004.00 as payment4, 3005.00 as payment5 union all
select 4 as id, 4001.00 as payment1, 4002.00 as payment2, 4003.00 as payment3, 4004.00 as payment4, 4005.00 as payment5 union all
select 5 as id, 5001.00 as payment1, 5002.00 as payment2, 5003.00 as payment3, 5004.00 as payment4, 5005.00 as payment5

select * from PaymentData

if exists (select 1 from sys.objects o where o.[object_id] = object_id(N'PaymentDataConfig'))
begin
    drop table PaymentDataConfig 
end

create table PaymentDataConfig 
(
    id int,
    configColumn varchar(1000)
)

insert into PaymentDataConfig (id, configColumn)
select 1 as id, 'payment1' as configColumn union all
select 2 as id, 'payment2' as configColumn union all
select 3 as id, 'payment3' as configColumn union all
select 4 as id, 'payment4' as configColumn union all
select 5 as id, 'payment5' as configColumn

select * from PaymentDataConfig

select p.*, c.* 
from PaymentData p 
join PaymentDataConfig c on p.id = c.id

如果我在 id 上加入两个表，我会得到如下结果

id  payment1| payment2|payment3 |payment4|  payment5|id |configColumn
------------+---------+---------+--------+----------+---+-------------
1   1001.00 | 1002.00 | 1003.00 | 1004.00|  1005.00 |1  |payment1
2   2001.00 | 2002.00 | 2003.00 | 2004.00|  2005.00 |2  |payment2
3   3001.00 | 3002.00 | 3003.00 | 3004.00|  3005.00 |3  |payment3
4   4001.00 | 4002.00 | 4003.00 | 4004.00|  4005.00 |4  |payment4
5   5001.00 | 5002.00 | 5003.00 | 5004.00|  5005.00 |5  |payment5

预期结果如下我想在列名中查看相应值的位置

id  payment1| payment2|payment3 |payment4|  payment5|id |configColumn|correspondingValue
============|=========|=========|========|==========|===|============|==================
1   1001.00 | 1002.00 | 1003.00 | 1004.00|  1005.00 |1  |payment1    |1001.00
2   2001.00 | 2002.00 | 2003.00 | 2004.00|  2005.00 |2  |payment2    |2002.00
3   3001.00 | 3002.00 | 3003.00 | 3004.00|  3005.00 |3  |payment3    |3003.00
4   4001.00 | 4002.00 | 4003.00 | 4004.00|  4005.00 |4  |payment4    |4004.00
5   5001.00 | 5002.00 | 5003.00 | 5004.00|  5005.00 |5  |payment5    |5005.00

标签： sqlsql-serversql-server-2012

解决方案

您可以使用 CASE 表达式来实现这一点：

select p.*, c.*, CASE c.configColumn WHEN N'payment1' THEN p.payment1 WHEN N'payment2' THEN p.payment2 WHEN N'payment3' THEN p.payment3 WHEN N'payment4' THEN p.payment4 WHEN N'payment5' THEN p.payment5 ELSE NULL END AS correspondingValue
from PaymentData p 
join PaymentDataConfig c 
on p.id = c.id

正如您的更新所提到的，如果您有很多配置的列，您可以使用动态 sql：

DECLARE @SQL nvarchar(max)=N''
DECLARE @ID int;
SELECT @ID=MIN(ID) FROM PaymentDataConfig;
WHILE @ID IS NOT NULL
BEGIN
  SET @SQL=@SQL+' WHEN N''payment''+CAST(@ID AS nvarchar(20)) +N' THEN p.payment'+ CAST(@ID AS nvarchar(20))';
  SELECT @ID=MIN(ID) FROM PaymentDataConfig WHERE ID>@ID;
END
SET @SQL=@SQL+N'
    select p.*, c.*, CASE c.configColumn '+ @SQL + N' ELSE NULL END AS correspondingValue
    from PaymentData p 
    join PaymentDataConfig c 
    on p.id = c.id'

exec sp_executesql @SQL;

推荐阅读