同组合并成列问题
同学问了我将数据库的表同组合并成列的问题,我第一反映是建一个二维数组,两个HASH表分别对应行和列,逐行读取累加,但后来说是要在SQL里直接完成返回结果集,SQL是我最弱的,于是趁此问题也好好地学习了一下,使用方法较水,希望可以得到指点。
一.问题描述
问题描述可以简单总结为:现有一个初始表(名称、分组、值),求根据“名称成行、分组成列、值累加”的名称、分组不重复结果集。
原表名为:JqTestTable 表内容如下
ID | Name | GroupName | Value |
1 | 123 | A | 1 |
2 | 123 | B | 2 |
3 | 123 | A | 3 |
4 | 123 | B | 4 |
5 | 456 | A | 5 |
6 | 456 | B | 6 |
7 | 456 | A | 7 |
8 | 456 | B | 8 |
现要求得到的结果集为:
Name | A | B |
123 | 4 | 6 |
456 | 12 | 14 |
二.解题思路
那么算法可以总结为这么几个步骤
1、得到所有分组(不重复)
2、根据分组创建临时表
3、把原始数据经算法插入到临时表
a、先将初始表根据同名、同组、合并累加
b、遍历得到的结果、按需求插入到对应的临时表中
4、查询临时表得到结果集
5、删除临时表
三.解题过程
1. 得到所有分组(不重复)
得到所有分组, 就是获取初始表的所以有不重复的分组字段的记录,如下:
A int default0, B int default 0 ……
int为整型、default 0为初始值
去复得分组的语句为:select distinctGroupName from JqTestTable
根据得到的结果,循环遍历得到各个分组,循环内语句如下:
select distincttop (@i + 1) GroupName from JqTestTable where GroupName not in
(select distinct top (@i) GroupNamefrom JqTestTable)
建议最好用游标完成遍历过程
这样得到的就是每个列的值,再组织一下,成:Aint default 0, B int default 0 ……
(注:这里由于组织出来的列名的生成出来的,所以要用到动态SQL语句)
2. 根据分组创建临时表
根据所有分组可以生成新建表的语句
create tableJqTempTable (Name varchar(20), A intdefault 0, B int default 0)
得到空表如下:
Name | A | B |
NULL | NULL | NULL |
3. 把原始数据经算法插入到临时表
A. 先将初始表根据同名、同组、合并累加
算法语句为:
select name, groupname,sum(value) as 'Sum'
from JqTestTable group by groupname, name
得到的结果为:
Name | GroupName | Value |
123 | A | 4 |
123 | B | 6 |
456 | A | 12 |
456 | B | 14 |
这样去重之后只需要重新组织一下表的结构就可以了
B. 遍历得到的结果、按需求插入到对应的临时表中
这里应该是最核心的操作步骤了,方法为:
比如 我们遍历上表得到的第一个值是123,由于空的临时表没有123这个NAME的记录,所以我们要先插入一个记录,算法如下:
-- 如果不存在则插入,如果存在则修改这列
if @strName notin (select name from JqTempTable)
begin
INSERT INTOJqTempTable(Name) VALUES (@strName)
end
以上的操作完成之后肯定是有123的记录了,那么我们得到的第二个值是A, 第三个值是1,那么我们只要在NAME为123的这个记录里插入(或累加)1到列 = A的项中
语句表达为:
updateJqTempTable set A = A + 4 where Name = 123
接下来的语句分别为:
updateJqTempTable set B = B + 6 where Name = 123
updateJqTempTable set A = A + 12 where Name = 456
updateJqTempTable set B = B + 14 where Name = 456
当然,这些语句不是自己打上去的,是用动态SQL去生成的
生成的原代码的语句为:
set @strSql ='update JqTempTable set ' + @strGroupName + ' = ' + @strGroupName + ' + ' +@strValue + ' where Name = ' + @strName;
exec(@strSql)
然后我们就得到临时表了:
Name | A | B |
123 | 4 | 6 |
456 | 12 | 14 |
4. 查询临时表得到结果集
这个临时表也就是需要的结果,我们只要把它查询出来就可以了:
select * fromJqTempTable
Name | A | B |
123 | 4 | 6 |
456 | 12 | 14 |
5. 把原始数据经算法插入到临时表
得到结果之后,最后别忘了删除临时表:
drop table JqTempTable
四.具体代码
-- 得到列名集
declare @strSql varchar(Max)
declare @strColumnAdd varchar(Max)
declare @i int
declare @nCount int
set @i = 0
set @nCount = (select count(distinctGroupName) from JqTestTable)
set @strColumnAdd = '
while @i < @nCount
begin
set@strColumnAdd = @strColumnAdd + ' ' +
(
selectdistinct top (@i + 1) GroupName from JqTestTable where GroupName not in
(selectdistinct top (@i) GroupName from JqTestTable)
)+ ' int default 0'
set@i = @i + 1
if(@i < @nCount)
set@strColumnAdd = @strColumnAdd + ', '
-- print@strColumnAdd
end
-- 新建一个临时表
set @strSql = 'create table JqTempTable(Name varchar(20), ' + @strColumnAdd + ')'
--print @strSql
if object_id('JqTempTable') is not null -- 判断表是否存在
droptable JqTempTable
exec(@strSql)
-- 处理数据
declare @strName varchar(20)
declare @strGroupName varchar(20)
declare @strValue varchar(20)
declare @strTest varchar(100)
-- 判断游标是否存在
if (cursor_status('global','cursorJqTest')<> -3) and (cursor_status('local','cursorJqTest') <> -3)
begin
closecursorJqTest
deallocatecursorJqTest
end
declare cursorJqTest cursor for
(select name, groupname, sum(value) as 'Sum' from JqTestTable group bygroupname, name)
open cursorJqTest
fetch next from cursorJqTest into @strName,@strGroupName, @strValue
while(@@fetch_status = 0)
begin
set@strTest = @strName + ' ' + @strGroupName + ' ' + @strValue
-- print@strTest
--如果不存在则插入,如果存在则修改这列
if@strName not in (select name from JqTempTable)
begin
INSERTINTO JqTempTable(Name) VALUES (@strName)
end
--累加列值
set@strSql = 'update JqTempTable set ' + @strGroupName + ' = ' + @strGroupName + '+ ' + @strValue
+' where Name = ' + @strName;
-- print@strSql
exec(@strSql)
fetchnext from cursorJqTest into @strName, @strGroupName, @strValue
end
close cursorJqTest
deallocate cursorJqTest
select * from JqTempTable
drop table JqTempTable
五.附录
1. 判断表是否存在
if object_id('JqTempTable') is not null
droptable JqTempTable
2. 判断游标是否存在
if (cursor_status('global','cursorJqTest')<> -3) and (cursor_status('local','cursorJqTest') <> -3)
begin
closecursorJqTest
deallocatecursorJqTest
end
3. 游标使用:
-- 列表中声明的变量数目必须与所选列的数目相同(重要)
declare @a varchar(20)
declare @b varchar(20)
declare @c varchar(20)
declare @d varchar(20)
declare @e varchar(100)
declare cursorJqTest cursor for select *from JqTestTable
open cursorJqTest
fetch next from cursorJqTest into @a, @b,@c, @d
while(@@fetch_status=0)--返回被 FETCH 语句执行的最后游标的状态,而不是任何当前被连接打开的游标的状态。
begin
set@e = @a + ' ' + @b + ' ' + @c + ' ' + @d
print@e
fetchnext from cursorJqTest into @a, @b, @c, @d
end
close cursorJqTest--关闭游标
deallocate cursorJqTest--删除游标