主页 / dba / 问题 / 89706
Accepted
jhowe
Asked: 2015-01-21 05:55:45 +0800 CST

不合并所有行的sql连接

  • 772

我已经搜索过问题，但似乎不适用于我的场景。请参阅下面的附加代码。我正在尝试连接此数据的两个子集，但得到重复项/太多行，请参阅主要查询和主要结果下方的子集（我希望您可以通过提供没有表定义的数据/示例来理解，因为表很大）（请请原谅格式化，我不知道如何排列这里的所有内容）：

IF OBJECT_ID(N'tempdb..#TRANS', N'U') IS NOT NULL
    BEGIN
        DROP TABLE #TRANS;
    END

DECLARE @Item NVARCHAR(6) = 'AAS682' ,
    @ExpiryDate AS DATETIME = '2015-01-10 00:00:00.000'

SELECT  ITO.REFERENCEID ,
        WP.PRODID ,
        WP.WMSPALLETID ,
        ITR.ITEMID ,
        ITR.QTY ,
        ITR.STATUSISSUE ,
        ITR.STATUSRECEIPT ,
        ITO.REFERENCECATEGORY ,
        PT.USEBYDATE
INTO    #TRANS
FROM    dbo.INVENTTRANS AS ITR
        JOIN INVENTDIM AS ID ON ID.INVENTDIMID = ITR.INVENTDIMID
        JOIN dbo.WMSPALLET AS WP ON WP.WMSPALLETID = ID.WMSPALLETID
        JOIN dbo.PRODTABLE AS PT ON PT.PRODID = WP.PRODID
        JOIN dbo.INVENTTRANSORIGIN AS ITO ON ITR.INVENTTRANSORIGIN = ITO.RECID
WHERE   ITO.REFERENCECATEGORY IN ( 0, 2, 8 )
        AND PT.USEBYDATE = @ExpiryDate;

SELECT * FROM #TRANS

这给了我：

REFERENCEID PRODID  WMSPALLETID ITEMID  QTY STATUSISSUE STATUSRECEIPT   REFERENCECATEGORY   USEBYDATE
M0000042    M0000042    1288390 BAS242  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000042    M0000042    1288391 BAS242  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000042    M0000042    1288392 BAS242  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000042    M0000042    1288393 BAS242  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000042    M0000042    1288394 BAS242  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000043    M0000043    1288395 BAS243  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000043    M0000043    1288396 BAS243  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000043    M0000043    1288397 BAS243  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000043    M0000043    1288398 BAS243  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000043    M0000043    1288399 BAS243  255.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000044    M0000044    1288400 AAS682  168.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000044    M0000044    1288401 AAS682  168.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000044    M0000044    1288402 AAS682  168.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000044    M0000044    1288403 AAS682  168.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000044    M0000044    1288404 AAS682  168.0000000000000000    0   2   2   2015-01-10 00:00:00.000
M0000044    M0000042    1288390 BAS242  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000042    1288391 BAS242  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000042    1288392 BAS242  -50.0000000000000000    2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288395 BAS243  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288396 BAS243  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288397 BAS243  -50.0000000000000000    2   0   8   2015-01-10 00:00:00.000
0013949 M0000044    1288400 AAS682  -32.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   2   0   0   2015-01-10 00:00:00.000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    2   0   0   2015-01-10 00:00:00.000

我想加入这两个查询结果集

WITH    ACODE
          AS ( SELECT   *
               FROM     #TRANS
               WHERE    ITEMID = @Item
                        AND STATUSISSUE IN ( 1, 2 )
             )
    SELECT  *
    FROM    ACODE
SELECT  *
FROM    #TRANS
WHERE   REFERENCECATEGORY = 8

REFERENCEID PRODID  WMSPALLETID ITEMID  QTY STATUSISSUE STATUSRECEIPT   REFERENCECATEGORY   USEBYDATE
0013949 M0000044    1288400 AAS682  -32.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   2   0   0   2015-01-10 00:00:00.000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    2   0   0   2015-01-10 00:00:00.000

有了这个

REFERENCEID PRODID  WMSPALLETID ITEMID  QTY STATUSISSUE STATUSRECEIPT   REFERENCECATEGORY   USEBYDATE
M0000044    M0000042    1288390 BAS242  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000042    1288391 BAS242  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000042    1288392 BAS242  -50.0000000000000000    2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288395 BAS243  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288396 BAS243  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288397 BAS243  -50.0000000000000000    2   0   8   2015-01-10 00:00:00.000

我试过了

SELECT  ACODE.REFERENCEID ,
            ACODE.PRODID ,
            ACODE.WMSPALLETID ,
            ACODE.ITEMID ,
            ACODE.QTY ,
            TRANS.REFERENCEID ,
            TRANS.PRODID ,
            TRANS.WMSPALLETID ,
            TRANS.ITEMID ,
            TRANS.QTY
    FROM    ACODE
            CROSS APPLY ( SELECT    REFERENCEID ,
                                    PRODID ,
                                    WMSPALLETID ,
                                    ITEMID ,
                                    QTY
                          FROM      #TRANS
                          WHERE     ACODE.PRODID != #TRANS.PRODID
                                    AND REFERENCECATEGORY = 8
                        ) TRANS

REFERENCEID PRODID  WMSPALLETID ITEMID  QTY REFERENCEID PRODID  WMSPALLETID ITEMID  QTY
0013949 M0000044    1288400 AAS682  -32.0000000000000000    M0000044    M0000042    1288390 BAS242  -255.0000000000000000
0013949 M0000044    1288400 AAS682  -32.0000000000000000    M0000044    M0000042    1288391 BAS242  -255.0000000000000000
0013949 M0000044    1288400 AAS682  -32.0000000000000000    M0000044    M0000042    1288392 BAS242  -50.0000000000000000
0013949 M0000044    1288400 AAS682  -32.0000000000000000    M0000044    M0000043    1288395 BAS243  -255.0000000000000000
0013949 M0000044    1288400 AAS682  -32.0000000000000000    M0000044    M0000043    1288396 BAS243  -255.0000000000000000
0013949 M0000044    1288400 AAS682  -32.0000000000000000    M0000044    M0000043    1288397 BAS243  -50.0000000000000000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   M0000044    M0000042    1288390 BAS242  -255.0000000000000000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   M0000044    M0000042    1288391 BAS242  -255.0000000000000000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   M0000044    M0000042    1288392 BAS242  -50.0000000000000000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   M0000044    M0000043    1288395 BAS243  -255.0000000000000000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   M0000044    M0000043    1288396 BAS243  -255.0000000000000000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   M0000044    M0000043    1288397 BAS243  -50.0000000000000000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    M0000044    M0000042    1288390 BAS242  -255.0000000000000000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    M0000044    M0000042    1288391 BAS242  -255.0000000000000000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    M0000044    M0000042    1288392 BAS242  -50.0000000000000000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    M0000044    M0000043    1288395 BAS243  -255.0000000000000000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    M0000044    M0000043    1288396 BAS243  -255.0000000000000000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    M0000044    M0000043    1288397 BAS243  -50.0000000000000000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    M0000044    M0000042    1288390 BAS242  -255.0000000000000000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    M0000044    M0000042    1288391 BAS242  -255.0000000000000000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    M0000044    M0000042    1288392 BAS242  -50.0000000000000000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    M0000044    M0000043    1288395 BAS243  -255.0000000000000000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    M0000044    M0000043    1288396 BAS243  -255.0000000000000000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    M0000044    M0000043    1288397 BAS243  -50.0000000000000000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    M0000044    M0000042    1288390 BAS242  -255.0000000000000000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    M0000044    M0000042    1288391 BAS242  -255.0000000000000000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    M0000044    M0000042    1288392 BAS242  -50.0000000000000000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    M0000044    M0000043    1288395 BAS243  -255.0000000000000000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    M0000044    M0000043    1288396 BAS243  -255.0000000000000000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    M0000044    M0000043    1288397 BAS243  -50.0000000000000000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    M0000044    M0000042    1288390 BAS242  -255.0000000000000000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    M0000044    M0000042    1288391 BAS242  -255.0000000000000000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    M0000044    M0000042    1288392 BAS242  -50.0000000000000000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    M0000044    M0000043    1288395 BAS243  -255.0000000000000000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    M0000044    M0000043    1288396 BAS243  -255.0000000000000000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    M0000044    M0000043    1288397 BAS243  -50.0000000000000000

但这给了我 6 * 6 36 行...我只想将正确的查询连接到左侧查询的末尾。完全像一个工会

SELECT  *
                        FROM    ACODE
                        UNION
                        ( SELECT    *
                          FROM      #TRANS
                          WHERE     REFERENCECATEGORY = 8
                        )

REFERENCEID PRODID  WMSPALLETID ITEMID  QTY STATUSISSUE STATUSRECEIPT   REFERENCECATEGORY   USEBYDATE
0013949 M0000044    1288400 AAS682  -32.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013949 M0000044    1288402 AAS682  -168.0000000000000000   2   0   0   2015-01-10 00:00:00.000
0013951 M0000044    1288400 AAS682  -35.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013951 M0000044    1288401 AAS682  -29.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013952 M0000044    1288400 AAS682  -28.0000000000000000    2   0   0   2015-01-10 00:00:00.000
0013953 M0000044    1288400 AAS682  -73.0000000000000000    2   0   0   2015-01-10 00:00:00.000
M0000044    M0000042    1288390 BAS242  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000042    1288391 BAS242  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000042    1288392 BAS242  -50.0000000000000000    2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288395 BAS243  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288396 BAS243  -255.0000000000000000   2   0   8   2015-01-10 00:00:00.000
M0000044    M0000043    1288397 BAS243  -50.0000000000000000    2   0   8   2015-01-10 00:00:00.000

除了附加到查询右侧的底部 6 行...基本上，“B”项构成了“A”项。这是这份报告的目的，但所有数据都存在于同一张表中，这正是我真正苦苦挣扎的……谢谢！

sql-server-2008-r2

1 个回答

  1. Best Answer

    对此有一个简单的答案。如果表之间的关系不存在，无法为您提供所需的结果，您需要创建自己的关系。我使用 ROW_NUMBER OVER (ORDER BY PALLETID) 给我两个数据集的唯一 ID。然后简单地连接（确保检查不同的连接以确保不从一侧或另一侧排除行）ACODEID = BCODEID。简单的！

    • 0

相关问题