[每日见闻-2017-08-02]sql整理

[xiaohesong]

关于join

• Left join

select count(distinct store_name) from scores left join stores on scores.scoreable_id = stores.id and scores.scoreable_type = 'Store' where store.status = 0 and scores.user_type=0;
# 这个是查询 所有带有评分的门店,并且门店是营业状态(0)和评分的类型是用户评分(0)

• Right join
  和left join差不多.不过这个是以右表全表为基础进行处理.

• inner join
  ruby的 joins.sql查询中也可以直接使用join,就是inner join

User.joins(:roles).to_sql
# => "SELECT `users`.* FROM `users` INNER JOIN `users_roles` ON `users_roles`.`user_id` = `users`.`id` INNER JOIN `roles` ON `roles`.`id` = `users_roles`.`role_id`"

Inner join 是左右表等价的.

• includes

A.includes(:bs).where(bs: {name: '#'}).count
# =>
# SELECT COUNT(DISTINCT `bs`.`id`) FROM `as` LEFT OUTER JOIN `bs` ON `bs`.`a_id` = `as`.`id` WHERE `bs`.`name` = '#'

从上面可以发现,left join和includes的left outer join很像.其实left join和left outer join类似于join和inner join类似的.此处查看

小方法

• sum

SUM(CASE WHEN num > 0 THEN 1 else 0 END) AS available_times

• round

ROUND('123.654',2)
# 123.654 取小数后两位

• GROUP_CONCAT/CONCAT
  返回拼接的字符串. GROUP_CONCAT与group by使用,效果更佳.

[xiaohesong]

• boolean转为汉字显示

select name, if(status>0,'激活','锁定') AS '操作状态' from users where status is not null;