如何为不同的组找到重复的行数 [英] How to find duplicate bunches of rows for differrent group

问题描述

朋友，

我有一张包含4列的表格。

Friends,
I have a table which contains 4 columns like below.

GRP_KIT_NO	COMPONENT	COMP_TYPE	QTY
PLATE-1_4123	99S31653	PRT	        1
PLATE-1_4123	33125112	PRT	        1
PLATE-1_4123	49DE0006	PRT	        1

现在我在不同的Grp_KIt_NO相同的表中插入相同的行

Now I insert same rows in same table with different Grp_KIt_NO like

GRP_KIT_NO	COMPONENT	COMP_TYPE	QTY
PLATE-2_4123	        99S31653	       PRT	   1
PLATE-2_4123	        33125112	       PRT	    1
PLATE-2_4123	        49DE0006	       PRT	    1

所以我创建了多个副本同一日期与不同的Grp_KIt_NO。



现在我们的客户不想向包含相同数据的dupliacte组收费。



我无法找到这样的团体。



没有我举个例子



supppose我输入了

SO I have created mulitple copy of same date with different Grp_KIt_NO.

Now our client doesnt want to bill dupliacte groups which contains same data.

I am not able to find such group.

NOw I give an example

supppose I have input

GRP_KIT_NO	COMPONENT	COMP_TYPE	QTY
PLATE-1_4123	22              PRT	        1
PLATE-1_4123	33              PRT	        1

PLATE-2_4123	22	        PRT	        1
PLATE-2_4123	33	        PRT	        1

PLATE-3_4123	11	        PRT	        1
PLATE-3_4123	33      	PRT	        1
PLATE-3_4123	22      	PRT	        1

AAA-3_4123	33      	PRT	        1

所以我想输出像

So I want output like

GRP_KIT_NO	COMPONENT	COMP_TYPE	QTY
PLATE-1_4123	22       	PRT	        1
PLATE-1_4123	33              PRT	        1

PLATE-2_4123	22      	PRT	        1
PLATE-2_4123	33       	PRT	        1

因为grp_kit_no与精确数据匹配。



请帮帮我们。



我尝试了什么：



我尝试了以下查询，但没有按预期进行

Becuase both grp_kit_no matches exact data.

Please help me guys.

What I have tried:

I tried below query but not as expected

WITH A   -- Get a list of unique combinations of Alias and [Record ID]
AS  (
   SELECT Distinct   GRP_KIT_NO, COMPONENT, COMP_TYPE,QTY--,INDEX_NO
    from GROUP_KIT_BOM
)
,   B  -- Get a list of all those Alias values that have more than one [Record ID] associated
AS  (
    SELECT COMPONENT, COMP_TYPE,QTY--,INDEX_NO
    FROM   A  
    GROUP BY
           COMPONENT, COMP_TYPE,QTY--,INDEX_NO
    HAVING COUNT(*) > 1
)
--select * from b order by COMPONENT, COMP_TYPE,QTY
SELECT A.GRP_KIT_NO, A.COMPONENT, A.COMP_TYPE,A.QTY
FROM    A
    JOIN B
        ON  A.COMPONENT = B.COMPONENT AND A.COMP_TYPE=B.COMP_TYPE ANd A.QTY=B.QTY-- AND A.INDEX_NO=B.INDEX_NO
              order by GRP_KIT_NO,COMPONENT, COMP_TYPE,QTY

推荐答案

我使用下面的代码完成了这个...

I have done this by using below code..

SELECT DISTINCT
  SUBSTRING(Grp_kit_NO, 0, CHARINDEX('_', Grp_kit_NO)) GRP_KIT_NO,  0 rownum INTO #temp
FROM group_kit_detail
DECLARE @i int = 0
UPDATE #temp
SET @i = rownum = @i + 1

SELECT
  GRP_KIT_NO,
  CONVERT(varchar(max), '') comp,
  0 RW INTO #T1
FROM GROUP_KIT_BOM
WHERE 1 = 2
SELECT
  CONVERT(varchar(max), '') GRP_KIT_NO,
  CONVERT(varchar(max), '') Match_GRP_KIT_NO INTO #Final
FROM GROUP_KIT_BOM
WHERE 1 = 2


DECLARE @RowCounter int = 1
WHILE @RowCounter <= (SELECT
    COUNT(1)
  FROM #temp)
BEGIN
  DECLARE @MasterGroup varchar(50) = ''
  SELECT
    @MasterGroup = GRP_KIT_NO
  FROM #temp
  WHERE rownum = @RowCounter

  SELECT
    GRP_KIT_NO,
    COMPONENT + COMP_TYPE + QTY comp,
    0 Row1 INTO #TInput
  FROM GROUP_KIT_BOM
  WHERE SUBSTRING(Grp_kit_NO, 0, CHARINDEX('_', Grp_kit_NO)) = @MasterGroup
  ORDER BY GRP_KIT_NO, comp
  DECLARE @j int = 0
  UPDATE #TInput
  SET @j = Row1 = @j + 1

  INSERT INTO #T1
    SELECT
      GRP_KIT_NO,
      COMP = STUFF((SELECT
        ',' + COMP
      FROM #TInput A
      WHERE A.GRP_KIT_NO = B.GRP_KIT_NO     
      FOR xml PATH ('')), 1, 1, ''),
      0
    FROM #TInput B
    GROUP BY B.GRP_KIT_NO
    ORDER BY COMP

  DROP TABLE #TInput
  SET @RowCounter = @RowCounter + 1

END

DECLARE @k int = 0
UPDATE #T1
SET @k = RW = @k + 1


DECLARE @Counter int = 1-----------------matching comp

WHILE @Counter <= (SELECT
    COUNT(1)
  FROM #T1)
BEGIN

  DECLARE @Group varchar(50) = ''
  SELECT
    @Group = GRP_KIT_NO
  FROM #T1
  WHERE RW = @Counter

  INSERT INTO #Final
    SELECT DISTINCT
      A.GRP_KIT_NO,
      B.GRP_KIT_NO
    FROM #T1 A,
         #T1 B
    WHERE B.GRP_KIT_NO <> @Group
    AND A.comp = B.comp
    AND A.GRP_KIT_NO <> B.GRP_KIT_NO
    ORDER BY A.GRP_KIT_NO

  SET @Counter = @Counter + 1
END
 
SELECT
  *
FROM #Final
		
DROP TABLE #Final
DROP TABLE #T1
DROP TABLE #TEMP

这篇关于如何为不同的组找到重复的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！