从SQL Server中删除结果中的行（多个连接） [英] Remove rows from results in SQL Server (mutliple joins)

问题描述

您好，我需要从连接到多个表的表中删除重复的结果。因此，表A包含发票信息：每个工作号（worknum）可以具有多个发票ID，并且每个发票ID可以具有分组成类别的多个费率代码（即水果，蔬菜，玩具，书籍，糖果，饮料）。所以它看起来像这样：



表A：

WorkNum | InvoiceID | ratecodes

1000 | 1 |水果

1000 | 1 |蔬菜

1000 | 2 |书籍

1000 | 3 |饮料

1000 | 3 |玩具

1002 | 4 |水果

1002 | 5 |书籍

1002 | 7 |糖果



现在这与其他表格相关联，以便按工作号码输入日期和客户名称。



表B：

WorkNum |客户名称|联系

1000 | CustA |艾米

1000 | CustA | Sue

1002 | CustA | Tom



TableC：

WorkNum | EnteredDate |

1000 | 2016-01-01 |

1002 | 2016-01-02 |



现在，如果其中一个价目代码的工号中有玩具，我不希望工作号出现。

这是我的代码：

 < span class =code-keyword>选择  distinct  
 tblA.worknum，
 tblB.CustomerName，
 tblc .entereddate 
 
  From  TableA tblA 
  Inner  加入 TableB tblB   tblA.worknum = tblB.worknum 
  Inner  加入 TableC tblC   tblA.worknum = tblc.worknum 
 
 其中 tblA.worknum = '  1000' 
 和 tblA.ratecodes<> '  toys' 

所以我得到的东西看起来像这样，因为有没有费率代码'玩具'的行（表A中的第1-4行）：



工作号码|客户|输入日期

1000 |客户A | 2016-01-01





我希望worknum 1000不会出现在结果中，因为其中一张发票ID的价格代码是玩具。



有什么方法可以做到这一点吗？我有很多工作号码（只想用一个工号来测试我的查询）。

谢谢！

解决方案

试一试方法，

  DECLARE   @TBL   TABLE  
（
 WorkNum  int ，
 InvoiceID  int ，
 ratecodes  varchar （ 10 ）
）
; 
 
  INSERT   INTO   @ TBL （WorkNum，InvoiceID，ratecodes）
  SELECT   1000 ， 1 ，'  fruit' 
  UNION   ALL  
  SELECT   1000 ， 1 ，'  vegetables' 
  UNION   ALL  
  SELECT   1000 ， 2 ，'  toys' 
  UNION   ALL  
  SELECT   1000 ， 3 ，'  drink' 
  UNION   ALL  
  SELECT   1000 ， 3 ，'  toys' 
 < span class =code-keyword> UNION   ALL  
  SELECT   1002 ， 4 ，'  fruit' 
; 
 
 从 @ TBL  T 
  LEFT   OUTER   JOIN  
（
  SELECT  WorkNum 
  FROM   @ TBL  
  group   by  WorkNum，ratecodes 
   ratecodes = ' 玩具 
）TM  ON  T.WorkNum = TM.WorkNum 
  WHERE  Tm.WorkNum    null  

这将向您展示如何过滤玩具类型的工作项。使用上述方法，您可以像这样修改您的查询

 选择 < span class =code-keyword> distinct  T.worknum，
 tblB.CustomerName，
 tblc.entereddate 
  from   @ TBL  T 
  LEFT   OUTER   JOIN  
（
  SELECT  WorkNum 
  FROM   @ TBL  
  group   by  WorkNum，ratecodes 
   ratecodes = '  toys' 
）TM  ON  T.WorkNum = TM.WorkNum 
 < span class =code-keyword>内部 加入 TableB tblB   T.worknum = tblB.worknum 
 内部 加入 TableC tblC   T.worknum = tblc.worknum 
  WHERE  Tm。 WorkNum    null  

< pre lang =SQL> 选择 distinct
tblA.worknum，
tblB .CustomerName，
tblc.entereddate

From TableA tblA
Inner 加入 TableB tblB tblA.worknum = tblB.worknum
内部 加入 TableC tblC on tblA.worknum = tblc。 worknum

其中 tblA.WorkNum < span class =code-keyword> （
select < span class =code-keyword> distinct A.WorkNum
来自 TableA A
where A.ratecodes = ' toys'）

Hello, I need to remove duplicate results from a table that is joined to multiple tables. So table A contains invoice information: each work number (worknum) can have multiple invoice ID's, and each invoice ID can have multiple rate codes grouped into categories (i.e. fruit, vegetables, toys, books, candy, drinks). So it looks like this:

TableA:
WorkNum |InvoiceID | ratecodes
1000 |1 | fruit
1000 |1 | vegetables
1000 |2 | books
1000 |3 | drinks
1000 |3 | toys
1002| 4| fruits
1002| 5| books
1002| 7| candy

Now this is linked to other tables to get entered date and customer name by the work number.

TableB:
WorkNum |CustomerName | contact
1000 |CustA | Amy
1000 |CustA | Sue
1002 |CustA | Tom

TableC:
WorkNum |EnteredDate |
1000 |2016-01-01 |
1002 |2016-01-02 |

Now, if one of the rate codes has 'toys' in the work number, I don't want the work number to appear at all.
This is my code:

Select distinct
tblA.worknum,
tblB.CustomerName,
tblc.entereddate

From TableA tblA
Inner Join TableB tblB on tblA.worknum = tblB.worknum
Inner Join TableC tblC on tblA.worknum = tblc.worknum

Where tblA.worknum = '1000'  
and tblA.ratecodes <> 'toys'    

So I get something that looks like this since there are lines without rate code 'toys' (lines 1-4 in table A):

Work Number| Customer | Entered Date
1000 | Cust A | 2016-01-01


I'd like the worknum 1000 to not show up in the results at all because one of the invoice ID's has the rate code 'toys'.

Is there any way to do this? I have many work numbers in there (just want to test my query with one work number).
Thank you!

解决方案

Try this approach,

DECLARE @TBL TABLE
(
  WorkNum int,
  InvoiceID int, 
  ratecodes varchar(10)
 )
;

INSERT INTO @TBL (WorkNum, InvoiceID, ratecodes)
SELECT 1000, 1, 'fruit'
UNION ALL
SELECT 1000, 1, 'vegetables'
UNION ALL
SELECT 1000, 2, 'toys'
UNION ALL
SELECT 1000, 3, 'drinks'
UNION ALL
SELECT 1000, 3, 'toys'
UNION ALL
SELECT 1002, 4, 'fruit'
;

select *
from   @TBL T
LEFT OUTER JOIN 
(
SELECT WorkNum
FROM @TBL
group by WorkNum, ratecodes
having ratecodes = 'toys'
) TM ON T.WorkNum = TM.WorkNum
WHERE Tm.WorkNum is null

This will show you how you can filter the workitems of type 'toys'. Using above approach you can modify your query like this

select distinct T.worknum,
tblB.CustomerName,
tblc.entereddate
from   @TBL T
LEFT OUTER JOIN 
(
SELECT WorkNum
FROM @TBL
group by WorkNum, ratecodes
having ratecodes = 'toys'
) TM ON T.WorkNum = TM.WorkNum
Inner Join TableB tblB on T.worknum = tblB.worknum
Inner Join TableC tblC on T.worknum = tblc.worknum
WHERE Tm.WorkNum is null

Select distinct
tblA.worknum,
tblB.CustomerName,
tblc.entereddate
 
From TableA tblA
Inner Join TableB tblB on tblA.worknum = tblB.worknum
Inner Join TableC tblC on tblA.worknum = tblc.worknum
 
where tblA.WorkNum not in (
        select distinct A.WorkNum
        from TableA A
        where A.ratecodes = 'toys')

这篇关于从SQL Server中删除结果中的行（多个连接）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！