SQL例题

例题1:表格格式转换

create table mst_class(class_id varchar(10) primary key,grade varchar(10),rate varchar(10));

insert into mst_class values('abc123','primary','70%'),('abc123','middle','65%'),('abc123','high','72%'),

('hjkk86','primary','69%'),('hjkk86','middle','63%'),('hjkk86','high','74%');

mysql> select * from mst_class;

+----------+---------+------+

| class_id | grade  | rate |

+----------+---------+------+

| abc123  | primary | 70%  |

| abc123  | middle  | 65%  |

| abc123  | high    | 72%  |

| hjkk86  | primary | 69%  |

| hjkk86  | middle  | 63%  |

| hjkk86  | high    | 74%  |

+----------+---------+------+

6 rows in set (0.00 sec)

select class_id,

max(case when grade='primary' then rate else null end) as 'primary',

max(case when grade='middle' then rate else null end) as 'middle',

max(case when grade='high' then rate else null end) as 'high'

from mst_class

group by class_id;

+----------+---------+--------+------+

| class_id | primary | middle | high |

+----------+---------+--------+------+

| abc123  | 70%    | 65%    | 72%  |

| hjkk86  | 69%    | 63%    | 74%  |

+----------+---------+--------+------+

2 rows in set (0.00 sec)

------------------------------------------------------------------------------------------------

例题2：后一天温度比前一天温度高的情况

create table temp(id int primary key,`date` date,temperature int);(1,'2013-04-01',20),

insert into temp values(2,'2013-04-02',25),(3,'2013-04-03',21),(4,'2013-04-04',24);

mysql> select * from temp;

+----+------------+-------------+

| id | date      | temperature |

+----+------------+-------------+

|  1 | 2013-04-01 |          20 |

|  2 | 2013-04-02 |          25 |

|  3 | 2013-04-03 |          21 |

|  4 | 2013-04-04 |          24 |

+----+------------+-------------+

4 rows in set (0.00 sec)

select day2.*

from temp as day1 join temp as day2 on day2.id=day1.id+1 and day2.temperature>day1.temperature;

+----+------------+-------------+

| id | date      | temperature |

+----+------------+-------------+

|  2 | 2013-04-02 |          25 |

|  4 | 2013-04-04 |          24 |

+----+------------+-------------+

2 rows in set (0.00 sec)

------------------------------------------------------------------------------------------------

例题3:排名，按照从大到小的顺序排名

方法：

1.需要列与列之间对比，自己join自己

2，需知道多少人比我大 count去找多少人比我大

3.需要去重 dictinct

4.使用大于等于会更好 过滤时用>=

mysql> create table ranktest(id int primary key,grade int);

Query OK, 0 rows affected (0.02 sec)

mysql> insert into ranktest values(1,100),(2,99),(3,100),(4,80),(5,90),(6,90),(7,70),(8,80);

Query OK, 8 rows affected (0.02 sec)

Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from ranktest;

+----+-------+

| id | grade |

+----+-------+

|  1 |  100 |

|  2 |    99 |

|  3 |  100 |

|  4 |    80 |

|  5 |    90 |

|  6 |    90 |

|  7 |    70 |

|  8 |    80 |

+----+-------+

8 rows in set (0.00 sec)

select a.id,a.grade,count(distinct b.grade)as '排名'

from ranktest a join ranktest b

where b.grade>=a.grade

group by a.id

order by 排名;

| id | grade | 排名  |

+----+-------+--------+

|  1 |  100 |      1 |

|  3 |  100 |      1 |

|  2 |    99 |      2 |

|  5 |    90 |      3 |

|  6 |    90 |      3 |

|  8 |    80 |      4 |

|  4 |    80 |      4 |

|  7 |    70 |      5 |

+----+-------+--------+

8 rows in set (0.00 sec)