mysql - SQL 查询 : Separate column where IDs are the same but type is different

Question

基本上我是从一个简单的选择查询中得到的:

SELECT  Site.RefID, SiteName, Dates.Date, Dates.Type
FROM Site, Dates
WHERE Site.RefID = Dates.RefID; 

RefID | SiteName | Date        | Type
1       Sydney       06-12-15   OPENED
1       Sydney       08-12-15   CLOSED
2       Mel          17-12-15   OPENED
2       Mel          19-12-15   CLOSED

但我想将它分开，所以 tge 结果类似于:

RefID | SiteName | DateOPENED     | DateCLOSED
1       Sydney       06-12-15     | 08-12-15

基本上我想比较数据跟踪细节

如果这个问题的结构不是很好，请提前致歉:/我是一个完全的初学者

我在想也许是选择中的选择或可能的情况，但我似乎无法工作

Answer 1

尝试以下方法，使用 case 表达式:

select s.RefID
     , s.Name
     , min(case when d.Type == 'OPENED' then d.Date end) as DateOPENED
     , min(case when d.Type == 'CLOSED' then d.Date end) as DateCLOSED
from Site s 
join Dates d on s.RefID = d.RefID
group by s.RefID, s.Name