左联接的替代方法 [英] Alternative to Left Join

问题描述

我需要使用MS Query在Excel 2007中显示一组固定管道的月费率，即使管道没有月费率也必须以这种方式显示

I need to display the monthly rates for a fixed set of pipelines in Excel 2007 using MS Query and even if a pipeline has no monthly rate it has to be displayed in this manner

我已经在SQL Server 2008 R2中使用以下代码完成了此操作.

I have done it using the following code in SQL Server 2008 R2.

SELECT P.Name     AS [Pipeline Name], 
       PR.Id, 
       PR.[Name], 
       PH.[Value] AS Rate 
FROM   [GAS].[dbo].[Pipelinerate] PR 
       INNER JOIN Pipeline P 
               ON P.Id = PR.Pipelineid 
       LEFT OUTER JOIN [GAS].[dbo].[Pipelineratehistory] PH 
               ON PH.[Pipelinerateid] = PR.[Id] 
               AND ( PH.[Month] = ?   --Month
                     AND PH.[Year] = ? )  --Year
WHERE  ( PR.[Id] IN ( 197, 198, 1, 2, 3, 5, 67, 68, 23 ) ) 
       AND ISNULL(PH.Deprecated, 'n') <> 'Y' 
ORDER  BY [Pipeline name], 
          PR.[Name] 

当我尝试在Excel 2007中的MS Query中执行此操作时，出现以下错误

When I try to do it in MS Query in Excel 2007 then I get I get the following errors

[Microsoft][ODBC SQL Server Driver] Invalid Parameter Number

[Microsoft][ODBC SQL Server Driver] Invalid Descriptor Index

通过错误和错误发现，如果我从ON子句中删除带有参数的条件，并将其放入WHERE Clause中，如下所示:

I found out by trail and error that If I remove the conditions with the parameters from the ON clause and put it in WHERE Clause as below

SELECT P.Name     AS [Pipeline Name], 
       PR.Id, 
       PR.[Name], 
       PH.[Value] AS Rate 
FROM   [GAS].[dbo].[Pipelinerate] PR 
       INNER JOIN Pipeline P 
               ON P.Id = PR.Pipelineid 
       LEFT OUTER JOIN [GAS].[dbo].[Pipelineratehistory] PH 
               ON PH.[Pipelinerateid] = PR.[Id] 
WHERE  ( PR.[Id] IN ( 197, 198, 1, 2, 3, 5, 67, 68, 23 ) ) 
       AND ( PH.[Month] = ?   --Month
             AND PH.[Year] = ? )  --Year
       AND ISNULL(PH.Deprecated, 'n') <> 'Y' 
ORDER  BY [Pipeline name], 
          PR.[Name] 

该代码在MS Query中有效，并给出以下结果

The code works in MS Query and give the following result

在此输出中，未显示没有月费率的管道.因此，此代码不起作用.因此，在这种情况下，我试图寻找LEFT JOIN的替代方法，以使用MS Query在excel中获得所需的输出.

In this output the pipelines with no rates for the month are not shown. Hence this code doesn't work. Thus I am trying to find alternatives to LEFT JOIN in this case to get the desired output in excel using MS Query.

协会
Pipeline和PipelineRate-可选的一对多关系
PipelineRate和PipelineRateHistory-可选的一对多关系

Associations
Pipeline and PipelineRate - optional one to many relationship
PipelineRate and PipelineRateHistory - optional one to many relationship

有人能建议左联接的替代方法或完成此任务的方法吗?

Can anyone suggest alternatives to left join or a way to accomplish this?

PS:我无法使用存储过程.我知道如何使用VBA做到这一点.我需要使用MS Query来完成此操作

PS: I can't use stored procedures. I know how to do this using VBA. I require to accomplish this using MS Query

推荐答案

根据您的第一个查询，您尝试在左侧联接中添加月份过滤器. 由于这是左侧，请加入您的获取记录.月和年过滤器不是必需的.

As per your first query u tried to put month filter in the left join. Since that is a left join your getting records The month and Year filter is not mandatory.

但是在下一个查询中，您将过滤器放在where条件下，因此查询不会为您返回任何结果.

But in next query u put the filter in where condition so the query is not returning any result for you.

如果必须使用月份和年份过滤器，请将其放置在第二个查询给出正确结果数的位置.

If your month and year filter is mandatory please put it in where and the second query is giving correct number of results.

否则，请使用此查询

SELECT P.Name     AS [Pipeline Name], 
       PR.Id, 
       PR.[Name], 
       PH.[Value] AS Rate 
FROM   [GAS].[dbo].[Pipelinerate] PR 
       INNER JOIN Pipeline P 
               ON P.Id = PR.Pipelineid 
       LEFT OUTER JOIN (select [Pipelinerateid],[Value] 
                       from [GAS].[dbo].[Pipelineratehistory] 
                       where [Month] = ?   --Month
                     AND [Year] = ? --Year
                      ) PH 
               ON PH.[Pipelinerateid] = PR.[Id] 
       WHERE  ( PR.[Id] IN ( 197, 198, 1, 2, 3, 5, 67, 68, 23 ) ) 
               AND ISNULL(PH.Deprecated, 'n') <> 'Y' 
ORDER  BY [Pipeline name], 
          PR.[Name] 

这篇关于左联接的替代方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！