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)
