SQL查询：单独的列，其中ID相同但类型不同

Basically I get this from a simple select query:

基本上我从一个简单的选择查询得到这个：

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

But I want to seperate it so tge result is similar to this:

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

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

Basically I want to compare the data tracking details

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

Apologies in advance if this question isn't structured very well :/ I'm a complete beginner

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

I was thinking maybe a select within a select or possible case when's, but I can't seem to get either working

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

4 个解决方案

#1

Try the following approach, using case expression:

使用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

#2

You can use conditional aggregation to get expected result:

您可以使用条件聚合来获得预期结果：

SELECT  Site.RefID, SiteName, 
        MIN(CASE WHEN Dates.Type = 'OPENED' THEN Dates.Date END) DateOPENED,
        MAX(CASE WHEN Dates.Type = 'CLOSED' THEN Dates.Date END) DateCLOSED
FROM Site
INNER JOIN Dates ON Site.RefID = Dates.RefID
GROUP BY Site.RefID, SiteName

Also, it is always preferable to use explicit instead of implicit join syntax.

此外，始终最好使用显式连接语法而不是隐式连接语法。

#3

Add a GROUP BY to your current query, use MIN for opening date, and MAX for en closing date.

将GROUP BY添加到当前查询中，使用MIN表示打开日期，使用MAX表示结束日期。

SELECT  Site.RefID, SiteName, MIN(Dates.Date) as DateOPENED, MIN(Dates.Date) as DateCLOSED
FROM Site
  JOIN Dates ON Site.RefID = Dates.RefID
group by fID, SiteName

Alternatively, JOIN once for opening and once for closing:

或者，JOIN一次打开，一次关闭：

SELECT  Site.RefID, SiteName, do.Date as DateOPENED, dc.Date as DateCLOSED
FROM Site
  LEFT JOIN (select Refid, Date from Dates where Type = 'OPENED') do ON Site.RefID = do.RefID
  LEFT JOIN (select Refid, Date from Dates where Type = 'CLOSED') dc ON Site.RefID = dc.RefID

#4

SELECT A.RefId, A.SiteName, A.Date DateOpened, B.Date DateClosed
FROM #tbl A JOIN #tbl B
ON A.RefId = B.RefID
AND A.Type = 'OPENED'
AND B.Type = 'CLOSED'

For the sake of simplicity, have replaced the query with #tbl(you can deal with it howsoever you'd like to).

为了简单起见，已经用#tbl替换了查询（无论你想做什么，你都可以处理它）。

#1