IF EXISTS(SELECT NAME FROM sys.objects AS o WHERE NAME = 'test') DROP TABLE test GOCREATE TABLE test(id VARCHAR(03) , [time] DATETIME , VALUE DECIMAL(10,3) , value2 DECIMAL(10,3))GO INSERT INTO test SELECT '001' , '2014-03-11 00:00:00.000' , 19.094 , 5.402 UNION ALL SELECT '001' , '2014-03-11 00:10:00.000' , 18.722 , 5.325 UNION ALL SELECT '002' , '2014-03-11 00:00:00.000' , 18.598 , 5.445 UNION ALL SELECT '002' , '2014-03-11 00:10:00.000' , 18.88 , 5.709 UNION ALL SELECT '003' , '2014-03-11 00:00:00.000' , 19.052 , 5.935 UNION ALL SELECT '003' , '2014-03-11 00:10:00.000' , 19.052 , 5.935GO ---执行查询------------------------------- DECLARE @sql NVARCHAR(MAX) SELECT @sql = ISNULL(@sql , '') + ' sum(case when id = ''' + id + ''' then value else 0 end) as [' +id+'value]' +' , sum(case when id = ''' + id + ''' then value2 else 0 end) as ['+id+'value2]'+ ',' FROM test GROUP BY IDSET @sql = 'SELECT time ,'+ left(@sql , LEN(@sql) - 1 ) + ' from test group by time' PRINT @sql EXEC(@sql)/* 查询结果 time 001value 001value2 002value 002value2 003value 003value2 ----------------------- ------------ ------------ ----------- ----------- ---------- ---------- 2014-03-11 00:00:00.000 19.094 5.402 18.598 5.445 19.052 5.935 2014-03-11 00:10:00.000 18.722 5.325 18.880 5.709 19.052 5.935(2 行受影响) */
DROP TABLE test
GOCREATE TABLE test(id VARCHAR(03) , [time] DATETIME , VALUE DECIMAL(10,3) , value2 DECIMAL(10,3))GO
INSERT INTO test
SELECT '001' , '2014-03-11 00:00:00.000' , 19.094 , 5.402 UNION ALL
SELECT '001' , '2014-03-11 00:10:00.000' , 18.722 , 5.325 UNION ALL
SELECT '002' , '2014-03-11 00:00:00.000' , 18.598 , 5.445 UNION ALL
SELECT '002' , '2014-03-11 00:10:00.000' , 18.88 , 5.709 UNION ALL
SELECT '003' , '2014-03-11 00:00:00.000' , 19.052 , 5.935 UNION ALL
SELECT '003' , '2014-03-11 00:10:00.000' , 19.052 , 5.935GO
---执行查询-------------------------------
DECLARE @sql NVARCHAR(MAX) SELECT @sql = ISNULL(@sql , '') + ' sum(case when id = ''' + id + ''' then value else 0 end) as [' +id+'value]'
+' , sum(case when id = ''' + id + ''' then value2 else 0 end) as ['+id+'value2]'+ ',' FROM test GROUP BY IDSET @sql = 'SELECT time ,'+ left(@sql , LEN(@sql) - 1 ) + ' from test group by time'
PRINT @sql
EXEC(@sql)/* 查询结果
time 001value 001value2 002value 002value2 003value 003value2
----------------------- ------------ ------------ ----------- ----------- ---------- ----------
2014-03-11 00:00:00.000 19.094 5.402 18.598 5.445 19.052 5.935
2014-03-11 00:10:00.000 18.722 5.325 18.880 5.709 19.052 5.935(2 行受影响) */
A表有MODULE_NAME(模块名称)和MODULE_ID列,其中MODULE_ID为空,MODULE_NAME列为一个这样格式可能是:”1、模块列表-产品实现-献血服务-献血核查。“,也可能是“1、产品实现。”
B表(模块名称表)有ID和NAME(模块名称)列,NAME列的数据为标准信息,如“产品实现”。
数据就是这样的:A表的模块名称列里的数据包含B表的NAME的数据。
现在想通过将A表关联B表实现将B表的ID更新到A表的MODULE_ID列。
请问如何实现,谢谢。