求高手幫忙，BW555在不！！！

Question

delete from T
WHERE B IN (SELECT B FROM T GROUP BY B HAVING COUNT(1)=1)
DELETE FROM T T1
WHERE NOT EXISTS(SELECT 1 FROM T WHERE B=T1.B AND ROWID<>T1.ROWID)

Answer 1

不好意思，我可能表達錯了，我的意思是用SELECT 只顯示B行有重的數據，4個字段都要Show出來

Answer 2

SELECT * FROM T
WHERE B IN (SELECT B FROM T GROUP BY B HAVING COUNT(1)>1)
SELECT * FROM T T1
WHERE EXISTS(SELECT 1 FROM T WHERE B=T1.B AND ROWID<>T1.ROWID)

Answer 3

/* Formatted on 2014/9/3 下午 03:19:44 (QP5 v5.256.13226.35510) */
SELECT *
  FROM (  SELECT TO_CHAR (WMSYS.WM_CONCAT (A.BOM_NO)) BOM_NO,
                 A.KEY_PART_NO,
                 TO_CHAR (WMSYS.WM_CONCAT (A.FEEDER_NO)) Feeder,
                 A.IMPORT_TIME
            FROM SFIS1.C_SMT_BOM_T A
           WHERE     A.BOM_NO IN ('061-03937-0017-R02-BCM402-6',
                                  '061-03937-0019-R02-BDT401-1',
                                  '061-06255-0000-R01-BDT401-1')
                 AND A.KEY_PART_NO IN (SELECT b.KEY_PART_NO
                                         FROM SFIS1.C_SMT_BOM_T b
                                        WHERE B.BOM_NO =
                                                 '061-06255-0000-R01-BDT401-1')
        GROUP BY A.KEY_PART_NO, A.IMPORT_TIME            --  HAVING COUNT(1)>1
        ORDER BY A.KEY_PART_NO) b
 WHERE B.KEY_PART_NO IN (  SELECT c.KEY_PART_NO
                             FROM (  SELECT TO_CHAR (WMSYS.WM_CONCAT (A.BOM_NO))
                                               BOM_NO,
                                            A.KEY_PART_NO,
                                            TO_CHAR (
                                               WMSYS.WM_CONCAT (A.FEEDER_NO))
                                               Feeder,
                                            A.IMPORT_TIME
                                       FROM SFIS1.C_SMT_BOM_T A
                                      WHERE     A.BOM_NO IN ('061-03937-0017-R02-BCM402-6',
                                                             '061-03937-0019-R02-BDT401-1',
                                                             '061-06255-0000-R01-BDT401-1')
                                            AND A.KEY_PART_NO IN (SELECT b.KEY_PART_NO
                                                                    FROM SFIS1.C_SMT_BOM_T b
                                                                   WHERE B.BOM_NO =
                                                                            '061-06255-0000-R01-BDT401-1')
                                   GROUP BY A.KEY_PART_NO, A.IMPORT_TIME --  HAVING COUNT(1)>1
                                   ORDER BY A.KEY_PART_NO) c
                         GROUP BY c.KEY_PART_NO
                           HAVING COUNT (1) > 1)
恩，這是我要的結果 ，再請幫忙看看可以簡化語句不

Answer 4

       SELECT TO_CHAR(WMSYS.WM_CONCAT (A.BOM_NO))  BOM_NO,A.KEY_PART_NO ,
      TO_CHAR(WMSYS.WM_CONCAT (A.FEEDER_NO )) Feeder,
      TO_CHAR(WMSYS.WM_CONCAT (A.IMPORT_TIME ))  DDate
       FROM SFIS1.C_SMT_BOM_T  A
       WHERE A.BOM_NO IN('061-03937-0017-R02-BCM402-6','061-03937-0019-R02-BDT401-1','061-06255-0000-R01-BDT401-1')       
       and A.KEY_PART_NO in(select b.KEY_PART_NO from SFIS1.C_SMT_BOM_T b where B.BOM_NO='061-06255-0000-R01-BDT401-1'  )
       GROUP BY  A.KEY_PART_NO  HAVING COUNT(1)>1
       ORDER BY A.KEY_PART_NO
得到的結果如下：
061-06255-0000-R01-BDT401-1,061-03937-0019-R02-BDT401-1 045-01108-0000 2-B03,2-B02 23-7月 -14,28-6月 -14
061-06255-0000-R01-BDT401-1,061-03937-0019-R02-BDT401-1 075-09255-0001 2-A17,2-A18 23-7月 -14,28-6月 -14
061-06255-0000-R01-BDT401-1,061-03937-0019-R02-BDT401-1 075-09275-0000 1-18L,1-12L 23-7月 -14,28-6月 -14
061-06255-0000-R01-BDT401-1,061-03937-0017-R02-BCM402-6,061-03937-0019-R02-BDT401-1 075-09281-0000 2-A04,4-26L,2-A20 23-7月 -14,16-5月 -14,28-6月 -14
061-06255-0000-R01-BDT401-1,061-03937-0019-R02-BDT401-1 075-09308-0000 2-A05,2-A14 23-7月 -14,28-6月 -14
061-06255-0000-R01-BDT401-1,061-03937-0019-R02-BDT401-1 075-09337-0002 2-A09,2-A17 23-7月 -14,28-6月 -14
061-06255-0000-R01-BDT401-1,061-03937-0019-R02-BDT401-1 075-09449-0000 2-A20,2-A19 23-7月 -14,28-6月 -14
061-06255-0000-R01-BDT401-1,061-03937-0019-R02-BDT401-1 080-04452-0002 2-A16,2-A07 23-7月 -14,28-6月 -14
061-06255-0000-R01-BDT401-1,061-03937-0019-R02-BDT401-1 080-04496-0000 2-B09,2-A02 23-7月 -14,28-6月 -14
還可以按照1L的方式顯示不

Answer 5

重复的部分分离出来，避免二次解析
另外如果表的数据量较大，建议采用exists的方法
with B AS (
SELECT TO_CHAR (WMSYS.WM_CONCAT (A.BOM_NO)) BOM_NO,
A.KEY_PART_NO,
TO_CHAR (WMSYS.WM_CONCAT (A.FEEDER_NO)) Feeder,
A.IMPORT_TIME
FROM SFIS1.C_SMT_BOM_T A
WHERE A.BOM_NO IN ('061-03937-0017-R02-BCM402-6',
'061-03937-0019-R02-BDT401-1',
'061-06255-0000-R01-BDT401-1')
AND A.KEY_PART_NO IN (SELECT b.KEY_PART_NO
FROM SFIS1.C_SMT_BOM_T b
WHERE B.BOM_NO =
'061-06255-0000-R01-BDT401-1')
        GROUP BY A.KEY_PART_NO, A.IMPORT_TIME--HAVING COUNT(1)>1
        ORDER BY A.KEY_PART_NO
)
SELECT *
  FROM b
 WHERE KEY_PART_NO IN (SELECT B.KEY_PART_NO
                             FROM B
                         GROUP BY B.KEY_PART_NO
                           HAVING COUNT (1) > 1)

Answer 6

只是使用了with as的写法，具体用法说明见下面的链接
ORACLE WITH AS 用法