Rails中的sql小记

Rails中的Sql小记

关于join

sql中的join

  • left join

    1
    2
    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

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

rails中的join

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

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

  • includes
1
2
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. join就是inner join, inner是可选的.
  2. left outer join就是left join, outer是可选的.
  3. 具体的可查看此链接

其他方法

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

    1
    2
    ROUND('123.654',2)
    # 123.654 取小数后两位
  • GROUP_CONCAT/CONCAT
    返回拼接的字符串. GROUP_CONCAT与group by配合使用,效果更佳.

原文链接