一、统计每个表自增值
根据统计信息用来统计每个表自增字段当前使用量及自增最大值。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT table_schema, TABLE_NAME, COLUMN_NAME, AUTO_INCREMENT, pow(2, CASE data_type WHEN 'tinyint' THEN 7 WHEN 'smallint' THEN 15 WHEN 'mediumint' THEN 23 WHEN 'int' THEN 31 WHEN 'bigint' THEN 63 END+(column_type LIKE '% unsigned'))-1 AS max_int FROM information_schema.tables t JOIN information_schema.columns c USING (table_schema, TABLE_NAME) WHERE c.extra = 'auto_increment' AND t.auto_increment IS NOT NULL; |
效果如下：
|
1 2 3 4 5 6 |
+---------------------+---------------------------------+------------------+---------------------+-----------------------+ | table_schema | table_name | column_name | auto_increment | max_int | +---------------------+---------------------------------+------------------+---------------------+-----------------------+ | mysql | time_zone | Time_zone_id | 1 | 4294967295 | | dcbiz_account | BalanceCredit | id | 5214623 | 2147483647 | -------------------------------------------------------------------------------------------------------------------------- |
二、给查询结果一个序号
首先set一个变量，然后查询时给这个变量赋值并加1就可以了。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> set @rn=0; Query OK, 0 rows affected (0.00 sec) mysql> select @rn:=@rn+1 as rownumber, NAME, AGE from info; +-----------+-------+-----+ | rownumber | NAME | AGE | +-----------+-------+-----+ | 1 | ZHANG | 32 | | 2 | LI | 25 | | 3 | PENG | 23 | +-------------------------+ 8 rows in set (0.01 sec) |
注意变量赋值时“=”跟“:=”的区别，其中“=”是用来设置一个变量，而“:=”是用来赋值一个变量，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select @var; +------+ | @var | +------+ | NULL | +------+ 1 row in set (0.00 sec) mysql> select @var:=1; +---------+ | @var:=1 | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) |
这里，我们需要先用set设置一个变量。其实也可以把上面这种操作变更为一条语句，使用子查询方式处理。
|
1 2 3 4 5 6 7 8 |
mysql> select @rn:=@rn+1 as rownumber, NAME, AGE from (select * from info) as a, (select @rn:=0) as b; +-----------+-------+-----+ | rownumber | NAME | AGE | +-----------+-------+-----+ | 1 | ZHANG | 32 | | 2 | LI | 25 | | 3 | PENG | 23 | +-------------------------+ |
如果需要把这个序号更新到表的某个字段呢？做法其实也一样，比如根据时间降序将序号更新到 seq 字段。
|
1 2 |
mysql> set @rn=0; mysql> update info set seq = @rn:=@rn+1 order by updateTime desc; |
三、利用CASE语句标识字段属性
使用MySQL CASE语句来标识字段属性，如下：
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> select sex,(CASE WHEN sex=1 THEN '男' WHEN sex=0 THEN '女' ELSE '保密' END) as sex_text from user; +------+----------+ | sex | sex_text | +------+----------+ | 0 | 女 | | 1 | 男 | | 2 | 保密 | | 1 | 男 | | 0 | 女 | +------+----------+ 5 rows in set (0.00 sec) |
0标识为女，1标识为男，2标识为保密。
这里需要注意一点，我们这可以拿sex_text字段来进行排序或分组，但是不能拿sex_text字段来做where条件。这是因为SQL执行顺序where在select前面，字段不存在所以无法做条件判断，但是排序或分组在select后面，所以可以使用sex_text字段。
SQL逻辑解析顺序：
|
1 2 3 4 5 6 7 8 9 10 |
(8) SELECT (9) DISTINCT <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <where_condition> (5) GROUP BY <group_by_list> (6) WITH {CUBE|ROLLUP} (7) HAVING <having_condition> (10) ORDER BY <order_by_list> (11) LIMIT <limit_number> |
四、利用CASE语句更新字段
需求，当条件AGE等于22时，extra赋值为100；当AGE=24时，extra赋值为200。
|
1 |
mysql> UPDATE info SET extra = CASE WHEN AGE=22 THEN 100 WHEN AGE=24 THEN 200 END; |
需要注意一点的是，使用这种方式更新时，虽然会简便SQL编写，但会更新所有记录。会把符合条件的对应值更新，而不符合条件的值更新为NULL，所以谨慎使用。如果想把不符合条件的值更新为指定值，可以加ELSE条件。
|
1 |
mysql> UPDATE info SET extra = CASE WHEN AGE=22 THEN 100 WHEN AGE=24 THEN 200 ELSE 300 END; |
五、给查询结果排名
|
1 2 3 4 5 6 7 8 9 |
set @prev_value = NULL; set @rank_count = 0; select score, CASE WHEN @prev_value = score THEN @rank_count WHEN @prev_value := score THEN @rank_count := @rank_count + 1 END AS rank FROM rank ORDER BY score desc; |
使用子查询方式实现。
|
1 2 3 4 5 6 |
select score, CASE WHEN @prev_value = score THEN @rank_count WHEN @prev_value := score THEN @rank_count := @rank_count + 1 END AS rank FROM rank, (select @prev_value := NULL, @rank_count := 0) as t ORDER BY score desc; |
结果如下：
|
1 2 3 4 5 6 7 8 |
+-------+------+ | score | rank | +-------+------+ | 40 | 1 | | 30 | 2 | | 20 | 3 | | 20 | 3 | +-------+------+ |
六、根据重复行合并多行
测试数据如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select * from t1; +------+------+ | a | b | +------+------+ | 1 | 2 | | 1 | 4 | | 2 | 1 | | 2 | 2 | | 3 | 3 | | 5 | 4 | +------+------+ 6 rows in set (0.00 sec) |
实现消除重复字段a，且合并字段b的结果集。利用group_concat函数处理，如下SQL：
|
1 2 3 4 5 6 7 8 9 10 |
mysql> select a, group_concat(b order by b separator ",") as b from t1 group by a; +------+------+ | a | b | +------+------+ | 1 | 2,4 | | 2 | 1,2 | | 3 | 3 | | 5 | 4 | +------+------+ 4 rows in set (0.00 sec) |
七、合并结果集
先把测试环境给出来，下面再说需求。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> select * from a; +------+------+ | id | name | +------+------+ | 1 | a | | 2 | b | +------+------+ 2 rows in set (0.00 sec) mysql> select * from b; +------+------+ | id | name | +------+------+ | 1 | a | | 3 | c | +------+------+ 2 rows in set (0.00 sec) |
就是把两张表结果集合并到一起， 并且id字段相同的需要合并成一行。如果直接使用union方法处理如下：
|
1 2 3 4 5 6 7 8 9 10 |
mysql> select id,name,'name' from a union select id,'name',name from b; +------+------+------+ | id | name | name | +------+------+------+ | 1 | a | name | | 2 | b | name | | 1 | name | a | | 3 | name | c | +------+------+------+ 4 rows in set (0.00 sec) |
显然无法满足我们id字段相同的合并到一起的需求。一个朋友给了下面这种处理方式：
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT a.id, a.`name`, b.name FROM a a LEFT JOIN b b ON a.id = b.id UNION SELECT b.id, a.name, b.`name` FROM a a RIGHT JOIN b b ON a.id = b.id; |
得到的结果集如下：
|
1 2 3 4 5 6 7 |
+------+------+------+ | id | name | name | +------+------+------+ | 1 | a | a | | 2 | b | NULL | | 3 | NULL | c | +------+------+------+ |
完美解决了我们的需求。
八、分组统计
在一个MySQL群里看见有网友发出这样的一个查询请求，表结构及数据如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from ab; +---------------------+--------+ | ct | status | +---------------------+--------+ | 2017-05-09 00:00:00 | 胜 | | 2017-05-09 00:00:00 | 胜 | | 2017-05-09 00:00:00 | 负 | | 2017-05-09 00:00:00 | 负 | | 2017-05-10 00:00:00 | 负 | | 2017-05-10 00:00:00 | 负 | | 2017-05-10 00:00:00 | 胜 | +---------------------+--------+ 7 rows in set (0.00 sec) |
要求得到结果如下：
|
1 2 3 4 5 6 7 |
+---------------------+------+------+ | ct | 胜 | 负 | +---------------------+------+------+ | 2017-05-09 00:00:00 | 2 | 2 | | 2017-05-10 00:00:00 | 1 | 2 | +---------------------+------+------+ 2 rows in set (0.00 sec) |
这道题一看就是分组统计类的，但不是简单的sum … group by就行，因为你要判断一下那些记录是“胜”，那些记录是“负”，要分开统计。所以，直接使用case … group by即可。
|
1 2 3 4 5 6 7 8 |
SELECT ct, sum((CASE WHEN status='胜' THEN 1 END)) AS '胜', sum((CASE WHEN status='负' THEN 1 END)) AS '负' FROM ab GROUP BY ct; |
九、一个有意思的SQL查询
起因这是面试题，感觉挺有意思的，就刷了一下（写完发现涉及的技巧还是挺多的）。表结构（夺冠球队名称及夺冠年份）及表记录如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
create table nba(name varchar(10), year int) charset utf8; insert into nba select "活塞",1990; insert into nba select "公牛",1991; insert into nba select "公牛",1992; insert into nba select "公牛",1993; insert into nba select "火箭",1994; insert into nba select "火箭",1995; insert into nba select "公牛",1996; insert into nba select "公牛",1997; insert into nba select "公牛",1998; insert into nba select "马刺",1999; insert into nba select "湖人",2000; insert into nba select "湖人",2001; insert into nba select "湖人",2002; insert into nba select "马刺",2003; insert into nba select "活塞",2004; insert into nba select "马刺",2005; insert into nba select "火箭",2006; insert into nba select "马刺",2007; insert into nba select "凯尔",2008; insert into nba select "湖人",2009; insert into nba select "湖人",2010; |
写出一条 SQL 语句，查询出在此期间连续获得冠军的有哪些？其连续的年份的起止时间是多少？结果如下：
|
1 2 3 4 5 6 7 8 9 |
+--------+-----------+---------+ | name | begintime | endtime | +--------+-----------+---------+ | 公牛 | 1991 | 1993 | | 火箭 | 1994 | 1995 | | 公牛 | 1996 | 1998 | | 湖人 | 2000 | 2002 | | 湖人 | 2009 | 2010 | +--------+-----------+---------+ |
咋一看确实挺有难度的，小步慢跑，拆分实现。
1. 首先查询出连续两年获奖的球队
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> select a.name, a.year from nba a join nba b on a.name = b.name where a.year = b.year+1; +--------+------+ | name | year | +--------+------+ | 公牛 | 1992 | | 公牛 | 1993 | | 火箭 | 1995 | | 公牛 | 1997 | | 公牛 | 1998 | | 湖人 | 2001 | | 湖人 | 2002 | | 湖人 | 2010 | +--------+------+ 8 rows in set (0.01 sec) |
通过inner join得到了连续两年获奖的球队，下面再要解决的一个问题就是把连续获奖的球队进行分组，这里当然不能直接group by name，不然就全部合并了，达不到我们上面的需求。所以只能从year下手来进行分组，可以通过增加两个虚拟列，一个自增行号，一个夺冠伪起始年份（利用夺冠伪起始年份进行分组）。
2. 得到可进行分组的列
|
1 2 3 4 5 6 7 8 9 10 |
SELECT c.name, c.year, @rn:=@rn+1 AS rn, c.year - @rn AS BEGIN FROM (SELECT a.name, a.year FROM nba a JOIN nba b ON a.name = b.name WHERE a.year = b.year+1) c, (SELECT @rn:=0) AS d; |
得到的结果如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
+--------+------+------+-------+ | name | year | rn | begin | +--------+------+------+-------+ | 公牛 | 1992 | 1 | 1991 | | 公牛 | 1993 | 2 | 1991 | | 火箭 | 1995 | 3 | 1992 | | 公牛 | 1997 | 4 | 1993 | | 公牛 | 1998 | 5 | 1993 | | 湖人 | 2001 | 6 | 1995 | | 湖人 | 2002 | 7 | 1995 | | 湖人 | 2010 | 8 | 2002 | +--------+------+------+-------+ 8 rows in set (0.02 sec) |
在上面的SQL中，我们利用“c.year – @rn”就可以把相同连续年份夺冠的球队分到一组。
3. 通过子查询做分组处理
再把上面这个SQL进行分组处理（根据begin字段），然后使用min(year) – 1和max(year)得到球队夺冠起始时间和结束时间。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT max(e.name) AS name, min(e.year - 1) AS begintime, max(e.year) AS endtime FROM (SELECT c.name, c.year, @rn:=@rn+1 AS rn, c.year - @rn AS BEGIN FROM (SELECT a.name, a.year FROM nba a JOIN nba b ON a.name = b.name WHERE a.year = b.year+1) c, (SELECT @rn:=0) AS d) e GROUP BY e.begin; |
得到结果如下：
|
1 2 3 4 5 6 7 8 9 |
+--------+-----------+---------+ | name | begintime | endtime | +--------+-----------+---------+ | 公牛 | 1991 | 1993 | | 火箭 | 1994 | 1995 | | 公牛 | 1996 | 1998 | | 湖人 | 2000 | 2002 | | 湖人 | 2009 | 2010 | +--------+-----------+---------+ |
现在就得到了我们想要的结果，是不是还蛮多技巧的。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select * from ( SELECT order_id,create_time,process_type, IF(@order_id <> order_id,@rank := 1,IF(@create_time = create_time,@rank:=@rank,@rank:=@rank+1)) AS rank, @order_id := order_id AS v_order_id, @create_time := create_time AS v_create_time FROM (SELECT @order_id := 0,@rank := 0,@create_time := '') a, fbs_order_history e where e.order_id in (203652743) ORDER BY order_id,create_time desc ) ee where rank = 1; |
10. 行转列
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE tb_score( id INT(11) NOT NULL auto_increment, userid VARCHAR(20) NOT NULL COMMENT '用户id', subject VARCHAR(20) COMMENT '科目', score DOUBLE COMMENT '成绩', PRIMARY KEY(id) )ENGINE = INNODB DEFAULT CHARSET = utf8; INSERT INTO tb_score(userid,subject,score) VALUES ('001','语文',90); INSERT INTO tb_score(userid,subject,score) VALUES ('001','数学',92); INSERT INTO tb_score(userid,subject,score) VALUES ('001','英语',80); INSERT INTO tb_score(userid,subject,score) VALUES ('002','语文',88); INSERT INTO tb_score(userid,subject,score) VALUES ('002','数学',90); INSERT INTO tb_score(userid,subject,score) VALUES ('002','英语',75.5); INSERT INTO tb_score(userid,subject,score) VALUES ('003','语文',70); INSERT INTO tb_score(userid,subject,score) VALUES ('003','数学',85); INSERT INTO tb_score(userid,subject,score) VALUES ('003','英语',90); INSERT INTO tb_score(userid,subject,score) VALUES ('003','政治',82); |
行转列之后
|
1 2 3 4 5 6 7 |
SELECT userid, SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文', SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学', SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) as '英语', SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) as '政治' FROM tb_score GROUP BY userid |
可以看出，这里行转列是将原来的 subject 字段的多行内容选出来，作为结果集中的不同列，并根据 userid 进行分组显示对应的 score。
也可以使用 IF() 进行行转列
|
1 2 3 4 5 6 7 |
SELECT userid, SUM(IF(`subject`='语文',score,0)) as '语文', SUM(IF(`subject`='数学',score,0)) as '数学', SUM(IF(`subject`='英语',score,0)) as '英语', SUM(IF(`subject`='政治',score,0)) as '政治' FROM tb_score GROUP BY userid |
如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。
