代码之家 › 专栏 › 技术社区 › Wes Price

内联表上的左联接

sql-server-2005 sql

Wes Price · 技术社区 · 15 年前

谷歌在这里没有帮助我,微软也没有在线帮助。

我有作为子查询生成的内联表。(请参阅下面的简化代码,我还有几个内联表。)

现在,这是好的,我有数据。但是,在有些情况下,当没有数据时,我需要返回结果。例如,内联表1返回我的活动客户数。。。如果我指定了一个没有活动客户的范围,则整个查询都不会得到结果。

这是由于我的加入(和IL1.transaction_id=th.transaction_id)

如何左键联接内联表?

我在IL1.TraceActuyId= T.TraceActuyId上尝试了左连接IL1,但它表示该表不存在。

select  SUM(th.total_net_retail_central) as 'Net Purchases  TY', 
            IL1.Active as 'Number of Active Customers TY',
            COUNT(th.transaction_id) as 'Number of Transactions TY'

FROM        

(SELECT transaction_type, COUNT(DISTINCT customer_id) as 'Active' from transaction_header  
where transaction_date BETWEEN @Active and @ToDate group by transaction_type)IL1, 
transaction_header th

INNER JOIN transaction_type tt ON th.transaction_type = tt.transaction_type
WHERE 
th.transaction_date Between @FromDate AND @ToDate

AND         IL1.transaction_type = th.transaction_type

GROUP BY 
            tt.transaction_type_description, IL1.Active

任何帮助都是非常感谢的。

3 回复 | 直到 15 年前

Josh Crozier HBP 10 年前

不应该混合隐式连接和显式连接,可能会得到不一致的结果。坦白地说,你不应该使用隐式连接。

看看这是否适合你:

SELECT  SUM(th.total_net_retail_central) AS 'Net Purchases  TY',  
            COALESCE(IL1.Active, 0) AS 'Number of Active Customers TY', 
            COUNT(th.transaction_id) AS 'Number of Transactions TY' 

FROM     transaction_header th      
INNER JOIN transaction_type tt 
    ON th.transaction_type = tt.transaction_type 
LEFT JOIN (SELECT transaction_type, COUNT(DISTINCT customer_id) AS 'Active' 
            FROM transaction_header   
            WHERE transaction_date BETWEEN @Active and @ToDate 
            GROUP BY transaction_type)IL1
    ON  IL1.transaction_type = th.transaction_type 
WHERE  th.transaction_date BETWEEN @FromDate AND @ToDate 
GROUP BY  tt.transaction_type_description, COALESCE(IL1.Active, 0)

Joe Stefanelli 15 年前

既然您运行的是SQL Server 2005,我将使用 CTE 清理一下。

;with cteIL1 as (
    SELECT transaction_type, COUNT(DISTINCT customer_id) as 'Active' 
        from transaction_header  
        where transaction_date BETWEEN @Active and @ToDate 
        group by transaction_type
)
select  SUM(th.total_net_retail_central) as 'Net Purchases  TY', 
        ac.Active as 'Number of Active Customers TY',
        COUNT(th.transaction_id) as 'Number of Transactions TY'
    FROM transaction_header th
        INNER JOIN transaction_type tt 
            ON th.transaction_type = tt.transaction_type
        LEFT JOIN cteIL1 IL1
            on th.transaction_type = IL1.transaction_type
    WHERE th.transaction_date Between @FromDate AND @ToDate
    GROUP BY tt.transaction_type_description, IL1.Active

编辑 :注释中提到的2000年非CTE版本:

select  SUM(th.total_net_retail_central) as 'Net Purchases  TY', 
        ac.Active as 'Number of Active Customers TY',
        COUNT(th.transaction_id) as 'Number of Transactions TY'
    FROM transaction_header th
        INNER JOIN transaction_type tt 
            ON th.transaction_type = tt.transaction_type
        LEFT JOIN (SELECT transaction_type, COUNT(DISTINCT customer_id) as 'Active' 
                       from transaction_header  
                       where transaction_date BETWEEN @Active and @ToDate 
                       group by transaction_type
                   ) IL1
            on th.transaction_type = IL1.transaction_type
    WHERE th.transaction_date Between @FromDate AND @ToDate
    GROUP BY tt.transaction_type_description, IL1.Active

Harrison 15 年前

select  SUM(th.total_net_retail_central) as 'Net Purchases  TY', 
            IL1.Active as 'Number of Active Customers TY',
            COUNT(th.transaction_id) as 'Number of Transactions TY'
FROM        
(SELECT transaction_type, 
       COUNT(DISTINCT customer_id) as 'Active' 
         from transaction_header  
    where transaction_date BETWEEN @Active and @ToDate 
     group by transaction_type ) IL1
     right join
                transaction_header th
                 on IL1.transaction_type = th.transaction_type
INNER JOIN transaction_type tt 
            ON th.transaction_type = tt.transaction_type
WHERE 
th.transaction_date Between @FromDate AND @ToDate
GROUP BY 
            tt.transaction_type_description, IL1.Active

如果我读对了我相信你需要一个 右连接 在IL1和TH之间

IL1号右连接交易标题关于IL1.transaction_type=th.transaction_type