T-SQL,动态聚合查询
来源:互联网 发布:迅雷种子论坛会员淘宝 编辑:程序博客网 时间:2024/03/29 20:45
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'AccountMessage')
DROP TABLE AccountMessage
GO
CREATE TABLE AccountMessage(
FFundCode VARCHAR(6) NOT NULL,
FAccName VARCHAR(20) NOT NULL,
FAccNum INT NOT NULL);
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'AccountBalance')
DROP TABLE AccountBalance
GO
CREATE TABLE AccountBalance(
FFundCode VARCHAR(6) NOT NULL,
FAccNum INT NOT NULL,
FDate DATETIME DEFAULT (getdate()) NOT NULL,
FBal NUMERIC(10,2) NOT NULL);
INSERT INTO AccountMessage VALUES('000001','北京存款',1)
INSERT INTO AccountMessage VALUES('000001','上海存款',2)
INSERT INTO AccountMessage VALUES('000001','深圳存款',3)
INSERT INTO AccountMessage VALUES('000002','北京存款',1)
INSERT INTO AccountMessage VALUES('000002','上海存款',2)
INSERT INTO AccountMessage VALUES('000002','天津存款',3)
INSERT INTO AccountMessage VALUES('000003','上海存款',1)
INSERT INTO AccountMessage VALUES('000003','福州存款',2)
INSERT INTO AccountBalance(FDate, FFundCode, FAccNum, FBal) VALUES ('2004-07-28','000001',1,1000.00)
INSERT INTO AccountBalance(FDate, FFundCode, FAccNum, FBal) VALUES ('2004-07-28','000001',2,1000.00)
INSERT INTO AccountBalance(FDate, FFundCode, FAccNum, FBal) VALUES ('2004-07-28','000001',3,1120.00)
INSERT INTO AccountBalance(FDate, FFundCode, FAccNum, FBal) VALUES ('2004-07-28','000002',1,2000.00)
INSERT INTO AccountBalance(FDate, FFundCode, FAccNum, FBal) VALUES ('2004-07-28','000002',2,1000.00)
INSERT INTO AccountBalance(FDate, FFundCode, FAccNum, FBal) VALUES ('2004-07-28','000002',3,1000.00)
INSERT INTO AccountBalance(FDate, FFundCode, FAccNum, FBal) VALUES ('2004-07-28','000003',1,2000.00)
INSERT INTO AccountBalance(FDate, FFundCode, FAccNum, FBal) VALUES ('2004-07-28','000003',2,1000.00)
go
两种不同的方法
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+quotename(FAccName)
+'=isnull(sum(case a.FAccName when '+quotename(FAccName,'''')
+' then b.FBal end),0)'
from AccountMessage group by FAccName
exec('
select 基金代码=a.FFundCode'+@s+'
from AccountMessage a,AccountBalance b
where a.FFundCode=b.FFundCode and a.FAccNum=b.FAccNum
group by a.FFundCode')
go
select * into #t from(select a.*,b.fbal from AccountMessage a join AccountBalance b on a.ffundcode=b.ffundcode and a.faccnum=b.faccnum)t
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT ffundcode'
SELECT @SQL= @SQL+ ',sum(CASE WHEN FAccName = '''
+ tt + ''' THEN FBal else 0 END) [' +tt+ ']'
FROM (SELECT DISTINCT FAccName as tt FROM #t) A
SET @SQL=@SQL+' FROM #t group by ffundcode'
exec (@SQL)
- T-SQL,动态聚合查询
- T-SQL,动态聚合查询
- T-SQL 查询优化之聚合
- T-SQL聚合函数
- T-SQL动态查询(4)——动态SQL
- SQL聚合函数查询
- mongo 聚合查询sql
- T-SQL动态查询(2)——关键字查询
- T-SQL动态查询(3)——静态SQL
- T-SQL动态查询(1)——简介
- 聚合函数查询的SQL
- 【0042】SQL查询--分组聚合
- SQL-使用聚合函数查询
- T-SQL中内部函数-聚合函数
- T-Sql表查询
- T-SQL SELECT 查询
- T-SQL语句查询
- T-SQL查询基础
- 缘分这东西
- Google将推出视频Blog服务
- 项目计划技巧 (转自IBM)
- 在 IIS 服务器上设置 SSL
- Item01: 数据提取(Data Abstraction)
- T-SQL,动态聚合查询
- SQLServer和Oracle的常用函数对比
- [转贴][人生]工作两年后感悟的《大话西游》
- 长大
- 需求工程???
- jbuilder 使用技巧
- SQL Server Analysis Services 使用实践
- Delphi代码优化(字符处理)
- eclipse常用插件