sql语句使用技巧

查询在table1中但不在 table2中的行

方法1：select a.id from a left join b on a.id=b.id where b.id is null

方法2：select * from b where (select count(1) as num from a where a.id = b.id) = 0

sql先排序后分组的使用方法

  select * from (select  * from user order by time)as t group by t.name

统计数据表每天生成的数据条数

select count(*) as num from logs group by day (time) order by time

带有in的between操作符实现(下面的 SQL 语句选取alexa介于 1 和 20 之间但 country 不为 USA 和 IND 的所有行)

select * from websites where(alexa between 1 and 20) and not country in('USA','IND')

按父级的子集数量为父级排序

select t1.* from contry t1 order by(select count(*) from town t2 where t1.cid = t2.cid) desc

当一个字段为某个值时则更新这个字段否则不更新（在hero表中id为10的行如果name字段为空则将其更新为“赤发鬼刘唐“）

主要涉及mysql case when then else end的使用详情请参考：https://blog.youkuaiyun.com/qq_32439101/article/details/80904545

update hero set nickname = (case when nickname='' then '赤发鬼刘唐' else nickname end) where id=1

显示文章的标题,作者和文章最后评论时间

 select a.title,a.author, (select max(create_at) from comment where pid=a.id) reply from articles a where a.id = 10;

去重后统计记录的数量(日志表中按用户ID去重然后统计记录数量)

SELECT count(distinct uid) as numrows FROM `logs` WHERE `uid` != ''

通过case when then 修改字段的值

SELECT id,game_id,
CASE question_id 
WHEN 1 THEN '储值' 
WHEN 2 THEN 'bug' 
WHEN 3 THEN '攻略' 
WHEN 4 THEN '活动' ELSE '其他' END 
FROM collect

查询id最大的一件商品(使用where子查询实现)

SELECT goods_id,goods_name,shop_price FROM goods WHERE goods_id = (SELECT MAX(goods_id) FROM goods)

查询每个类别下id最大的商品(使用where子查询实现)

 SELECT goods_id,goods_name,cat_id,shop_price FROM goods WHERE goods_id IN 
(SELECT MAX(goods_id) FROM goods GROUP BY cat_id);

from型子查询：把内层的查询结果当成临时表，供外层sql再次查询。查询结果集可以当成表看待。临时表要使用一个别名。

2.1 查询每个类别下id最大的商品(使用from型子查询)
SELECT goods_id,goods_name,cat_id,shop_price FROM
(SELECT goods_id,goods_name,cat_id,shop_price FROM goods ORDER BY cat_id ASC,goods_id DESC) 
AS tmp GROUP BY cat_id