3.3w+
社区成员
- 全部
- 基础类
- 应用实例
- 新技术前沿
小菜提问一个觉得比较复杂的sql 倾囊。。。
一个表是这样的
其中职业是有限的个数是1-9的任意数 不然这个转化后的表的列数就不一定了 但是城市可以认为是无限多种的 也就是转化后的行可以无限多
这个问题很重要 请高手解答 俺倾囊了。。。
1 战士 杭州
2 法师 平顶山
3 牧师 南京
4 盗贼 北京
5 术士 杭州
6 萨满 杭州
7 德鲁伊 上海
8 圣骑士 北京
9 猎人 悉尼
转为下面的表
1 战士 5 术士 6 萨满 杭州
4 盗贼 8 圣骑士 北京
3 牧师 南京
9 猎人 悉尼
7 德鲁伊 上海
2 法师 平顶山
其中职业是有限的个数是1-9的任意数 不然这个转化后的表的列数就不一定了 但是城市可以认为是无限多种的 也就是转化后的行可以无限多
这个问题很重要 请高手解答 俺倾囊了。。。
1 战士 杭州
2 法师 平顶山
3 牧师 南京
4 盗贼 北京
5 术士 杭州
6 萨满 杭州
7 德鲁伊 上海
8 圣骑士 北京
9 猎人 悉尼
转为下面的表
1 战士 5 术士 6 萨满 杭州
4 盗贼 8 圣骑士 北京
3 牧师 南京
9 猎人 悉尼
7 德鲁伊 上海
2 法师 平顶山
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
一品梅 2008-03-21
呵呵，又见xx端倪。
wzy_love_sly 2008-03-21
梁哥太有名了 哈哈
irye 2008-03-21
wzy_love_sly 咋了
多谢liangCK 。。
多谢liangCK 。。
liangCK 2008-03-21
[Quote=引用 11 楼 wzy_love_sly 的回复:]
咋回事？日
[/Quote]
不知道.
咋回事？日
[/Quote]
不知道.
liangCK 2008-03-21
create table tb(id int,type varchar(50),address varchar(50))
insert tb select 1 ,'战士' , '杭州'
insert tb select 2 ,'法师' ,'平顶山'
insert tb select 3 ,'牧师' , '南京'
insert tb select 4 ,'盗贼' , '北京'
insert tb select 5 ,'术士' , '杭州'
insert tb select 6 ,'萨满' , '杭州'
insert tb select 7 ,'德鲁伊' , '上海'
insert tb select 8 ,'圣骑士' , '北京'
insert tb select 9 ,'猎人' , '悉尼'
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when px='+rtrim(px)+' then type else '''' end) [f'+rtrim(px)+']'
from (select distinct px=(select count(*) from tb where address=a.address and id<a.id)+1 from tb a) t
set @sql='select '+stuff(@sql,1,1,'')+',address from (select *,px=(select count(*) from tb where address=a.address and id<a.id)+1 from tb a) t group by address order by address'
exec ( @sql )
drop table tb
/*
f1 f2 f3 address
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
盗贼 圣骑士 北京
战士 术士 萨满 杭州
牧师 南京
法师 平顶山
德鲁伊 上海
猎人 悉尼
(6 行受影响)
*/wzy_love_sly 2008-03-21
咋回事？日
irye 2008-03-21
呵呵 意思就是这个意思 只要逻辑对了就行 行数没限制的
请问 liangCK老大 您的输出是测试结果吗 我手头没有测试环境 不是的话我找个环境试试 多谢您了
请问 liangCK老大 您的输出是测试结果吗 我手头没有测试环境 不是的话我找个环境试试 多谢您了
irye 2008-03-21
在原表中是数字是唯一索引 但转换后就只是标识了
如原表也可能是
1 战士 杭州
2 法师 平顶山
3 牧师 南京
4 盗贼 北京
5 术士 杭州
6 萨满 杭州
7 德鲁伊 上海
8 圣骑士 北京
9 猎人 悉尼
10 萨满 杭州
11 德鲁伊 上海
12 圣骑士 北京
13 猎人 悉尼
14 德鲁伊 北京
15 术士 杭州
16 德鲁伊 杭州
17 猎人 上海
18 猎人 北京
19 德鲁伊 悉尼
20 萨满 杭州
21 德鲁伊 上海
如原表也可能是
1 战士 杭州
2 法师 平顶山
3 牧师 南京
4 盗贼 北京
5 术士 杭州
6 萨满 杭州
7 德鲁伊 上海
8 圣骑士 北京
9 猎人 悉尼
10 萨满 杭州
11 德鲁伊 上海
12 圣骑士 北京
13 猎人 悉尼
14 德鲁伊 北京
15 术士 杭州
16 德鲁伊 杭州
17 猎人 上海
18 猎人 北京
19 德鲁伊 悉尼
20 萨满 杭州
21 德鲁伊 上海
wzy_love_sly 2008-03-21
4个杭州啊？你结果怎么三个
wzy_love_sly 2008-03-21
create table tb(name varchar(10),ads varchar(10))
insert into tb select '战士','杭州'
insert into tb select '法师','平顶山'
insert into tb select '牧师','北京'
insert into tb select '盗贼','杭州'
insert into tb select '术士','杭州'
insert into tb select '萨满','杭州'
insert into tb select '德鲁伊','上海'
insert into tb select '圣骑士','北京'
insert into tb select '猎人','悉尼'
select *,px=(select count(1) from tb where ads=t.ads and name<t.name)+1 into # from tb t
select * from # order by ads,px
declare @sql varchar(8000)
set @sql = 'select ads as ' + '地名'
select @sql = @sql + ' , max(case px when ''' + ltrim(px) + ''' then name else '''' end) [' + ltrim(px) + ']'
from (select distinct px from #) as a
set @sql = @sql + ' from # group by ads'
exec(@sql)地名 1 2 3 4
北京 牧师 圣骑士
杭州 盗贼 萨满 术士 战士
平顶山 法师
上海 德鲁伊
悉尼 猎人
irye 2008-03-21
谢谢 数字不是一列 是与职业一起的就行
liangCK 2008-03-21
/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/
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,指课程只有语文、数学、物理这三门课程。(以下同)
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 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 SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
---------------------------------
/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 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.姓名')
drop table tb
------------------
------------------
/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果)：
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/
create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go
--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end
--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')
--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t
--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。
--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程 分数
---- ------ ------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
------------------
*/
select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tbliangCK 2008-03-21
行列转换?
wzy_love_sly 2008-03-21
行列转换
liangCK 2008-03-21
1 战士 杭州
2 法师 平顶山
3 牧师 南京
4 盗贼 北京
5 术士 杭州
6 萨满 杭州
7 德鲁伊 上海
8 圣骑士 北京
9 猎人 悉尼
1 战士 5 术士 6 萨满 杭州
4 盗贼 8 圣骑士 北京
3 牧师 南京
9 猎人 悉尼
7 德鲁伊 上海
2 法师 平顶山
-------------
请问这个..数字是一列吗?..还是跟那个战士..什么的是一列的?
2 法师 平顶山
3 牧师 南京
4 盗贼 北京
5 术士 杭州
6 萨满 杭州
7 德鲁伊 上海
8 圣骑士 北京
9 猎人 悉尼
1 战士 5 术士 6 萨满 杭州
4 盗贼 8 圣骑士 北京
3 牧师 南京
9 猎人 悉尼
7 德鲁伊 上海
2 法师 平顶山
-------------
请问这个..数字是一列吗?..还是跟那个战士..什么的是一列的?
liangCK 2008-03-21
3.3 各种字符串合并处理示例.sql
--各种字符串分函数
--3.3.1 使用游标法进行字符串合并处理的示例。
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
--合并处理
--定义结果集表变量
DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))
--定义游标并进行合并处理
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY col1,col2
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
IF @col1=@col1_old
SELECT @s=@s+','+CAST(@col2 as varchar)
ELSE
BEGIN
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1
END
FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT * FROM @t
DROP TABLE tb
/*--结果
col1 col2
---------- -----------
a 1,2
b 1,2,3
--*/
GO
/*==============================================*/
--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
GO
--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(col2 as varchar)
FROM tb
WHERE col1=@col1
RETURN(STUFF(@re,1,1,''))
END
GO
--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
--删除测试
DROP TABLE tb
DROP FUNCTION f_str
/*--结果
col1 col2
---------- -----------
a 1,2
b 1,2,3
--*/
GO
/*==============================================*/
--3.3.3 使用临时表实现字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
--合并处理
SELECT col1,col2=CAST(col2 as varchar(100))
INTO #t FROM tb
ORDER BY col1,col2
DECLARE @col1 varchar(10),@col2 varchar(100)
UPDATE #t SET
@col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,
@col1=col1,
col2=@col2
SELECT * FROM #t
/*--更新处理后的临时表
col1 col2
---------- -------------
a 1
a 1,2
b 1
b 1,2
b 1,2,3
--*/
--得到最终结果
SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1
/*--结果
col1 col2
---------- -----------
a 1,2
b 1,2,3
--*/
--删除测试
DROP TABLE tb,#t
GO
/*==============================================*/
--3.3.4.1 每组 <=2 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'c',3
--合并处理
SELECT col1,
col2=CAST(MIN(col2) as varchar)
+CASE
WHEN COUNT(*)=1 THEN ''
ELSE ','+CAST(MAX(col2) as varchar)
END
FROM tb
GROUP BY col1
DROP TABLE tb
/*--结果
col1 col2
---------- ----------
a 1,2
b 1,2
c 3
--*/
--3.3.4.2 每组 <=3 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
UNION ALL SELECT 'c',3
--合并处理
SELECT col1,
col2=CAST(MIN(col2) as varchar)
+CASE
WHEN COUNT(*)=3 THEN ','
+CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar)
ELSE ''
END
+CASE
WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar)
ELSE ''
END
FROM tb a
GROUP BY col1
DROP TABLE tb
/*--结果
col1 col2
---------- ------------
a 1,2
b 1,2,3
c 3
--*/
GO北上广深10年面试经验老司机倾囊相授，让你少走5-10年弯路的面试私房课，必听.wmv 北上广深10年面试经验老司机倾囊相授，让你少走5-10年弯路的面试私房课，必听 1：你必须知道的软件从业者的3条主线职业路径 2：你的职业生涯已经被你的简历规划了 3：面试到底在谈什么？面试官心理分析 4：技术面试...
10年老司机倾囊相授，贴片晶振的PCB layout需要注意哪些？ 晶振有两个比较重要的参数，频偏和温偏，单位都是PPM，通俗说，晶振的标称频率不是一直稳定的，某些环境下晶振频率会有误差，误差越大，电路稳定性越差，甚至电路无法正常工作。 所以在PCB设计时，晶振的...
倾囊相授之性能分析思路 我还年轻的时候，经常听一些大会或者演讲。...而有了操作的功底之后，还有一个大的鸿沟要越过去，那就是从操作到对监控计数器的理解。这一步可以说让很多性能测试人员都望而却步了。 但是这还不算完，这一步迈过去
1024，程序员“赚钱”秘籍倾囊而赠！ 按照常规，今天应该更新技术干货，但今天是程序员的节日——1024，那就来点特别的——分享秘籍，程序员如何赚钱。...在讲堂里的还有一个我。“万岁！”他们都拍掌欢呼起来。 如果你意识到这事...
倾囊相授DDoS防护十二式 DDoS攻击对于在线业务如同噩梦。从BBC到Twitter，再到川普的网站，去年的网络攻击不绝于耳。 现在高科技产品千变万化，物联网设备备受瞩目，DDoS攻击已经是过去几年的数倍，...创建一个系统来预防响应潜在的DDoS攻击
七年交易经验，倾囊分享中长线交易秘诀 自2010年开始投资股票基金，接着参与炒作沪深股市最后一只权证长虹CWB1，继而开始踏入A股市场，之后陆续参与了大宗商品期货、现货、艺术品金融电子盘、外汇交易，这漫长的七年交易之路确实经历许多波折。如果有谁问...
自动驾驶概述 随着5G逐渐走进人们的视线，自动驾驶汽车成为一个社会热门话题。那么自动驾驶究竟是什么样的？它真的能实现吗？什么时候我们才能真正使用上呢？ 汽车是当今社会的主要交通工具之一，自动驾驶汽车是目前可以看到，...
Fusion Analyzer (FAS)：Fusion Analyzer (FAS) 是一个分析囊泡融合事件的程序。-matlab开发 Fusion Analyzer 软件 (FAS) 用于通过跨Kong膜中的脂质和内容物混合来分析囊泡融合。
沉默王二的黄金时代 我写这篇文章的缘由是什么，其实也和上面这些废话有关系，这就好比，你xxoo的时候要有前戏（这™有点太直接），或者说你屙屎之前可能要先放点五谷杂粮之气到外面（这话听起来怎么像在骂我自己）。
论文研究 - 一种不同的前置胎盘治疗方法：下子宫节段横向缝合技术与Bakri球囊应用的比较 这项研究的目的是确定子宫下段加压缝合线和bakri球囊在诊断为前置胎盘的患者中的成功应用。 材料与方法：回顾性分析Ondokuz Mayis大学2010年至2018年间因全前置胎盘（PPT）进行剖宫产的257例患者。 根据治疗方法将...
论文研究 - 儿童巨大颈囊性淋巴管瘤：一例的手术治疗 观察：这是一个3岁的女童，因右颈椎肿胀入CHU“卢森堡”耳鼻喉科。 临床检查发现，前-颈大颈椎肿胀，柔软，有弹性，无痛触诊，可相对于浅表和深平面移动，大小约为20 cm×17 cm，皮肤健康。 其余的身体检查不是特别...
