在SQL中按多列分组

bd528 发表于 Dev

bd528

我正在尝试通过site.Site_Name来获取每个hive.hiveno，这是max（hiverdg.invdate）。由于site.Site_Name未凝结，因此无法运行下面的代码。如果我将site.Site_Name添加到分组依据，则代码将运行，但输出将重复显示结果，每个站点一次。

select site.Site_Name ,hive.hiveno, max(hiverdg.invdate)
from hiverdg 
        inner join hive
        on        hiveRdg.hive_Link = hive.hive_Link
        inner join Customer
        on        customer.Customer_Link = hive.Customer_Link
        inner join site
        on        site.Customer_Link = customer.Customer_Link        
where 
(hiverdg.xtype = 'N'
and customer.CustomerName = 'Cust1')
or
(hiverdg.xtype = 'A'
and customer.CustomerName = 'Cust1')
group by hive.hiveno

戈登·利诺夫（Gordon Linoff）

使用查询最简单的方法是substring_index()/group_concat()技巧：

select substring_index(group_concat(s.Site_Name order by rdg.invdate desc separator '|'
                                   ), '|', 1
                      ) as SiteName,
       h.hiveno, max(rdg.invdate)
from hiverdg rdg inner join
     hive h
     on rdg.hive_Link = h.hive_Link inner join
     Customer c
     on c.Customer_Link = h.Customer_Link inner join
     site s
     on s.Customer_Link = c.Customer_Link        
where rdg.xtype in ('N', 'A') and c.CustomerName = 'Cust1')
group by h.hiveno;

我还对您的查询进行了以下更改：