赵龙 全栈之路

数据库SQL 自学02

| Comments

20-3 分析票种的总金额、20-4 分析票种的报名人数,并根据状态分类,当时是把数据全部捞出然后用 Ruby 进行分类,请改用 SQL 的 GROUP BY 语法来做。

  • 20-3 分析票种的总金额: SELECT t.name, (count(r.id )* t.price) as "Of Amount" FROM tickets t LEFT JOIN registrations r ON t.id = r.ticket_id where r.status = "confirmed" GROUP BY ticket_id having t.name in("Guest", "VIP 第一期","VIP 第二期"); 
  • 20-4 分析票种的报名人数:
    SELECT t.name, count(status) as "报名尚未完成" from tickets t LEFT JOIN registrations r ON r.ticket_id = t.id where r.status = "pending" group by ticket_id having t.name in("Guest", "VIP 第一期","VIP 第二期");

  • SELECT t.name, count(status) as "报名成功" from tickets t LEFT JOIN registrations r ON r.ticket_id = t.id where r.status = "confirmed" group by ticket_id having t.name in("Guest", "VIP 第一期","VIP 第二期");

Comments

comments powered by Disqus