Closed xiaohesong closed 6 years ago
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差不多.不过这个是以右表全表为基础进行处理.
left join
inner join ruby的 joins.sql查询中也可以直接使用join,就是inner join
ruby
joins
sql
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类似的.此处查看
left outer 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使用,效果更佳.
group by
boolean
select name, if(status>0,'激活','锁定') AS '操作状态' from users where status is not null;
关于
join
Left join
Right join 和
left join
差不多.不过这个是以右表全表为基础进行处理.inner join
ruby
的joins
.sql
查询中也可以直接使用join
,就是inner join
Inner join 是左右表等价的.
includes
从上面可以发现,
left join
和includes
的left outer join
很像.其实left join
和left outer join
类似于join
和inner join
类似的.此处查看小方法
sum
round
GROUP_CONCAT/CONCAT 返回拼接的字符串. GROUP_CONCAT与
group by
使用,效果更佳.