查询未显示0值mysql

Question

在我的查询中，我列出了不同客户的所有电影票销售和电影票销售。我遇到的问题是所有的“0”门票销售，所以那些没有购买电影票或电影票的用户不会出现。

这是一张视觉方面的图片：table

我认为我需要做一个联盟来返回那些没有购买任何门票的用户。我就是想不通。

提前谢谢。

到目前为止，我的代码如下：

select customer.hippcode, customer.LastName, customer.Firstname, customer.Email,
count(ticketdetails.eventtype) as 'Theater Tickets',
0 as 'Movie Tickets'
from customer
inner join ticketdetails on ticketdetails.hippcode = customer.hippcode
where ticketdetails.hippcode is not null
and ticketdetails.eventType ='T'
Group by Customer.hippcode
union 
select customer.hippcode, customer.LastName, customer.Firstname, customer.Email,
0 as 'Theater Tickets', count(ticketdetails.eventtype) as 'Movie Tickets'
from customer
inner join ticketdetails on ticketdetails.hippcode = customer.hippcode
where ticketdetails.hippcode is not null
and ticketdetails.eventType ='M'
Group by Customer.hippcode
order by `theater tickets` + `movie tickets` desc;
select 
    customer.hippcode, customer.LastName, customer.Firstname, customer.Email,
    sum(case when ticketdetails.eventtype = 'T' then 1 else 0 end) as TheaterTickets,
    sum(case when ticketdetails.eventtype = 'M' then 1 else 0 end) as MovieTickets
from customer
inner join ticketdetails on ticketdetails.hippcode = customer.hippcode
where ticketdetails.hippcode is not null
and ticketdetails.eventType in ('T', 'M')
Group by customer.hippcode, customer.LastName, customer.Firstname, customer.Email
Order by 'TheaterTickets' + 'MovieTickets' desc

Answer 1

只有在两个表上都有记录时，inner join =>才会带上行。

我认为您应该在选择主表时使用LEFT JOIN

http://dev.mysql.com/doc/refman/5.7/en/join.html和http://dev.mysql.com/doc/refman/5.7/en/left-join-optimization.html

Answer 2

我认为最后一个查询是您唯一想要的。left join是合适的，但您需要注意where子句：

select c.hippcode, c.LastName, c.Firstname, c.Email,
       sum(td.eventtype) as TheaterTickets,
       sum(td.eventtype) as MovieTickets
from customer c left join
     ticketdetails td
    on td.hippcode = c.hippcode and
       td.eventType in ('T', 'M')
Group by c.hippcode, c.LastName, c.Firstname, c.Email
Order by count(t.hippcode) desc;

备注：

• 表别名使查询更易于阅读，并且column).
• case on on go在on子句中，而不是where子句中。
• on td.hippcode is not null条件是不必要的，因为NULL在join中不匹配(注意:您可能希望检查客户column).
• case是进行条件求和的标准方法(因此是正确的)。然而，MySQL提供了一种更简单直观的语法：
• 你的order by什么也不做，因为它添加了两个字符串(因此等同于order by 0。永远不要对列名使用单引号，这样就不会有这样的问题了。