SQL随笔_2

摘要:
代码如下:createtable#salaryinsertinto#salaryvalues,,selectb.years,SUMfrom#salarya,#salarywherea。years˂=b.yearsgroupby.yearsorderbyb.years另一种方法是使用子查询。第一列是年份,第二列是本年度所有小于或等于第一列的工资总和,这也是比较直接的。代码如下:selects1。yearsys,来自#salaris12的资产。现在我们假设只有一个表,称为pages,有四个字段,id、url、title和body。输赢的想法2005-05-09222005-05-1012:首先,应该按时间分组,然后用和来计算输赢的次数。

它山之石可以攻玉,这一篇是读别人的博客后写下的,不是原原本本的转载,加入了自己的分析过程和演练。sql语句可以解决很多的复杂业务,避免过多的项目代码,下面几个语句很值得玩味。

  

1. 已经知道原表 year salary 2000 1000 2001 2000 2002 3000 2003 4000 怎么查询的到下面的结果,就是累积工资 year salary 2000 1000 2001 3000 2002 6000 2003 10000

思路:这个需要两个表交叉查询得到当前年的所有过往年,然后再对过往年进行聚合。代码如下:

 
create table #salary(years int ,salary int ) insert into #salary values (2000, 1000), (2001, 2000), (2002, 3000), (2003, 4000)
select b.years,SUM(a.salary) from #salary a,#salary b where a.years<=b.years group by b.years order by b.years
 

  

还有一种方法是使用子查询,第一列是年,第二列是所有小于等于第一列这年的工资总和,也比较直接,代码如下:

select  s1.years as years, (select sum(s2.salary) from #salary s2 where s2.years<=s1.years) as salary  from #salary s1

  

2. 现在我们假设只有一个table,名为pages,有四个字段,id, url,title,body。里面储存了很多网页,网页的url地址,title和网页的内容,然后你用一个sql查询将url匹配的排在最前, title匹配的其次,body匹配最后,没有任何字段匹配的,不返回。

思路:做过模糊搜索对这个应该很熟悉的,可以使用union all依次向一个临时表中添加记录。这里使用order by和charindex来是实现,代码如下:

 
create table #page(id int, url varchar(100),title varchar(100), body varchar(100)) insert into #page values (1,null,'abcde','abcde'), (2,null,'abcde',null), (3,'abcde','e',null)
select * from #page where url like '%e%' or title like '%e%' or body like '%e%' order by case when (charindex('e', url)>0) then 1 else 0 end desc, case when (charindex('e', title)>0) then 1 else 0 end desc, case when (charindex('e', body)>0) then 1 else 0 end desc
 

  

只要出现一次就会排在前面,这种情况如果两行都出现就会比较下一个字段,以此类推。

还有一种实现,类似于记分牌的思想,如下:

 
select a.[id],sum(a.mark) as summark  from ( select #page.*,10 as mark from #page where #page.[url] like '%b%' union select #page.*,5 as mark from #page where #page.[title] like '%b%' union select #page.*,1 as mark from #page where #page.[body] like '%b%' ) as a  group by id order by summark desc
 

    

3. 表内容: 2005-05-09 胜 2005-05-09 胜 2005-05-09 负 2005-05-09 负 2005-05-10 胜 2005-05-10 负 2005-05-10 负
如果要生成下列结果, 该如何写sql语句?
                       胜负 2005-05-09  2    2 2005-05-10  1    2

思路:首先要有group by 时间,然后是使用sum统计胜负的个数。代码如下:

 
create table #scores(dates varchar(10),score varchar(2)) insert into #scores values ('2005-05-09', '胜'), ('2005-05-09', '胜'), ('2005-05-09', '负'), ('2005-05-09', '负'), ('2005-05-10', '胜'), ('2005-05-10', '负'), ('2005-05-10', '负')
select a.dates as [比赛时间], SUM(case a.score when '胜' then 1 else 0 end) as [胜], SUM(case a.score when '负' then 1 else 0 end) as [负] from #scores a group by a.dates
 

  

还有一种方法是使用子查询,先用两个子查询得到这些日期中的胜负常数,然后连接查询,代码如下:

 
select  t1.dates as [比赛时间], t1.score as [胜], t2.score as [负] from (select a.dates as dates, COUNT(1) as score from #scores a where a.score='胜'  group by a.dates) t1 inner join (select a.dates as dates, COUNT(1) as score from #scores a where a.score='负'  group by a.dates) t2 on t1.dates=t2.dates
 

  

  

4. 表中有A B C三列,用SQL语句实现:当A列大于B列时选择A列否则选择B列,当B列大于C列时选择B列否则选择C列

思路:这个字面意思很简单了,就是二者选其一,使用case就可以实现,代码如下:

 
create table #table3(A int, B int ,C int) insert into #table3 values (2,1,3), (4,2,5)
select case when A>B then A else B end as AB, case when B>C then B else C end as BC from #table3
 

  

  

5. 请用一个sql语句得出结果

从table1,table2中取出如table3所列格式数据,注意提供的数据及结果不准确,只是作为一个格式向大家请教。

table1

月份          部门业绩

一月份      01      10

一月份      02      10

一月份      03      5

二月份      02      8

二月份      04      9

三月份      03      8

table2

部门     部门名称

01      国内业务一部

02      国内业务二部

03      国内业务三部

04      国际业务部

table3 (result)

部门部门名称  一月份      二月份      三月份

  01  国内业务一部    10        null      null

  02   国内业务二部   10         8        null

03   国内业务三部   null       5        8

  04   国际业务部   null      null      9

思路:又是行列转换,不过这个稍微复杂一点代码如下:

 
create table #table4([月份] varchar(10),[部门] varchar(10),[业绩] int) insert into #table4 values ('一月份','01','10'), ('一月份','02','10'), ('一月份','03','5'), ('二月份','02','8'), ('二月份','04','9'), ('三月份','03','8')
create table #table5([部门] varchar(10),[部门名称] varchar(50)) insert into #table5 values ('01','国内业务一部'), ('02','国内业务二部'), ('03','国内业务三部'), ('04','国际业务部')
select [部门],[部门名称],[一月份],[二月份],[三月份] from(select a.[月份] ,a.[部门] as [部门],b.[部门名称],a.[业绩] from #table4 a join #table5 b on a.[部门]=b.[部门] ) sod pivot(min(sod.[业绩]) for sod.[月份] in([一月份],[二月份],[三月份])) pvt order by [部门]
 

  

注意,这里每个月份每个部门只有一行数据,所以pivot运算的时候可以使用min函数,使用max,min都可以。如果这里有多行数据,那么一般会让计算合计,只能用sum了

还有一种方法是使用子查询,这个代码要多一点,如下:

select a.[部门] ,b.[部门名称], SUM(case  when a.月份='一月份' then a.[业绩] else 0 end) as [一月份], SUM(case  when a.月份='二月份' then a.[业绩] else 0 end) as [二月份], SUM(case  when a.月份='三月份' then a.[业绩] else 0 end) as [三月份] from #table4 a inner join #table5 b on a.[部门] =b.[部门]group by a.[部门],b.[部门名称]

6. 表结构以及数据如下:

CREATE TABLE #table6

(ID int, 日期 varchar(11), 单据 char(3))

INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 1 , '2004-08-02' , '001' );

INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 2 , '2004-09-02' , '001' );

INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 3 , '2004-10-02' , '002' );

INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 4 , '2004-09-02' , '002' );

要求:设计一个查询,返回结果如下:

ID 日期      单据

1 2004-08-02 001

4 2004-09-02 002

思路:这个是要找到日期比较小的那一条单据,这个有多种方法实现。第一种方法是相关子查询,如下:

 
create table #table6  (id int, 日期varchar(11), 单据char(3)) insert into #table6 (id , 日期, 单据) values ( 1 , '2004-08-02' , '001' ); insert into #table6 (id , 日期, 单据) values ( 2 , '2004-09-02' , '001' ); insert into #table6 (id , 日期, 单据) values ( 3 , '2004-10-02' , '002' ); insert into #table6 (id , 日期, 单据) values ( 4 , '2004-09-02' , '002' );
select * from #table6 a where a.[日期] = (select MIN(b.[日期]) from #table6 b where b.[单据] =a.[单据] )
 

还可以使用join连接,如下:

select a.* from #table6 a join (select b.[单据] , MIN(b.[日期]) as [日期] from #table6 b group by b.[单据]) c on a.[日期] = c.[日期] and a.[单据] = c.[单据]

注意最后on条件必须是a.[日期] = c.[日期] and a.[单据] = c.[单据],因为c表只是找出来两组符合条件的数据,如果只是a.[日期] = c.[日期]的话会找出多条不符合要求的数据。

还可以不使用join连接,如下:

select a.* from #table6 a , (select b.[单据] , MIN(b.[日期]) as [日期] from #table6 b group by b.[单据]) c where a.[日期] = c.[日期] and a.[单据] = c.[单据]

还可以使用谓词exist,如下:

select * from #table6 a where not exists (select 1 from #table6 where [单据]=a.[单据] and a.[日期]>[日期])

注意not exists查询筛选得到时间最小的那条记录,注意这里不能使用exists,exists会得到多条。可以理解为a中的日期不会大于子查询中所有日期,就是那个最小的日期。还有去掉[单据]=a.[单据],也会得到更多的数据,这个和普通的情况刚好相反。因为加上这个条件整个子查询会得到更多的数据,否则只保留a.[日期]>[日期]只会得到一条数据。

    

7. 已知下面的表

id  strvalue type

1    how      1

2    are      1

3    you      1

4    fine     2

5    thank    2

6    you      2

要求用sql把它们搜索出来成为这样的

#how are you#fine thank you#

思路:这个和上一篇中的最后一题很相似,也是连接有相同字段的字符,上回使用游标实现的,这次用for xml来实现,代码如下:

 
create table #table7(id int,strvalue varchar(20),typ int) insert into #table7 values (1,'how',1), (2,'are',1), (3,'you',1), (4,'fine',2), (5,'thank',2), (6,'you',2) select * from #table7
select (select '#'+replace(replace((select strvalue from #table7 t where typ = 1 for xml auto),'<t strvalue="',''),'"/>', '')+'#') + (select replace(replace((select strvalue from #table7 t where typ = 2 for xml auto),'<t strvalue="',''),'"/>', '')+'#')
 

或者这样

select '#'+ ltrim((select ''+a.strvalue from #table7 a where a.typ=1 for xml path('')))+'#'+ ltrim((select ''+a.strvalue from #table7 a where a.typ=2 for xml path('')))+'#'

或者这样,用变量来处理

 
declare @value varchar(1000)='#' select  @value=''+@value+ a.strvalue+'' from #table7 a where a.typ=1 select @value=@value+'#' select  @value= @value+ a.strvalue+'' from #table7 a where a.typ=2 select @value=@value+'#' print @value
 

for xml是好东西啊,是解决这类字符连接问题的利刃

免责声明:文章转载自《SQL随笔_2》仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇使用ICE遇到的编译问题SQL随笔_1下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

随便看看

移动开发者生态实录:在失败与成功的天平上 狼人:

移动开发者当然不是这两年才出现的角色,但是个人开发者和小型团队开发者通过开发应用程序盈利却是在最近两年移动互联网尘嚣日上之后才出现的。在靠卖License作为主要盈利模式的传统“大”软件行业,不管中小型开发者如何优秀,都不太可能在与微软、Sun、CA等大型软件公司的较量中赢得胜算。看看那些只有十几人甚至几个人的团队,公司小到连封闭会议室都没有;团队的成员少...

用gdb调试程序笔记: 以段错误(Segmental fault)为例_哎呀呀!_百度空间

用gdb调试程序笔记: 以段错误(Segmental fault)为例_哎呀呀!_百度空间 用gdb调试程序笔记: 以段错误(Segmental fault)为例 2011-06-05 12:52 这个笔记是我以前发表在电子科大的BBS上的,大家反应较好,特贴到这里和童鞋分享! 笔记内容:1.背景介绍2.程序中常见的bug分类3.程序调试器(如gdb)有...

C++ 程序员必读书目清单

C++ 程序员必读书目清单 - 软件编程 - OPEN开源资讯 C++ 程序员必读书目清单 作者openkk 2012-06-08 20:23:36 原文:Must read C++ book list 多读一些优秀的书籍,对于开发者稳固编程基础、提高编程技能有很大帮助。但是,大多时候,初学者不知道应该读什么书入门,有一定基础的开发者不知道如何进...

Nginx负载均衡服务器的双机高可用

(15) Nginx负载均衡服务器的双机高可用 - Nginx Linux - 乐维UP Nginx负载均衡服务器的双机高可用 1 何勇 主机真实IP:公网 61.1.1.4备机真实IP:公网 61.1.1.5 公网虚拟IP:61.1.1.2我想请问一下,我租用的2台服务器各自只有一个公网IP。要搭建nginx的负载均衡双机高可...

UltraMemcache , UltraMySQL 初试

UltraMemcache , UltraMySQL 初试 - 冷眼 - ITeye技术网站 UltraMemcache , UltraMySQL 初试 博客分类:python gevent 这两个是由ESN公司开发的的memcache、mysql客户端 显著特征是: 完全由C、C++完成,更高效 可以与gevent的socket mo...

mysql主从复制

GRANT REPLICATION SLAVE ON *.* TO 'root'@'10.10.243.179' IDENTIFIED BY '123'; GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY '123'; MySQL主从复制配置 - orochihuang - 博客园 MyS...