sql练手

 

最近发现自己写sql不熟练，花些时间强化练习,下面是我在博客园里找的题，都自己做了遍：

1.一道SQL语句面试题，关于group by
表内容：
小王 胜
小王 胜
小王 负
小王 负
小张 胜
小张 负
小张 负

如果要生成下列结果, 该如何写sql语句?

    胜 负
小王 2 2
小张 1 2
------------------------------------------

构建题目：
create table #chase(name varchar(10),result nchar(1))
insert into #chase values('小王','胜')
insert into #chase values('小王','胜')
insert into #chase values('小王','负')
insert into #chase values('小王','负')
insert into #chase values('小张','胜')
insert into #chase values('小张','负')
insert into #chase values('小张','负')

我的答案：

select a.aname,a.胜局,b.负局 
    from (select name aname,count(result) as 胜局 from chase
        where result='胜'
    group by name) as a
inner join
(select name bname,count(result) as 负局 from chase
    where result='负'
        group by name) as b
on 
a.aname=b.bname

答案2：

select name, sum(case when result='胜' then 1 else 0 end)胜局,sum(case when result='负' then 1 else 0 end)负局
from chase
group by name

结果：

 

2.请教一个面试中遇到的SQL语句的查询问题
表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
------------------------------------------
select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name

3.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：  
   大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。  
       显示格式：  
       语文              数学                英语  
       及格              优秀                不及格 

构建题目：

create table #tempTable (语文 nvarchar(50) NULL,数学 nvarchar(50) NULL,英语 nvarchar(50) NULL)
insert into #tempTable(语文,数学,英语)values('60','50','90')

 

我的答案：

 

select (case When 语文<60 then '不及格'
         when 语文 >= 60 and 语文 <79 then '及格'
         when 语文>79 then '优秀'
        end
        )语文,(case When 数学<60 then '不及格'
         when 数学 > 60 and 数学 <79 then '及格'
         when 数学>79 then '优秀'
        end
        )数学,(case When 英语<60 then '不及格'
          when 英语 > 60 and 英语 <79 then '及格'
          when 英语>79 then '优秀'
        end
        )英语
from #tempTable

结果：

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

table1

月份mon 部门dep 业绩yj
-------------------------------
一月份      01      10
一月份      02      10
一月份      03      5
二月份      02      8
二月份      04      9
三月份      03      8

table2

部门dep      部门名称dname
--------------------------------
      01      国内业务一部
      02      国内业务二部
      03      国内业务三部
      04      国际业务部

table3 （result）

部门dep 一月份      二月份      三月份
--------------------------------------
      01      10        null      null
      02      10         8        null
      03      null       5        8
      04      null      null      9

------------------------------------------
构建表：

 

create table #YEJI (mon nvarchar(50) NULL,dep nvarchar(50) NULL,yj nvarchar(50) NULL)
insert into #YEJI(mon,dep,yj)values('一月份','01','10')
insert into #YEJI(mon,dep,yj)values('一月份','02','10')
insert into #YEJI(mon,dep,yj)values('一月份','03','5')
insert into #YEJI(mon,dep,yj)values('二月份','02','8')
insert into #YEJI(mon,dep,yj)values('二月份','04','9')
insert into #YEJI(mon,dep,yj)values('三月份','03','8')

select * from #YEJI

create table #DPT (depID nvarchar(50) NULL,depName nvarchar(50) NULL)
insert into #DPT(depID,depName)values('01','国内业务一部')
insert into #DPT(depID,depName)values('02','国内业务二部')
insert into #DPT(depID,depName)values('03','国内业务三部')
insert into #DPT(depID,depName)values('04','国际业务部')

 

我的答案：

SELECT DEPID,(select yj from #YEJI where mon='一月份' and #YEJI.dep=DEPID) as 一月,
(select yj from #YEJI where mon='二月份' and #YEJI.dep=DEPID) as 二月
,(select yj from #YEJI where mon='三月份' and #YEJI.dep=DEPID) as 三月
,(select yj from #YEJI where mon='四月份' and #YEJI.dep=DEPID) as 四月 
FROM #DPT 

 

结果：

 

5。要求:设计一个查询，返回结果如下：

ID 日期 单据
---------- ----------- ---
1 2004-08-02 001
4 2004-09-02 002

即对于每个单据号，返回日期最小的行。

CREATE TABLE #表 
(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' );

select * from #表

select b.id,a.单据,a.minD from
(select 单据,min(日期) minD from #表 group by 单据) as a
left join 
#表 b
on
a.minD=b.日期
and a.单据 =b.单据

select a.* from #表 a where 
a.日期 in (select min(日期) from #表 b where b.单据=a.单据 )

 

方法二、方法三：

select * from #表 a
where  a.日期< any(select 日期 from #表 where 单据=a.单据 ) 

select * from #表 a
not exsit (select 1 from #表 where 单据=a.单据 and 日期<a。日期 )

 

结果：

 

  

 

create table #tTable
(
productid int,
productname varchar(10),
PARENTID INT,
clicknum int
)

INSERT INTO #tTable(productid,productname,PARENTID,clicknum)
VALUES(1,'男士衣服','1',90)
INSERT INTO #tTable(productid,productname,PARENTID,clicknum)
VALUES(2,'女士衣服','1',80)
INSERT INTO #tTable(productid,productname,PARENTID,clicknum)
VALUES(3,'男士裤子','2',70)
INSERT INTO #tTable(productid,productname,PARENTID,clicknum)
VALUES(4,'女士裤子','2',90)
INSERT INTO #tTable(productid,productname,PARENTID,clicknum)
VALUES(5,'男士帽子','5',15)
INSERT INTO #tTable(productid,productname,PARENTID,clicknum)
VALUES(6,'女士帽子','5',30)
INSERT INTO #tTable(productid,productname,PARENTID,clicknum)
VALUES(7,'男士鞋子','10',65)
INSERT INTO #tTable(productid,productname,PARENTID,clicknum)
VALUES(8,'女士鞋子','10',52)
INSERT INTO #tTable(productid,productname,PARENTID,clicknum)
VALUES(9,'女士鞋子1','10',54)

SELECT * FROM #tTable

SELECT PARENTID,MAX(CLICKNUM) FROM #tTable GROUP BY PARENTID

SELECT * FROM #tTable A WHERE CLICKNUM IN (SELECT MAX(CLICKNUM) FROM #tTable  WHERE PARENTID=A.PARENTID )