求SQL Server 2005 高级查询的题目 40wei2010happy]

发布时间:2016-12-8 17:52:29 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"求SQL Server 2005 高级查询的题目 40wei2010happy]",主要涉及到求SQL Server 2005 高级查询的题目 40wei2010happy]方面的内容,对于求SQL Server 2005 高级查询的题目 40wei2010happy]感兴趣的同学可以参考一下。

本人需要一些SQL Server 2005 高级查询的题目,最好是有点难度的。哪位高人可以提供? 如有请把文件发到我QQ邮箱 QQ:649035901 或加我QQ。 

/* 标题:普通行列转换(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

/* 标题:数据拆分1 作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 时间:2008-11-20 地点:广东深圳 描述 有表tb, 如下: id          value ----------- ----------- 1           aa,bb 2           aaa,bbb,ccc 欲按id,分拆value列, 分拆后结果如下: id          value ----------- -------- 1           aa 1           bb 2           aaa 2           bbb 2           ccc */ --1. 旧的解决方法(sql server 2000) SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b  SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id) FROM tb A, # B WHERE SUBSTRING(',' + A.[value], B.id, 1) = ',' DROP TABLE # --2. 新的解决方法(sql server 2005)  create table tb(id int,value varchar(30)) insert into tb values(1,'aa,bb') insert into tb values(2,'aaa,bbb,ccc') go SELECT A.id, B.value FROM(     SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb )A OUTER APPLY(     SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v) )B DROP TABLE tb /* id          value ----------- ------------------------------ 1           aa 1           bb 2           aaa 2           bbb 2           ccc (5 行受影响) */ SQL2005用Xml: select      a.COl1,b.Col2 from      (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a outer apply     (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b SQL05用CTE: ;with roy as  (select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab union all select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>'' ) select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0) 生成结果: /* Col1        COl2 ----------- ----- 1           a 1           b 1           c 2           d 2           e 3           f */

/* 标题:SQL SERVER 2005中查询指定节点及其所有子节点的函数(表格形式显示) 作者:爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)  时间:2010-02-02 地点:新疆乌鲁木齐 */ create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10)) insert into tb values('001' , null  , N'广东省') insert into tb values('002' , '001' , N'广州市') insert into tb values('003' , '001' , N'深圳市') insert into tb values('004' , '002' , N'天河区') insert into tb values('005' , '003' , N'罗湖区') insert into tb values('006' , '003' , N'福田区') insert into tb values('007' , '003' , N'宝安区') insert into tb values('008' , '007' , N'西乡镇') insert into tb values('009' , '007' , N'龙华镇') insert into tb values('010' , '007' , N'松岗镇') go DECLARE @ID VARCHAR(3) --查询ID = '001'的所有子节点 SET @ID = '001' ;WITH T AS (   SELECT ID , PID , NAME    FROM TB   WHERE ID = @ID   UNION ALL   SELECT A.ID , A.PID , A.NAME    FROM TB AS A JOIN T AS B ON A.PID = B.ID ) SELECT * FROM T ORDER BY ID /* ID   PID  NAME ---- ---- ---------- 001  NULL 广东省 002  001  广州市 003  001  深圳市 004  002  天河区 005  003  罗湖区 006  003  福田区 007  003  宝安区 008  007  西乡镇 009  007  龙华镇 010  007  松岗镇 (10 行受影响) */ --查询ID = '002'的所有子节点 SET @ID = '002' ;WITH T AS (   SELECT ID , PID , NAME    FROM TB   WHERE ID = @ID   UNION ALL   SELECT A.ID , A.PID , A.NAME    FROM TB AS A JOIN T AS B ON A.PID = B.ID ) SELECT * FROM T ORDER BY ID /* ID   PID  NAME ---- ---- ---------- 002  001  广州市 004  002  天河区 (2 行受影响) */ --查询ID = '003'的所有子节点 SET @ID = '003' ;WITH T AS (   SELECT ID , PID , NAME    FROM TB   WHERE ID = @ID   UNION ALL   SELECT A.ID , A.PID , A.NAME    FROM TB AS A JOIN T AS B ON A.PID = B.ID ) SELECT * FROM T ORDER BY ID /* ID   PID  NAME ---- ---- ---------- 003  001  深圳市 005  003  罗湖区 006  003  福田区 007  003  宝安区 008  007  西乡镇 009  007  龙华镇 010  007  松岗镇 (7 行受影响) */ drop table tb --注:除ID值不一样外,三个SQL语句是一样的。

谢谢, 学习了。 

精华帖 大多都很经典 可以看看 不贴了

真的么? 你确定么? 。。。 去看技术内幕 

相当的不错额!

该回复于2010-04-16 13:10:01被版主删除

很不错

相当不错啊,支持了

Very helpful thanks dear

哥们儿,谢了!

/* 标题:数据拆分1 作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 时间:2008-11-20 地点:广东深圳 描述 有表tb, 如下: id          value ----------- ----------- 1           aa,bb 2           aaa,bbb,ccc 欲按id,分拆value列, 分拆后结果如下: id          value ----------- -------- 1           aa 1           bb 2           aaa 2           bbb 2           ccc */ --1. 旧的解决方法(sql server 2000) SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b  SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id) FROM tb A, # B WHERE SUBSTRING(',' + A.[value], B.id, 1) = ',' DROP TABLE # --2. 新的解决方法(sql server 2005)  create table tb(id int,value varchar(30)) insert into tb values(1,'aa,bb') insert into tb values(2,'aaa,bbb,ccc') go SELECT A.id, B.value FROM(     SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb )A OUTER APPLY(     SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v) )B DROP TABLE tb /* id          value ----------- ------------------------------ 1           aa 1           bb 2           aaa 2           bbb 2           ccc (5 行受影响) */

Great!刚好需要!

每天回帖即可获得10分可用分!

该回复于2010-11-04 09:54:30被版主删除

顶.......

各位大虾,以下是我的搜索存储过程 ALTER  PROCEDURE GetFlowByID @Type   varchar(50), @deptype varchar(50), @Title varchar(500), @FileZi varchar(50), @FileYear int, @Keywords varchar(300), @DBSaveDate datetime, @Remark8 varchar(50), @SaveLevel varchar(50), @Remark4 varchar(50), @OtherType varchar(50), @Remark10 varchar(50) AS     BEGIN TRANSACTION         -- Selct Flows from FlowControl  Table         SELECT * FROM DBFile WHERE    Type = @Type AND   deptype = @deptype AND  Title like [email protected]+'%' AND  FileZi like [email protected]+'%' AND  FileYear = @FileYear AND  Keywords like [email protected]+'%' AND  DBSaveDate = @DBSaveDate AND  Remark8 like [email protected]+'%' AND  SaveLevel like [email protected]+'%'  AND  Remark4 <> @Remark4 AND  OtherType = @OtherType AND Remark10 = @Remark10 order by Title asc     COMMIT TRANSACTION     RETURN 0 -------------------------------------------------- GO SET QUOTED_IDENTIFIER OFF  GO SET 如果想当 参数 @FileYear 为0的时候 按条件搜索时就不要搜索FileYear这个字段,DBSaveDate 为空时也不需要搜索此字段,以此类推除了 "LIKE" 之外所有为空或者为0的字段都不需要所搜索 请各位大虾帮忙顶顶

上一篇:关于SQL2005升级到SQL2008的问题~~急~~ 40yuki58]
下一篇:[推荐] ------------------我的一些笔记(五)(服务器与客户端配置)(基于2005)------------------ 20fredrickhu]

相关文章

相关评论