如何连接两个没有重复记录的表 [英] how to join two tables without duplicate records

问题描述

嗨Frds，

我的表1字段是

Hi Frds,
My Table 1 Fields are

Item Code  Item     Qty
  1       Item1     300

我的表2字段是

My Table 2 Fields are

Job No  Item Code  Item     Qty
  1       1        Item1    150
  2       1        Item1    150

当我加入此表时，结果将是

when I join this table, the result will be

Item Code  Item     Qty   Job No  Item Code  Item     Qty
  1        Item1     300    1       1        Item1    150
                            2       1        Item1    150

喜欢这样。我怎样才能做到这一点。任何人都帮助我。谢谢你



代码块添加 - OriginalGriff [/ edit]

Like that. How Can i do this. Any one help me. Thank u

[edit]Code block added - OriginalGriff[/edit]

推荐答案

你无法生成像SQL查询中的结果。对于右侧项目中的每条记录，左侧项目将重复。



您的UI代码必须过滤掉左侧的重复项目查询。这是一个显示问题，而不是查询问题。

You cannot generate a result like that in an SQL query. The left-side items WILL be duplicated for each record found in the right-side items.

Your UI code has to filter out the duplicated items in the left side of the query. This is a display issue, not a query issue.

正如Dave Kreskowiak所提到的（解决方案1），sql查询产生了什么产生。实现这样的目标，你应该使用专业的报告引擎，比如水晶报告等。



戴夫错过了一件事。 CTE [ ^ ]可能会产生这样的结果。我会尽快改善我的答案。

As Dave Kreskowiak mentioned (solution 1), an sql query produces what produces. The achieve something like this, you should use professional reporting engine, such as Crystal report, etc.

There is one thing that Dave missed. CTE[^] may produce such of results. I'll improve my answer ASAP.

DECLARE @tmp1 TABLE (ItemCode INT,  Item VARCHAR(30), Qty INT)
INSERT INTO @tmp1 (ItemCode, Item, Qty)
VALUES(1, 'Item1', 300),
(2, 'Item2', 500)

DECLARE @tmp2 TABLE (JobNo INT, ItemCode INT,  Item VARCHAR(30), Qty INT)

INSERT INTO @tmp2 (JobNo, ItemCode, Item, Qty)
VALUES(1, 1, 'Item1', 150),
(2, 1, 'Item1', 150),
(3, 2, 'Item1', 50),
(4, 2, 'Item1', 75),
(5, 2, 'Item1', 125),
(6, 2, 'Item1', 100)



;WITH MyCTE AS 
(
	SELECT t1.ItemCode, t1.Item, t1.Qty, t2.JobNo, t2.ItemCode AS ItemCode2, t2.Item AS Item2, t2.Qty AS Qty2, t2.MySeed 
	FROM @tmp1 AS t1 INNER JOIN (
		SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY JobNo) AS [MySeed] 
		FROM @tmp2 
		) AS t2 ON t1.ItemCode = t2.ItemCode
	WHERE t2.MySeed = 1
	UNION ALL 
	SELECT NULL AS ItemCode, NULL AS Item, NULL AS Qty, t2.JobNo, t2.ItemCode AS ItemCode2, t2.Item AS Item2, t2.Qty AS Qty2, t2.MySeed 
	FROM @tmp1 AS t1 INNER JOIN (
		SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY JobNo) AS [MySeed] 
		FROM @tmp2 
		) AS t2 ON t1.ItemCode = t2.ItemCode
	WHERE t2.MySeed > 1
)
SELECT *
FROM MyCTE
ORDER BY ItemCode2, MySeed

[/编辑]



结果：

[/EDIT]

Result:

ItemCode	Item	Qty	JobNo	ItemCode2	Item2	Qty2	MySeed
1	       Item1	300	    1	      1	    Item1	150	    1
NULL	   NULL	    NULL	2	      1	    Item1	150	    2
2	       Item2	500	    3	      2	    Item1	50	    1
NULL	   NULL	    NULL	4	      2	    Item1	75	    2
NULL	   NULL	    NULL	5	      2	    Item1	125	    3
NULL	   NULL	    NULL	6	      2	    Item1	100	    4

这篇关于如何连接两个没有重复记录的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！