sql临时表 - mysql虚拟表




MySQL如何在使用分组时将空值分配给不匹配的列 (2)

我有以下MovieTheaterTbl表:

Name      Location      Date    TicketRevenue  SnackRevenue  BeverageRevenue
AMC       Alpine St.    8/14        100             80             60
Atlas     Main St.      8/19        300             150            100
Atlas     Lincoln Rd.   8/19        50              50             40 

我想将这些数据插入名为SummaryTbl的表格中,该表格应该显示根据电影院名称分组的收入列的总和。 如果任何其他列对于具有相同名称的行不相同(例如,2个Atlas条目中的位置不同),我希望在该位置显示空值。 这就是我想要的SummaryTbl看起来像:

Name      Location      Date    TicketRevenue  SnackRevenue  BeverageRevenue
AMC       Alpine St.    8/14        100             80             60
Atlas                   8/19        350             200           140

这是我目前使用的代码:

insert into  SummaryTbl (Name,Location,Date,TicketRevenue,SnackRevenue,BeverageRevenue)
select Name,Location,Date,sum(TicketRevenue),sum(SnackRevenue), sum(BeverageRevenue)
from MovieTheaterTbl
group by Name

这将数据分组在一起就好了,但是当单个电影院有多个位置时,它不会在位置字段中插入一个NULL值。 有人可以解释如何做到这一点? 谢谢!


你可以使用联合adn ..有count(*)> 1插入null否则插入位置

  insert into  SummaryTbl (Name,Location,Date,TicketRevenue,SnackRevenue,BeverageRevenue)
  select Name, NULL,Date,sum(TicketRevenue),sum(SnackRevenue), sum(BeverageRevenue)
  from MovieTheaterTbl
  group by Name
  HAVING COUNT(*) >1
  and count(location) >1
  UNION
  select Name, Location,Date,sum(TicketRevenue),sum(SnackRevenue), sum(BeverageRevenue)
  from MovieTheaterTbl
  group by Name
  HAVING COUNT(*) =1
  AND count(location) = 1

尝试这个:

insert into  SummaryTbl (Name,Location,Date,TicketRevenue,SnackRevenue,BeverageRevenue)
select Name, if(count(distinct Location)=1, Location, NULL), Date,sum(TicketRevenue),sum(SnackRevenue), sum(BeverageRevenue)
from MovieTheaterTbl
group by Name




insert-into