SQL语句练习题

1.关于group by 的题（行转列）

create table tmp(rq varchar(10),shengfu nchar(1));
insert into tmp(rq, shengfu) values('2005-05-09','胜');
insert into tmp (rq, shengfu) values('2005-05-09','胜');
insert into tmp (rq, shengfu) values('2005-05-09','负');
insert into tmp (rq, shengfu) values('2005-05-09','负');
insert into tmp (rq, shengfu) values('2005-05-10','胜');
insert into tmp (rq, shengfu) values('2005-05-10','负');
insert into tmp (rq, shengfu) values('2005-05-10','负');

想要的结果：

#行转列 借助于sum() case when end 
#法一
select rq, sum(case when shengfu='胜' then 1 else 0 end) as '胜', sum(case when shengfu='负' then 1 else 0 end) as '负'
from  tmp
group by rq
#法二
select N.rq, N.sheng, M.fu 
from (
select rq,count(*) as sheng from tmp where shengfu='胜' group by rq ) N
inner join (
select rq,count(*) as fu from tmp where shengfu='负' group by rq)  M
on N.rq=M.rq 

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

create table tmp2(A varchar(10),B varchar(10),C varchar(10));

insert into tmp2(A, B,C) values('1','2','3');
insert into tmp2(A, B,C) values('6','5','4');
insert into tmp2(A, B,C) values('8','7','9');
insert into tmp2(A, B,C) values('12','10','11');
insert into tmp2(A, B,C) values('13','15','14');
select * from tmp2
#表中有A B C三列,用SQL语句实现：
#当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
select (case when A>B then A else B end) as '选择1' ,(case when B>C then B else C end) as '选择2'
from tmp2

结果：

3请取出表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间)

#请取出表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间)

以目前三大主流数据库oracle，mysql，sqlserver分别说明。

如果是oracle，需要用to_date函数将字符型数据转换，插入到数据库的日期型数据中。

如：

1 2 3 4 5

create table test (cdate date);   insert into test values (to_date('2015-08-13','yyyy-mm-dd')); commit;

如果是mysql或者sqlserver，可直接以字符形式插入，数据库会自动将字符转成日期。

1 2 3 4

create table test (cdate datetime);   insert into test values ('2015-08-13');

需要注意：oracle中插入后需要commit（提交），否则在关闭当前会话后，插入是不成功的。

create table td2(A varchar(10),B varchar(10),datediff datetime);

insert into td2(A, B,datediff) values('1','2',now());
insert into td2(A, B,datediff) values('6','5',curdate());
select * from td2

select * from td2 where datediff(now(),datediff)=0

mysql中日期的比较是可以直接用字符串进行比较的。下面是一些测试

<span style="font-family: 'Courier New';">mysql> select * from td2</span>

    -> ;
+---+---+---------------------+
| A | B | datediff            |
+---+---+---------------------+
| 1 | 2 | 2016-09-11 16:34:51 |
| 1 | 2 | 2009-06-08 00:00:00 |
| 2 | 2 | 2009-06-08 00:00:00 |
| 2 | 2 | 2009-06-09 00:00:00 |
+---+---+---------------------+
4 rows in set

mysql> select * from td2 where datediff(now(),datediff)=0;
+---+---+---------------------+
| A | B | datediff            |
+---+---+---------------------+
| 1 | 2 | 2016-09-11 16:34:51 |
+---+---+---------------------+
1 row in set

mysql> select * from td2 where datediff('2009-06-08
',datediff)=0;
+---+---+---------------------+
| A | B | datediff            |
+---+---+---------------------+
| 1 | 2 | 2009-06-08 00:00:00 |
| 2 | 2 | 2009-06-08 00:00:00 |
+---+---+---------------------+
2 rows in set
mysql> select * from td2 where datediff>'2009-06-08';
+---+---+---------------------+
| A | B | datediff            |
+---+---+---------------------+
| 1 | 2 | 2016-09-11 16:34:51 |
| 2 | 2 | 2009-06-09 00:00:00 |
+---+---+---------------------+
2 rows in set

mysql> select * from td2 where datediff=
'2009-06-08';
+---+---+---------------------+
| A | B | datediff            |
+---+---+---------------------+
| 1 | 2 | 2009-06-08 00:00:00 |
| 2 | 2 | 2009-06-08 00:00:00 |
+---+---+---------------------+
2 rows in set
mysql> select * from td2 where datediff<>
'2009-06-08';
+---+---+---------------------+
| A | B | datediff            |
+---+---+---------------------+
| 1 | 2 | 2016-09-11 16:34:51 |
| 2 | 2 | 2009-06-09 00:00:00 |
+---+---+---------------------+
2 rows in set
mysql> 

4.课程成绩，小于60不及格，60-80及格，80以上优秀。

结果：

create table course(语文 varchar(10),数学 varchar(10),英语 varchar(10));
insert into course(语文, 数学, 英语) values('67','90','100');
insert into course(语文, 数学, 英语) values('90','40','78');
insert into course(语文, 数学, 英语) values('76','80','78');
insert into course(语文, 数学, 英语) values('67','50','80');
insert into course(语文, 数学, 英语) values('54','98','40');
insert into course(语文, 数学, 英语) values('67','90','50');
insert into course(语文, 数学, 英语) values('100','34','100');

SELECT * FROM world.course;
select (case when 语文>=80 then '优秀' when 语文>=60 and 语文<80 then '及格' when 语文<60 then '不及格' end) as '语文',
(case when 数学>=80 then '优秀' when 数学>=60 and 数学<80 then '及格' when 数学<60 then '不及格' end) as '数学',
(case when 英语>=80 then '优秀' when 英语>=60 and 英语<80 then '及格' when 英语<60 then '不及格' end) as '英语'
from course