MySQL里的SQL技巧

在表首或者表尾多加一列

举一个场景：有时候我们需要将多个表合并到一个表里，然后多加一列来标识数据来源。此时该技巧就非常有用了，如果是在表首加一列：

select '2021' as year,message.* from message;

注意：*前面需要加表名.，否则会有语法错。表尾加一列：

select message.*,'2021' as year from message;

这样插入操作就很容易了：

insert into message_collect(field1, field2, field3, ...) 
select '2021' as year, message_2021.* from message_2021;

SQL的与或非操作

发帖子或发了消息的用户ID：

select user_id from subject union select user_id from message;

发帖子且发了消息的用户：

select user_id from subject intersect select user_id from message;

发帖子但没有发消息的用户：

select user_id from subject except select user_id from message;

一些注意事项：

• 使用Union，重复的数的会去重，如果将union 换成union all，重复的数的会保留
• 当使用集合运算时，每个查询语句字段的个数和数值类型要一致
• 如果有Order 排序，只能使用在最后一个语句
• 使用Except时，前面的表减去后面的表，因此如果顺序不同，导致的结果也会不同

union好像是处理并集的，但是有时候可以用来将多个sql合一块写，这样就不用手动合了。

举个简单例子，统计最近三天的数据量：

select '20210105', count(*) as cnt from data where datetime='20210105'
union
select '20210106', count(*) as cnt from data where datetime='20210106'
union
select '20210107', count(*) as cnt from data where datetime='20210107'

多表连接

inner join: 两边都有才会保留，缩写为join。常用。

left join: 左边表的数据都会保留。常用。

right join: 右边表的数据都会保留。不常用。

full join: 左右两边表的数据都会保留。不常用。

举个复杂点的例子，查询评论表中评论者昵称、被评论者昵称、帖子标题：

select comment.id, subject.title, src_user.nickname, dst_user.nickname 
from comment 
left join user as src_user on comment.owner_user_id=src_user.id 
left join user as dst_user on comment.target_comment_user_id=dst_user.id 
left join subject on comment.target_id=subject.id;

关系对数

有时候我们不关心次序，只关心一个集合存在的次数，比如互关好友对数，此时我们需要使用如下技巧：

select concat(LEAST(1,2), ',', GREATEST(1,2));

具体举例就是：

select
  count(distinct concat(LEAST(target_user_id,owner_user_id), ',', GREATEST(target_user_id,owner_user_id)))
from
  relation;