3.3w+
社区成员
求一SQL语句,高手进,在线等候
create table tb1(
a1 nvarchar(10),
a2 nvarchar(10),
a3 nvarchar(10)
)
INSERT tb1 SELECT 'a',1,232.78
UNION ALL SELECT 'a',2,343.54
UNION ALL SELECT 'b',1,3434.45
UNION ALL SELECT 'b',2,908.00
UNION ALL SELECT 'c',1,100.00
UNION ALL SELECT 'c',2,234.00
要求输出结果为:
'a',232.78,343.54
'b',3434.45,908.00
'c',100.00,234.00
即取出1,3两列,并且把列转换成行,按第二列顺序的值大小排序
a1 nvarchar(10),
a2 nvarchar(10),
a3 nvarchar(10)
)
INSERT tb1 SELECT 'a',1,232.78
UNION ALL SELECT 'a',2,343.54
UNION ALL SELECT 'b',1,3434.45
UNION ALL SELECT 'b',2,908.00
UNION ALL SELECT 'c',1,100.00
UNION ALL SELECT 'c',2,234.00
要求输出结果为:
'a',232.78,343.54
'b',3434.45,908.00
'c',100.00,234.00
即取出1,3两列,并且把列转换成行,按第二列顺序的值大小排序
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
dawugui 2008-03-20
/*
标题：普通行列转换(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 课程
set @sql = '[' + @sql + ']'
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 tb
向蛮子 2008-03-20
另外,记录的条数可能有很多,第二列的值是不定的,可以是1,2,3,4,5........
向蛮子 2008-03-20
可能我没说清楚,必须用一个SQL语句来实现
dawugui 2008-03-20
create table tb1(
a1 nvarchar(10),
a2 nvarchar(10),
a3 nvarchar(10)
)
INSERT tb1 SELECT 'a',1,232.78
UNION ALL SELECT 'a',2,343.54
UNION ALL SELECT 'b',1,3434.45
UNION ALL SELECT 'b',2,908.00
UNION ALL SELECT 'c',1,100.00
UNION ALL SELECT 'c',2,234.00
select a1,
max(case a2 when 1 then a3 end) col1,
max(case a2 when 2 then a3 end) col2
from tb1
group by a1
drop table tb1
/*
a1 col1 col2
---------- ---------- ----------
a 232.78 343.54
b 3434.45 908.00
c 100.00 234.00
（所影响的行数为 3 行）
*/-狙击手- 2008-03-20
create table tb1(
a1 nvarchar(10),
a2 nvarchar(10),
a3 nvarchar(10)
)
INSERT tb1 SELECT 'a',1,232.78
UNION ALL SELECT 'a',2,343.54
UNION ALL SELECT 'b',1,3434.45
UNION ALL SELECT 'b',2,908.00
UNION ALL SELECT 'c',1,100.00
UNION ALL SELECT 'c',2,234.00
select a1,
a2 = max(case when a2 =1 then a3 else null end),
a3 = max(case when a2 =2 then a3 else null end)
from tb1
group by a1
/*
要求输出结果为:
a1 a2 a3
---------- ---------- ----------
a 232.78 343.54
b 3434.45 908.00
c 100.00 234.00
（所影响的行数为 3 行）
警告: 聚合或其它 SET 操作消除了空值。
*/
drop table tb1liangCK 2008-03-20
3.5.2 字符串并集&交集处理示例.sql
--示例数据
CREATE TABLE tb(ID int,col varchar(50))
INSERT tb SELECT 1,'1,2,3,4'
UNION ALL SELECT 1,'1,3,4'
UNION ALL SELECT 1,'1,4'
UNION ALL SELECT 2,'11,3,4'
UNION ALL SELECT 2,'1,33,4'
UNION ALL SELECT 3,'1,3,4'
GO
--1. 字符串并集处理函数
CREATE FUNCTION dbo.f_mergSTR(@ID int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @t TABLE(ID int identity,b bit)
--分拆处理辅助表,由于列col的最大宽度为50,所以只需要1到50的分拆辅助记录
INSERT @t(b) SELECT TOP 50 0
FROM syscolumns
DECLARE @r varchar(50)
SET @r=''
SELECT @r=@r+','+s
FROM(
SELECT s=SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
FROM tb a,@t b
WHERE a.ID=@ID
AND b.ID<=LEN(a.col)
AND SUBSTRING(','+a.col,b.ID,1)=','
GROUP BY SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
)a ORDER BY s
RETURN(STUFF(@r,1,1,''))
END
GO
--调用用户定义实现交并集查询
SELECT ID,col=dbo.f_mergSTR(ID)
FROM tb
GROUP BY ID
GO
/*================================================================*/
--2. 字符串交集处理函数
CREATE FUNCTION dbo.f_mergSTR(@ID int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @t TABLE(ID int identity,b bit)
--分拆处理辅助表,由于列col的最大宽度为50,所以只需要1到50的分拆辅助记录
INSERT @t(b) SELECT TOP 50 0
FROM syscolumns
DECLARE @r varchar(50)
SET @r=''
SELECT @r=@r+','+s
FROM(
SELECT s=SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
FROM tb a,@t b
WHERE a.ID=@ID
AND b.ID<=LEN(a.col)
AND SUBSTRING(','+a.col,b.ID,1)=','
GROUP BY SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
HAVING COUNT(*)=(SELECT COUNT(*) FROM tb WHERE ID=@ID))a
RETURN(STUFF(@r,1,1,''))
END
GO
--调用用户定义实现交交集查询
SELECT ID,col=dbo.f_mergStr(ID)
FROM tb
GROUP BY ID
GO基础类 - SQL语句
基础类 - SQL语句
<br />转载请标明出处： http://blog.csdn.net/hz/archive/2006/03/01/613376.asp 本文来自 CSDN 博客。x
sql server 怎么跨库查询？
如何判断字符的大小写 ?
如何...
MS-SQL Server 基础类 - SQL语句 高分请教巨难sql语句，在线等候高手指教。 多表关联 二个问题 如何用一条SQL语句来获得表equip中的符合条件的记录集以及统计出所含记录条数 怎样选出一个Field的说明? order by 排序的问题 复杂问题，高手请进（高分...
如何使用ZOL一键安装器下载中关村在线的源安装包 中关村在线最近开始推出ZOL一键安装器，用户直接下载的并不是别人发布的... 本人以一个具体的实例，来讲述了一种方法，来实现使用ZOL一键安装器来下载中关村在线的《万能数据库查询分析器》的中文版本源安装程序。
怎样使用ZOL一键安装器下载中关村在线的源安装包 怎样使用ZOL一键安装器下载中关村在线的源安装包马根峰 (广东联合电子服务股份有限公司, 广州 510300) 摘要 中关村在线近期開始推出ZOL一键安装器，用户直接下载的并非别人公布的安装包。而是带有安装包编码...
2020-09-15 JAVA面试题整理 个人觉得面试也像是一场全新的征程，失败和胜利都是平常之事。所以，劝各位不要因为面试失败而灰心、 丧失斗志。也不要因为面试通过而沾沾自喜，等待你的将是更美好的未来，继续加油！ 本篇分享的面试题内容包括：...
WEB开发文档2 总结 高手请进：我要获取一个指定asp网页的html代码，真正的难题是：这个指定asp网页必须接受到post表单发出的参数才能正常显示. 请教微软专家：分工程后的Session问题 极速体验Vs2005(一) 为什么...
连载IT小说《失业的程序员》讲述一个Android开发者的奋斗史 失业的程序员(一) 不小心，我失业了。 原因是前几天和我的部门经理拍了桌子，我的组员去内蒙古出差，项目没有中标。年后，长得很像猪刚烈的部门经理发飙了，要辞退我的组员。 我纳闷了，我的组员是技术...
dwz.... 描述：在DWZ版中lookup的处理机制与经典的完全不同，对于lookup有两种展现形式，一种是通过点击lookup图标弹出对话框后带回选中的值，另一种是通过在文本框中输入相应信息从而实现联机式的查找带回效果。从实现目的...
Oracle数据库资料收藏 Oracle数据库资料收藏 1、UNIX基本培训 - [fjlin]2、AIX入门与提高（1）－中文 - [richard_he]3、PL/SQL上课记录 - [jxdco]4、[分享]2005年IBM原厂的AIX培训资料 - [fsm]5、Oracle入门 让学习更轻松 - [migrator]6...
ASP错误大全(一) 对初学者们有帮助,或许高手也会忘记,ASP错误大全===========================================================MicrosoftVBScript语*错误(0x800A03E9)-->内存不足MicrosoftVBScript语*错误(0x800A03EA)--&...
网站安全类精彩文章的摘抄 近来，网络上的SQL Injection 漏洞利用攻击，JS脚本，HTML脚本攻击似乎逾演逾烈.陆续的很多站点都被此类攻击所困扰，并非像主机漏洞那样可以当即修复，来自于WEB的攻击方式使我们在防范或者是修复上都带来了很大的...
袖珍拾遗（转贴来源于流水无声） 关键字 SQL Server 与 Excel /*存储过程名称：导出数据到Excel功能描述：导出数据到ExcelEXEC ExportToExcel @server = ., @uname = sa, @pwd = , @QueryText = SELECT *
目前使用的JAVA数据库链接管理类 首先需要设置好数据库连接池，参见Javaworld@TW出的wiki由于我们的服务器上仅有一个应用，使用的是在web.xml中添加一个随容器启动的servlet来实现加载连接池的方法，该servlet通过读取WEB-INF/config/proxool.xml...
Windows WEB服务器配置安全规范 目 录一、安装 Win 200x 安全概览1.硬盘分区的文件系统选择2.组件的定制3.接入网络时间4.账户安全管理5.安全审核6.卸载无用的组件模块二、基本系统设置1.安装各种补丁2.分区内容规划3.协议管理4.关闭所有以下不需要...
