近两天在看“MYSQL技术内幕”,里面有些不错的例子,我想仅看一下是不够的,动手写一写会加深理解。
1,序号问题
比如有这样一个表:
+-----+
| a |
+-----+
| 1 |
| 2 |
| 3 |
| 100 |
| 101 |
| 103 |
| 104 |
| 105 |
+-----+
要求给其添加一个序号列。
SQL语句可以这样写:
mysql> select a,@a:=@a+1 num from nianxu,(select @a:=0) as b;
+-----+-----+
| a | num |
+-----+-----+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 100 | 4 |
| 101 | 5 |
| 103 | 6 |
| 104 | 7 |
| 105 | 8 |
+-----+-----+
8 rows in set
还有另一种写法也行:
select a,(select count(*) from nianxu as table1 where table1.a<=table2.a) as num from nianxu as table2;
2,在第1个问题的基础上(表不变)找出连续的范围,像这样:
start end
1 3
100 101
103 105
也就是找出各个连续段的最小值与最大值
SQL语句可以这样写:
SELECT MIN(a) as start,MAX(a) as end FROM
(SELECT a,num,a-num as diff FROM
(SELECT a,@a:=@a+1 as num FROM nianxu,(SELECT @a:=0) as a)
as b
) as c
GROUP BY diff;
最后结果是:
+-------+-----+
| start | end |
+-------+-----+
| 1 | 3 |
| 100 | 101 |
| 103 | 105 |
+-------+-----+
3 rows in set
上面的SQL语句可以先看里层后看外层。
先写这几个吧,以后再加几个。