排除特定列中的多个结果（SQL JOIN）

问题描述：

我有几张合同和财务信息表，并且查询的工作原理与我需要的完全一致 - 在某一点上。它看起来或多或少这样的：

SELECT /some columns/ from CONTRACTS

链接3 extra tables与INNER JOIN添加之类的东西的部门名称，产品信息等，这一切工作，但他们都有一个simplish比一的关系（关系到一个合同单个部门Department表格，相应表格中的一个产品信息条目等）。

现在，这是我的挑战：

我还需要补充合同发票信息做这样的事情：

inner join INVOICES on CONTRACTS.contnoC = INVOICES.contnoI

（和选择也挂Contract number，虽然这部分是可选的Invoice number）

我面临的问题是，与其他表格不同的是，当连接表格时，总是存在一对一的关系，INVOICES表格可以有多个（或非电子邮件）与单个合同编号对应的条目。结果是，我将得到单个合同号的多个查询结果（提供不同的发票号），不必要地拥挤查询结果。

基本上我正在寻找将INVOICES表添加到查询中，以确定INVOICES表（合同是否已开具发票）中是否存在合同号。发票号码本身可以提供（它与INNER JOIN），但它不是关键，只要它以某种方式标记。 Invoice number字段在INNER JOIN函数的结果中保持空白，这也是必需的（即，即使未在INVOICES表中找到匹配，也要显示该行）。

SELECT DISTINCT看起来做什么，我需要，但我似乎要面对，我需要征收仅列代表合同编号DISTINCT标准的问题，而不是任何其他列（可以有相同的价值观呈现，但所有这些应被呈现）。

不幸的是我不完全知道我使用的是什么数据库系统。

你好，告诉我们你尝试过什么，请 –

如果你内加入像其他表的发票，但不把从发票表中的任何列到列的列表，并使用DISTINCT你还是应该让你的1对1其他表格的结果。除此之外，您可以使用IN或EXISTS代替where子句并在那里引用您的发票表。这个问题应该给你很多方法来解决这个问题。 http://*.com/questions/173041/not-in-vs-not-exists – Matt

它会更好，如果你显示你的整个查询和你的表结构... – barudo

答

您可以使用 SELECT ...，发票= 'YES' ...其中存在...... 工会 SELECT ...，发票= 'NO' ......哪里都不存在...

，或者您可以使用列，如“发票”使用子查询到发票来设置它取决于你是否得到一击或没有价值

答

我会做这种方式：

with mainquery as(
<<here goes you main query>> 
), 

invoices_rn as( 
select *, 
ROW_NUMBER() OVER (PARTITION BY contnoI order by 
<<some column to decide which invoice you want to take eg. date>>) as rn 
) 

invoices as (
select * from invoices_rn where rn = 1 
) 

select * from mainquery 
left join invoices i on contnoC = i.contnoI

这使你有能力将所有的发票细节都提供给y我们的查询，也让您完全控制您想要在主要查询中看到的发票。请阅读有关CTE的更多信息;它们非常方便，比嵌套选择更容易理解/读取。

我仍然不知道你使用的是什么数据库。如果ROW_NUMBER不可用，我会找出别的东西:)

还与LEFT JOIN，你应该使用COALESCE功能，例如：

COALESCE(i.invoice_number,'0')

当然，这给你一些更多的可能性，你可以为例如，在你的主要选择Do：

CASE WHEN i.invoicenumber is null then 'NOT INVOICED' 
else 'INVOICED' 
END as isInvoiced

如果OP想要除了其他信息之外还要返回特定的发票，这是一种很好的技术。支持公用表表达式和ROW_NUMBER（）窗口函数的RDBM，实际上并不需要您的发票CTE，您可以直接连接到invoices_rn，然后在连接的ON子句中添加rn = 1。 – Matt

你说得对。但我总是在2个CTE中做。只是我的习惯。 – cybernetic87

我也认为，对于一个begginer来说，它看起来更清楚，一步一步做。我是专业的数据仓库程序员，我仍然用2个CTE来做，我发现它更容易阅读和理解。我已经成为CTE的怪胎。我的大多数vievs /程序都有很多CTE。 – cybernetic87

答

好像问题仍然得到一些关注，并努力在这里提供一些解释的一些技巧。

如果您只是想要从1到1表中的任何细节的合同，你可以做类似于你所描述的。关键是不在列列表中包含Invoices表中的任何列。

SELECT 
    DISTINCT Contract, Department, ProductId .....(nothing from Invoices Table!!!) 
FROM 
    Contracts c 
    INNER JOIN Departments D 
    ON c.departmentId = d.Department 
    INNER JOIN Product p 
    ON c.ProductId = p.ProductId 
    INNER JOIN Invoices i 
    ON c.contnoC = i.contnoI

也许有点清洁剂将在使用或EXISTS像这样：

SELECT 
    Contract, Department, ProductId .....(nothing from Invoices Table!!!) 
FROM 
    Contracts c 
    INNER JOIN Departments D 
    ON c.departmentId = d.Department 
    INNER JOIN Product p 
    ON c.ProductId = p.ProductId 
WHERE 
    EXISTS (SELECT 1 FROM Invoices i WHERE i.contnoI = c.contnoC) 

SELECT 
    Contract, Department, ProductId .....(nothing from Invoices Table!!!) 
FROM 
    Contracts c 
    INNER JOIN Departments D 
    ON c.departmentId = d.Department 
    INNER JOIN Product p 
    ON c.ProductId = p.ProductId 
WHERE 
    contnoC IN (SELECT contnoI FROM Invoices)

不要在使用，如果SELECT ...列表可以返回NULL！

如果你真的希望所有的合同，只是知道，如果合同已开发票，你可以用聚集和case表达式：

SELECT 
    Contract, Department, ProductId, CASE WHEN COUNT(i.contnoI) = 0 THEN 0 ELSE 1 END as Invoiced 
FROM 
    Contracts c 
    INNER JOIN Departments D 
    ON c.departmentId = d.Department 
    INNER JOIN Product p 
    ON c.ProductId = p.ProductId 
    LEFT JOIN Invoices i 
    ON c.contnoC = i.contnoI 
GROUP BY 
    Contract, Department, ProductId

然后，如果你真的想返回有关特定发票的细节，你可以使用类似于cybercentic87的技术，如果您的RDBMS支持，或者您可以根据您的系统使用TOP或LIMIT的计算列。

SELECT 
    Contract, Department, ProductId, (SELECT TOP 1 InvoiceNo FROM invoices i WHERE c.contnoC = i.contnoI ORDER BY CreateDate DESC) as LastestInvoiceNo 
FROM 
    Contracts c 
    INNER JOIN Departments D 
    ON c.departmentId = d.Department 
    INNER JOIN Product p 
    ON c.ProductId = p.ProductId 
GROUP BY 
    Contract, Department, ProductId

排除特定列中的多个结果（SQL JOIN）

相关推荐