Rails中的sql小记

Rails中的Sql小记

关于join

sql中的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

SELECT `companies`.* FROM `companies` INNER JOIN `departments` ON `departments`.`company_id` = `companies`.`id`

rails中的join

• joins

Company.joins(:departments).to_sql
# => SELECT `companies`.* FROM `companies` INNER JOIN `departments` ON `departments`.`company_id` = `companies`.`id`

可以发现,rails中的joins对应sql中的inner join.

• includes

Company.includes(:departments).where(departments: {department_name: '#'}).first
# => SELECT  DISTINCT `companies`.`id`, `companies`.`id` AS alias_0 FROM `companies` LEFT OUTER JOIN `departments` ON `departments`.`company_id` = `companies`.`id` WHERE `departments`.`department_name` = '#'  ORDER BY `companies`.`id` ASC LIMIT 1

可以发现,有一个LEFT OUTER JOIN.是不是和left join很像.

小结

对应的关系

1. joins就是inner join, inner是可选的.
2. left outer join就是left join, outer是可选的.
3. 具体的可查看此链接

其他方法

• 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配合使用,效果更佳.

• boolean显示中文

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

原文链接