从左外连接中选择最上面的一个 [英] Select top one from left outer join

问题描述

伙计们，我有一个查询，其中基本上选择了我们用户使用的最新浏览器.

Guys, I have a query where basically select the latest browser that our user used.

这是我们的(简化的)表结构

here is our (simplified) table structure

HITS_TABLE
----------
USERID
BROWSER
HITSDATE

USER_TABLE
----------
USERID
USERNAME

这是我查询用户使用的最新浏览器的方式

and here is how I query the latest browser that our user used

SELECT U.*, H.BROWSER

FROM USER_TABLE U

CROSS APPLY 
  (SELECT TOP 1 BROWSER 
   FROM HITS_TABLE 
   WHERE HITS_TABLE.USERID = U.USERID
   ORDER BY HITS_TABLE.HITSDATE DESC
  )as H

HITS_TABLE 是几天前刚刚添加的.

The HITS_TABLE is just added several days ago.

因此，该查询只是在我们添加 HITS_TABLE 后访问我们网站的结果用户，并消除了其他人.

So, that query is just resulting users that visited our website after we added the HITS_TABLE, and eliminate the others.

这是示例案例

USER_TABLE
-------------------
USERID     USERNAME
-------------------
1          'Spolski'
2          'Atwoord
3          'Dixon'


HITS_TABLE
------------------------------
USERID     HITSDATE     BROWSER
------------------------------
2          15/8/2009    'Firefox 3.5'
1          16/8/2009    'IE 6'
2          16/8/2009    'Chrome'

这是示例结果

------------------------------
USERID     USERNAME     BROWSER
------------------------------
1          'Spolsky'    'IE 6'
2          'Atwoord'    'Chrome'

但是，我想用未知"浏览器添加其他用户.这是我想要的结果

But, I want to add other users with 'unknown' browser. Here is my desired result

------------------------------
USERID     USERNAME     BROWSER
------------------------------
1          'Spolsky'    'IE 6'
2          'Atwoord'    'Chrome'
3          'Dixon'      'Unknown'

我相信可以通过 LEFT OUTER JOIN 来实现.但我一直有这个:(我不想要这个结果)

I believe it could be achieved by LEFT OUTER JOIN. But I always had this: (I DO NOT want this result)

------------------------------
USERID     USERNAME     BROWSER
------------------------------
1          'Spolsky'    'IE 6'
2          'Atwoord'    'Chrome'
2          'Atwoord'    'Firefox 3.5'
3          'Dixon'      'Unknown'

我希望我的问题很清楚.

I hope my question is clear.

推荐答案

针对 hits_table 使用 userid 上的 group by 可以让您获得每个 userid 的 max() hitsdate.我在下面的代码中将此称为 LATEST HITS.

using a group by on userid against the hits_table allows you to get the max() hitsdate for each userid. I've called this LATEST HITS in the code below.

在 USER TABLE 上选择左连接到 LATEST HITS 允许您为每个用户提取记录.

Selecting on the USER TABLE with a left join to LATEST HITS allows you to pull records for every user.

重新加入 HITS TABLE 然后您就可以提取与该日期关联的浏览器记录，或者为没有记录的用户提取空值.

joining back onto the HITS TABLE then allwos you to pull the browser record associated with that date, or a null for users with no record in there.

select
   user_table.userid,
   user_table.username,
   isnull(hitstable.browser, 'unknown') as browser
from
  user_table
left join
(
  select
    userid,
    max(hitsdate) hitsdate
  from
    hits_table
  group by  
    userid
) latest_hits
on
  user_table.userid = latest_hits.userid    
left join
  hits_table
on hits.table.userid = latest_hits.userid
and hits_table.hitsdate = latest_hits.hitsdate

这篇关于从左外连接中选择最上面的一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！