代码之家 › 专栏 › 技术社区 › Abe Miessler

有趣的左连接查询

sql-server-2008 tsql sql-server sql

Abe Miessler · 技术社区 · 14 年前

至少对我来说很有趣。。。

假设我有两张桌子:

myLookUpTable公司:

lookupId  | Name
--------    -----
1           Red
2           Green
3           Blue

和信息表:

infoId  lookupId  Amount  ParentId
------  --------  ------  --------
1       1         2       332
2       3         14      332

如何编写一个返回 myLookUpTable

查询parentId 221将为 Name Amount :

Name  Amount
----  ------
Red   
Green
Blue

查询parentId 332将为和 :

Name  Amount
----  ------
Red   2
Green 
Blue  14

我试过十种左右的左连接,但都没有成功。以下是我的最新消息:

SELECT mlut.Name, it.Amount
FROM   myLookUpTable as mlut
LEFT JOIN InfoTable as it
ON     mlut.lookupId = it.lookUpId OR it.ParentId is null
where  it.ParentId = 332

6 回复 | 直到 9 年前

Dave Barker 14 年前

我想这会满足你的要求。

SELECT mlut.Name, it.Amount 
  FROM myLookUpTable as mlut 
  LEFT JOIN InfoTable as it 
    ON mlut.lookupId = it.lookUpId 
   AND it.ParentId = 332

下面是SQL Server 2005测试结果

CREATE TABLE [dbo].[myLookUpTable](
    [lookupId] [int] NOT NULL,
    [Name] [varchar](10) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[InfoTable](
    [infoId] [int] NOT NULL,
    [lookupId] [int] NOT NULL,
    [Amount] [int] NOT NULL,
    [ParentId] [int] NOT NULL
) ON [PRIMARY]

INSERT INTO myLookUpTable Values (1,'Red')
INSERT INTO myLookUpTable Values (2,'Green')
INSERT INTO myLookUpTable Values (3,'Blue')

INSERT INTO infoTable Values (1,1,2,332)
INSERT INTO infoTable Values (2,3,14,332)

SELECT mlut.Name, it.Amount  
  FROM myLookUpTable as mlut  
  LEFT JOIN InfoTable as it  
    ON mlut.lookupId = it.lookUpId  
   AND it.ParentId = 221

Red   NULL
Green NULL
Blue  NULL

SELECT mlut.Name, it.Amount  
  FROM myLookUpTable as mlut  
  LEFT JOIN InfoTable as it  
    ON mlut.lookupId = it.lookUpId  
   AND it.ParentId = 332

Red   2
Green NULL
Blue  14

Fyodor Soikin 14 年前

这个 OR it.ParentId is null

否则-看起来不错。如果你有一些特别的问题,请描述一下。

RMorrisey 14 年前

问题是这条线:

where  it.ParentId = 332

使用此where条件时,将删除所有没有ParentId的行,例如左侧表中没有连接行的行。用途:

where it.ParentId IS NULL or it.ParentId = 332

Community CDub 8 年前

而不是

where  it.ParentId = 332

你需要做什么

AND it.ParentId = 332

也要摆脱 OR it.ParentId is null

MySQL - How to get NULL if equality does not exist

LesterDove 14 年前

ParentID的WHERE子句正在撤消左联接所有空查找值的操作。可以将该约束移动到左联接条件中:

SELECT mlut.Name, it.Amount
FROM   myLookUpTable as mlut
LEFT JOIN InfoTable as it
ON     mlut.lookupId = it.lookUpId AND it.ParentId = 332

Hrvoje Piasevoli 14 年前

如果总是需要返回与myLookUpTable中的行数匹配的确切行数,并且(lookupid,ParentId)在InfoTable中不是唯一的,则可能需要使用以下选项之一:

-- sample contents - note that there are multiple amounts for lookupid, parentid (3,332)
INSERT INTO infoTable Values (1,1,2,332)
INSERT INTO infoTable Values (2,3,14,332)
INSERT INTO infoTable Values (3,3,24,332)
INSERT INTO infoTable Values (4,3,34,0)
INSERT INTO infoTable Values (5,3,44,332)

 -- option 1
    SELECT mlut.Name, it.Amount  
    FROM myLookUpTable as mlut  
        LEFT JOIN 
        (
            SELECT lookUpId, SUM(Amount) as Amount
            FROM InfoTable
            WHERE ParentId = 332
            GROUP BY lookupId
        ) as it  
        ON mlut.lookupId = it.lookUpId  

    -- option 2
    SELECT mlut.Name, it.Amount  
    FROM myLookUpTable as mlut  
        LEFT JOIN 
        (
            SELECT lookUpId, ParentId, SUM(Amount) as Amount
            FROM InfoTable
            GROUP BY lookupId, ParentId 
        ) as it  
        ON mlut.lookupId = it.lookUpId  
            AND it.ParentId = 332

    -- option 2 using CTE
    ;WITH it AS (
        SELECT lookUpId, ParentId, SUM(Amount) as Amount
        FROM InfoTable
        GROUP BY lookupId, ParentId 
    )
    SELECT mlut.Name, it.Amount  
    FROM myLookUpTable as mlut  
        LEFT JOIN it 
        ON mlut.lookupId = it.lookUpId  
        AND it.ParentId = 332

/*
Name       Amount
---------- -----------
Red        2
Green      NULL
Blue       82
*/