SQL点滴26—常见T-SQL面试解析

时间:2022-09-24 23:46:05

原文:SQL点滴26—常见T-SQL面试解析

它山之石可以攻玉,这一篇是读别人的博客后写下的,不是原原本本的转载,加入了自己的分析过程和演练。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
('一月份','',''),
('一月份','',''),
('一月份','',''),
('二月份','',''),
('二月份','',''),
('三月份','','')

create table #table5([部门] varchar(10),[部门名称] varchar(50))
insert into #table5 values
('','国内业务一部'),
('','国内业务二部'),
('','国内业务三部'),
('','国际业务部')

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' , '' );
insert into #table6 (id , 日期, 单据) values ( 2 , '2004-09-02' , '' );
insert into #table6 (id , 日期, 单据) values ( 3 , '2004-10-02' , '' );
insert into #table6 (id , 日期, 单据) values ( 4 , '2004-09-02' , '' );

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点滴26—常见T-SQL面试解析的更多相关文章

  1. 转:如何学习SQL(第一部分:SQL基础)

    转自:http://blog.163.com/mig3719@126/blog/static/285720652010950712271/ 1. 为什么学习SQL 自人类社会形成之日起,社会的运转就在 ...

  2. sql点滴41—mysql常见sql语法

    原文:sql点滴41-mysql常见sql语法 ALTER TABLE:添加,修改,删除表的列,约束等表的定义. 查看列:desc 表名; 修改表名:alter table t_book rename ...

  3. SQL点滴25—T-SQL面试语句,练练手

    原文:SQL点滴25-T-SQL面试语句,练练手 1. 用一条SQL语句查询出每门课都大于80分的学生姓名 name   kecheng    fenshu 张三     语文     81张三    ...

  4. sql点滴40—mysql乱码问题总结

    原文:sql点滴40-mysql乱码问题总结 本文将为大家讲解如何处理Java连接过程中的MySQL中文乱码问题.一般MySQL中文乱码问题都是与字符集有关,这里作者的经历也大致差不多. MySQL默 ...

  5. SQL点滴35—SQL语句中的exists

    原文:SQL点滴35-SQL语句中的exists 比如在Northwind数据库中有一个查询为 SELECT c.CustomerId,CompanyName FROM Customers c WHE ...

  6. SQL点滴22—性能优化没有那么神秘

    原文:SQL点滴22-性能优化没有那么神秘 经常听说SQL Server最难的部分是性能优化,不禁让人感到优化这个工作很神秘,这种事情只有高手才能做.很早的时候我在网上看到一位高手写的博客,介绍了SQ ...

  7. SQL点滴16—SQL分页语句总结

    原文:SQL点滴16-SQL分页语句总结 今天对分页语句做一个简单的总结,他们大同小异的,只要理解其中一个其他的就很好理解了. 使用top选项 *from Orders orderid from Or ...

  8. 常见的SQL错误和解决方法

    前言 今天你会看到每个人——从新手到专家——在使用SQL时犯的各种常见错误.你不能永远避免犯任何错误,但是熟悉广泛的错误将帮助你在尽可能短的时间内解决这些错误. 注:在我们的例子中我们使用的是Orac ...

  9. SQL常见优化Sql查询性能的方法有哪些?

    常见优化Sql查询性能的方法有哪些? 1.查询条件减少使用函数,避免全表扫描 2.减少不必要的表连接 3.有些数据操作的业务逻辑可以放到应用层进行实现 4.可以使用with as 5.使用“临时表”暂 ...

随机推荐

  1. &period;NET微信公众号开发-2&period;0创建自定义菜单

    一.前言 开发之前,我们需要阅读官方的接口说明文档,不得不吐槽一下,微信的这个官方文档真的很烂,但是,为了开发我们需要的功能,我们也不得不去看这些文档. 接口文档地址:http://mp.weixin ...

  2. String inputStream file转化

    String --> InputStreamByteArrayInputStream stream = new ByteArrayInputStream(str.getBytes()); Inp ...

  3. c&plus;&plus; 读写锁

    #ifndef THREAD_UTIL_H #define THREAD_UTIL_H #include <pthread.h> namespace spider { class Auto ...

  4. 正则表达式中&sol;i&comma;&sol;g&comma;&sol;m的作用

    一./i (ignorCase)忽略大小写,注意仅是忽略大小写,并不忽略全半角. 二./g (globle)全文查找出现的所有匹配字符 三./m 1.(mutiple)多行查找2.m 影响 ^.$.3 ...

  5. SQL Database学习笔记

    1. linux下快速安装MariaDB: MariaDB 是 一个采用 Maria 存储引擎的  MySQL  分支版本,是由原来 MySQL 的作者 Michael Widenius 创办的公司所 ...

  6. 手机控制电脑&comma;在WIFI局域网下&lpar;关机&comma;重启&comma;遥控&rpar;

    这个软件叫百变遥控:http://blog.sina.com.cn/s/blog_9abc7dbc0101hmut.html 今天周末,在家里看电影,家里用的是台式电脑,我自己买了一个投影仪来专门看视 ...

  7. &lbrack;Google Code Jam &lpar;Qualification Round 2014&rpar; &rsqb; A&period; Magic Trick

    Problem A. Magic Trick Small input6 points You have solved this input set.   Note: To advance to the ...

  8. python2 urllib2抓取51job网的招聘数据

    #coding=utf-8 __author__ = "carry" import sys reload(sys) sys.setdefaultencoding('utf-8') ...

  9. SpringMVC的标签库

            Spring2.0版本开始后,提供了一组功能强大的标签用来在JSP和SpringWebMVC中处理表单元素 ,可以用来访问控制器处理命令对象和绑定数据:         以下是表单标签 ...

  10. maven入门(10)maven的仓库

    [0]README 1)本文部分文字转自 "maven实战",旨在 review  "maven(6)仓库" 的相关知识:   [1]何为 Maven仓库 1) ...