- 浏览: 866186 次
- 性别:
- 来自: 上海
文章分类
最新评论
-
zzuliuli:
很实用,一直关注
mysql的执行计划 -
rxin2009:
你好,最近在解决redis数据同步的问题,找到了tedis,但 ...
taobao/tedis的redis集群 -
zhangping2056:
楼主接下来要考虑页面静态化与细节上面的东西了
Nginx与Redis解决高并发问题 -
XieFuQ:
Tomcat的重启shell脚本 -
jovinlee:
jovinlee 写道 jov ...
Tomcat的重启shell脚本
行转列问题总结
1、行转列
---1、最简单的行转列
/*
问题:假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果):
姓名 语文 数学 物理
李四 74 84 94
张三 74 83 93
*/
--测试用
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go
--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
--通过动态构建@sql,得到如下脚本
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
--得到SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b
--查询结果
/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
(所影响的行数为 2 行)
*/
--2 加合计
/*
问题:在上述结果的基础上加平均分,总分,得到如下结果:
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名
--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')
其他实例
http://topic.csdn.net/u/20100708/18/55df5a90-27a7-4452-a69a-27f735539a1f.html?seed=24842417&r=66831902#r_66831902
--3、不同数据按照序号转为列,方法基本同 1
if object_id('tb1') is not null drop table tb1
go
CREATE table tb1 --数据表
(
cpici varchar(10) not null,
cname varchar(10) not null,
cvalue int null
)
--插入测试数据
INSERT INTO tb1 values('T501','x1',31)
INSERT INTO tb1 values('T501','x1',33)
INSERT INTO tb1 values('T501','x1',5)
INSERT INTO tb1 values('T502','x1',3)
INSERT INTO tb1 values('T502','x1',22)
INSERT INTO tb1 values('T502','x1',3)
INSERT INTO tb1 values('T503','x1',53)
INSERT INTO tb1 values('T503','x1',44)
INSERT INTO tb1 values('T503','x1',50)
INSERT INTO tb1 values('T503','x1',23)
--在sqlserver2000里需要用自增辅助
alter table tb1 add id int identity
go
declare @s varchar(8000)
set @s='select cpici '
select @s=@s+',max(case when rn='+ltrim(rn)+' then cvalue end) as cvlue'+ltrim(rn)
from (select distinct rn from (select rn=(select count(1) from tb1 where cpici=t.cpici and id<=t.id) from tb1 t)a)t
set @s=@s+' from (select rn=(select count(1) from tb1 where cpici=t.cpici and id<=t.id),* from tb1 t
) t group by cpici'
exec(@s)
go
alter table tb1 drop column id
--再2005就可以用row_number
declare @s varchar(8000)
set @s='select cpici '
select @s=@s+',max(case when rn='+ltrim(rn)+' then cvalue end) as cvlue'+ltrim(rn)
from (select distinct rn from (select rn=row_number()over(partition by cpici order by getdate()) from tb1)a)t
set @s=@s+' from (select rn=row_number()over(partition by cpici order by getdate()),* from tb1
) t group by cpici'
exec(@s)
---结果
/*
cpici cvlue1 cvlue2 cvlue3 cvlue4
---------- ----------- ----------- ----------- -----------
T501 31 33 5 NULL
T502 3 22 3 NULL
T503 53 44 50 23
警告: 聚合或其他 SET 操作消除了空值。
(3 行受影响)
*/
--测试用
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
create table tb(电话号码 varchar(15), 通话时长 int ,行业 varchar(10))
insert tb
select '13883633601', 10 ,'餐饮' union all
select '18689704236', 20 ,'物流' union all
select '13883633601', 20 ,'物流' union all
select '13883633601', 20 ,'汽车' union all
select '18689704236', 20 ,'医疗' union all
select '18689704236', 20 ,'it' union all
select '18689704236', 20 ,'汽车' union all
select '13883633601', 50 ,'餐饮'
go
declare @sql varchar(8000)
set @sql='select 电话号码,sum(通话时长) 通话总和'
select @sql=@sql+',max(case when rowid='+ltrim(rowid)+' then 行业 else '''' end) as [行业'+ltrim(rowid)+']'
from (select distinct rowid from (select (select count(distinct 行业) from tb where 电话号码=t.电话号码 and 行业<=t.行业) rowid
from tb t) a) b
set @sql=@sql+' from ( select * , (select count(distinct 行业) from tb where 电话号码=t.电话号码 and 行业<=t.行业) rowid
from tb t ) t group by 电话号码'
exec(@sql)
--结果
/*
(所影响的行数为 8 行)
电话号码 通话总和 行业1 行业2 行业3 行业4
--------------- ----------- ---------- ---------- ---------- ----------
13883633601 100 餐饮 汽车 物流
18689704236 80 it 汽车 物流 医疗
(所影响的行数为 2 行)
*/
另一种动态行转列:
http://topic.csdn.net/u/20100612/10/4CFCB667-89FA-4985-90D5-B8A420A6FF12.html
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([姓名] varchar(1),[部门] varchar(4),[学历] varchar(4),[出生年月] datetime)
insert [tb]
select 'A','后勤','高中','1986-1-1' union all
select 'B','后勤','初中','1984-3-7' union all
select 'C','管理','本科','1987-2-1' union all
select 'D','操作','专科','1976-2-1' union all
select 'E','操作','专科','1943-2-1'
go
GO
if object_id('GetGroupByCol') is not null drop proc GetGroupByCol
go
create PROCEDURE [dbo].[GetGroupByCol]
@colm nvarchar(100)
AS
declare @sql varchar(4000)
set @sql='
declare @sql varchar(8000)
set @sql=''select 部门''
select @sql =@sql+ '', sum(case ltrim('+@colm+') when ''''''+ltrim(' + @colm + ')+'''''' then 1 else 0 end)
[''+ltrim(' + @colm + ')+'']'' from (select distinct '+@colm+' from tb where '+@colm+' is not null) as a
set @sql = @sql + '' from tb group by 部门''
exec(@sql)'
exec(@sql)
GO
exec GetGroupByCol N'学历'
exec GetGroupByCol N'出生年月'
exec GetGroupByCol N'姓名'
/*
(所影响的行数为 5 行)
部门 本科 初中 高中 专科
---- ----------- ----------- ----------- -----------
操作 0 0 0 2
管理 1 0 0 0
后勤 0 1 1 0
(所影响的行数为 3 行)
部门 02 1 1943 12:00AM 02 1 1976 12:00AM 03 7 1984 12:00AM 01 1 1986 12:00AM 02 1 1987 12:00AM
---- ------------------ ------------------ ------------------ ------------------ ------------------
操作 1 1 0 0 0
管理 0 0 0 0 1
后勤 0 0 1 1 0
(所影响的行数为 3 行)
部门 A B C D E
---- ----------- ----------- ----------- ----------- -----------
操作 0 0 0 1 1
管理 0 0 1 0 0
后勤 1 1 0 0 0
(所影响的行数为 3 行)
*/
以下可参考的例子
1、普通多表联合
http://topic.csdn.net/u/20100623/00/077055eb-784d-4b27-8407-2c17adc06c60.html?seed=81934135&r=66426155#r_66426155
http://topic.csdn.net/u/20100622/19/9710803c-441b-45d0-b010-703a2633fe89.html?47161
2、多表根据时间 计算序号
http://topic.csdn.net/u/20100623/12/bbb0921b-0e1b-4435-8e85-959d87844954.html?seed=2145286087&r=66438763#r_66438763
http://topic.csdn.net/u/20100701/09/1684649b-b893-463b-8b40-7f4b894cd41e.html?seed=205688256&r=66630774#r_66630774
3、财务相关
http://topic.csdn.net/u/20100626/00/83499112-43ae-4caa-a1fd-268cc5138da6.html?seed=415671352&r=66513615#r_66513615
4、根据行数转列
http://topic.csdn.net/u/20100705/12/e325571b-c368-4174-859f-17ae708eca3d.html
http://topic.csdn.net/u/20100706/09/c34728dc-6167-45df-b7cf-974612b9aa8b.html
http://topic.csdn.net/u/20100706/16/f217deed-a2be-4950-b911-2624ac7a881a.html?39445
5、根据排序大小转
http://topic.csdn.net/u/20100707/13/63f4a02e-ebc3-4c71-9380-d6b2ca0eb366.html?39970
6、分组排序按序号转
http://topic.csdn.net/u/20100725/05/7f813114-c423-4759-97b8-b22e1e2e90d7.html?seed=471594449&r=67220945#r_67220945
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/xys_777/archive/2010/06/22/5685953.aspx
发表评论
-
mysql-覆盖索引
2019-09-19 09:46 0思维导图-索引篇 系列思维导图源文件(数据库+架构) ... -
MySQL高性能优化规范建议
2019-09-19 09:26 0作者: 听风,原文地址: https://www.cnb ... -
事务的四种隔离级别
2019-09-19 08:50 0数据库事务的隔离级别有4种,由低到高分别为Read unco ... -
TCC 分布式事务
2019-09-11 09:30 0https://www.cnblogs.com/jajia ... -
终于明白为什么要“分库分表”了!
2019-09-11 09:28 0例如:单表中出现了,动辄百万甚至千万级别的数据。“分表分库” ... -
数据同步利器-otter的搭建使用说明
2019-09-11 09:26 0本文链接:https://blog.cs ... -
springboot+mycat数据库中间件+mysql(一主一备)主从复制
2019-09-11 09:05 0springboot+mycat数据库中 ... -
分布式数据库中间件Mycat百亿级数据存储(转)
2019-09-11 09:04 0此文转自: https://www.jiansh ... -
Mysql+Mycat实现数据库主从同步与读写分离
2019-09-11 09:03 0此文章源于 对文章中的架构扩展介绍,之前写过一篇关于M ... -
mysql主从复制
2016-09-26 10:31 576mysql主从复制(超简单) 怎么安装mysql数据库 ... -
mysql记录耗时的sql
2014-08-21 10:11 1196author:skatetime:2012/02/17mys ... -
mysql-锁表机制分析
2014-08-12 15:20 525为了给高并发情况下的 ... -
MyISAM InnoDB 区别
2014-08-12 15:18 489MyISAM 和 InnoDB 讲解 InnoDB和 ... -
MySQL行锁深入研究
2014-08-12 15:10 616做项目时由于业务逻辑的需要,必须对数据表的一行或多行加入行锁 ... -
MySQL批量数据导入Load data infile解决方案
2013-12-30 15:52 1941转自:http://www.veryphp.cn/datas ... -
mysql load
2013-12-16 18:36 0http://bugs.mysql.com/bug.php? ... -
MySQL监控所用到的常用命令
2013-12-03 16:13 520转自:http://www.easywu.com/?p=35 ... -
Mysql的存储引擎在InnoDB和MyISAM时的锁
2013-11-11 09:23 840mysql查询更新时的锁表机制分析 为了给高并发情 ... -
mysql LOCK
2013-11-10 16:07 808MySQL官方文档 http://dev.mysql.com/ ... -
MySQL innoDB insert into性能优化
2013-11-10 15:58 2158起因:有一个innoDB引擎的表Table,在一个大概3000 ...
相关推荐
SQL 行转列+动态获取列名 通用的动态获取列名
sql行转列_列转行问题.
SQL行转列学习谢谢!
SQL行转列参考代码(sqlserver)
sql 行转列 与列转行,oracle ,msssql等,详细数据库操作方法,各种例子,欢迎大家学习。、~
行转列sql实例行转列sql实例行转列sql实例行转列sql实例行转列sql实例
sql行转列动态与静态三种方法
SQL行转列问题,有实例,根据具体实例讲解,清晰易懂。 希望对你有帮助!
sql行转列解决方案.
有case when方式和2005之后的内置pivot和unpivot方法来实现,行列互转,可以分为静态互转,动态互转。
SQL行转列列转行.txt
利用SQL语句将表行转列显示,应用的是PIVOT ,很方便
sqlserver 实现 行转列 split 分割的函数,具体使用方法写有在文件里。
DB2 SQL 通过函数(CONCAT/POSSTR/LOCATE)实现行转列,列转行 可以按照标点把多列转换为一行,多行转换为一列
详细解释 行转列sql语句,作为范例查看sql
Sql语句实现表的行列转换,行转列,列转行
很好的行转列SQL写法,对SQL的拼装和拆分有很好的描述,可以帮助解析SQL的重组。
讲述,sql中如何实现行转列的实例,包含知道固定行数的静态转换,与未知行数的动态转换。
Sql Server里面的行转列,列转行
sql列转行以及行转列的通用存储过程!mssqlserver版本!