我想做个动态列查询,T_MappingAccount表中的FFieldID对应T_MappingFieldType表FID,
根据T_MappingFieldType表对应的列名查出T_MappingAccountData对应的列值,这样的SQL怎么写出来啊?-----测试数据-----
IF EXISTS(SELECT 1 FROM SYSOBJECTS
WHERE ID = OBJECT_ID(N'T_MappingFieldType')
AND OBJECTPROPERTY(ID,N'ISTABLE') = 1)
DROP TABLE T_MappingFieldType
GOCREATE TABLE T_MappingFieldType
(
FID INT,
FName NVARCHAR(20),
FField NVARCHAR(15)
)
GO
INSERT INTO T_MappingFieldType
SELECT 1,'借方累计发生额','FYtdDebit'
UNION ALL
SELECT 2,'贷方累计发生额','FYtdCredit'
UNION ALL
SELECT 3,'借方发生额','FDebit'
UNION ALL
SELECT 4,'贷方发生额','FCredit'
UNION ALL
SELECT 5,'本期余额','FEndBalance'
IF EXISTS(SELECT 1 FROM SYSOBJECTS
WHERE ID = OBJECT_ID(N'T_MappingAccount')
AND OBJECTPROPERTY(ID,N'ISTABLE') = 1)
DROP TABLE T_MappingAccount
GOCREATE TABLE T_MappingAccount
(
FInterID INT IDENTITY(1,1) PRIMARY KEY,
FAccountID INT,
FFieldID INT
)
GOINSERT INTO T_MappingAccount
SELECT 1000,2
UNION ALL
SELECT 1001,3
UNION ALL
SELECT 1004,1
UNION ALL
SELECT 1005,1
UNION ALL
SELECT 1006,4
UNION ALL
SELECT 1009,5
UNION ALL
SELECT 1011,2
UNION ALL
SELECT 1014,3
UNION ALL
SELECT 1015,1
UNION ALL
SELECT 1017,2
IF OBJECT_ID(N'T_MappingAccountData') IS NOT NULL
DROP TABLE T_MappingAccountData
GO
CREATE TABLE T_MappingAccountData
(
FAccountID INT,
FDebit DECIMAL(13,2),
FCredit DECIMAL(13,2),
FYtdDebit DECIMAL(13,2),
FYtdCredit DECIMAL(13,2),
FEndBalance DECIMAL(13,2)
)
GOINSERT INTO T_MappingAccountData
SELECT 1000,327359.00,328989.82,327359.00,328989.82,40250.63
UNION ALL
SELECT 1001,7509115.10,6572844.45,7509115.10,6572844.45,9366373.50
UNION ALL
SELECT 1004,0.00,0.00,0.00,0.00,1000000.00
UNION ALL
SELECT 1005,6919858.13,7508525.00,6919858.13,7508525.00,43790107.43
UNION ALL
SELECT 1006,0.00,0.00,0.00,0.00,-860000.00
UNION ALL
SELECT 1009,201861.64,285499.59,201861.64,285499.59,1289985.48
UNION ALL
SELECT 1011,5659809.04,4207507.22,5659809.04,4207507.22,9010930.77
UNION ALL
SELECT 1014,4629674.33,4182286.37,4629674.33,4182286.37,836511.24
UNION ALL
SELECT 1015,312136.43,85802.36,312136.43,85802.36,336651.40
UNION ALL
SELECT 1017,3999585.68,4175146.27,3999585.68,4175146.27,480897.94SELECT * FROM T_MappingFieldType
SELECT * FROM T_MappingAccount
SELECT * FROM T_MappingAccountData----测试结果-----
FAccountID FAccountValue
1000 328989.82
1001 7509115.10
1004 0.00
1005 6919858.13
1006 0.00
1009 285499.59
1011 4207507.22
1014 4629674.33
1015 312136.43
1017 4175146.27(10 row(s) affected)
http://topic.csdn.net/u/20110719/16/24a7629a-bb6a-482a-a172-c1b5942ebd8d.html