为什么我的左外连接不起作用？ [英] Why isn't my left outer join working?

问题描述

这很奇怪。我有一些CTE，它带有row_number的结果列表。 1是最近的，2是最近的第二个等等。



我从这个表中选择结果然后再次将它作为带有左外连接的第二个表加入。我有一个Where子句，所以第一个选择row = 1，第二个行= 2.



目前没有结果，其中row = 2.我希望这些列为空



当我有第一张桌子时，我得到66张结果

当我加入第二张桌子时，我没有！



我做错了什么？



这里是代码（修剪到基本部分）

Hi,

This is odd. I have a number of CTE's which lead up to a list of results with a row_number. 1 is most recent, 2 is second most recent, etc.

I select the results from this table then join it again as a second table with a left outer join. I have a Where clause for each so the first selects row=1 and the second row = 2.

There are currently no results where row = 2. I expect these columns to be null

When I have just the first table, I get 66 results
When I join the second table, I get none!

What am I doing wrong?

here is the code (trimmed to the essential parts)

	with daily_results as(--Gets one result a day so averages later are not oddly waited when two runs occurred in a single day
		select
		   mr.[moz_result_id] as id,
		   mr.[moz_result_url_id] as url_id,
		   convert(datetime,convert(date, mr.[moz_result_datetime])) as [datetime],
		   AVG(cast(mr.[moz_result_page_authority] as float)) as page_authority,
		   AVG(cast(mr.[moz_result_domain_authority] as float)) as domain_authority,
		   AVG(cast(mr.[moz_result_moz_trust] as float)) as trust,
		   row_number() over (partition by mr.[moz_result_url_id] order by convert(datetime,convert(date, mr.[moz_result_datetime])) desc) as num
		from dbo.moz_results mr
		group by 
		   mr.[moz_result_id] ,
		   mr.[moz_result_url_id] ,
		   convert(datetime,convert(date, mr.[moz_result_datetime])) 
	)
, moz as (
    select
       mr.[id]
      ,mr.[url_id]
      ,mr.[datetime]
      ,mr.[page_authority]
      ,mr.[domain_authority]
      ,mr.[trust]
      ,row_number() over (partition by mr.[url_id] order by mr.[datetime] desc) as num
    from daily_results mr
) -- adds the row_number as num
	select
	   mr.[id]
      ,mr.[url_id]
      ,mr.[datetime]
      ,mr.[page_authority]
      ,mr.[domain_authority]
      ,mr.[trust]
      ,prev.[page_authority] as prev_page_authority
      ,prev.[domain_authority] as prev_domain_authority
      ,prev.[trust] as prev_trust
   from moz mr
   left outer join moz prev on mr.url_id = prev.url_id
   where 
      mr.num = 1 
   and prev.num = 2 -- change this to 1 and all results return

推荐答案

您所做的基本上是url_id组内的交叉连接：mr x上一个。您需要限制连接条件以获得正确的结果：

What you do there is basically cross join within url_id group: mr x prev. You need to restrict the join condition to get the correct result:

left outer join moz prev on mr.url_id = prev.url_id and prev.num = 2
where mr.num = 1 

这篇关于为什么我的左外连接不起作用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！