SQL bergabung dengan jumlah

Halo, saya punya Kueri SQL berikut yang menghasilkan hasil berikut

SELECT * FROM
     (
     Select
        catalogid, numitems, allitems - numitems ignoreditems
     from
         ( 
             select
                 i.catalogid,
                 case
                     when Exists(select paymentodate from payentmethodvalidation q where q.paymentnoodate = o.ocardtype) AND NOT EXISTS(SELECT booked FROM bookedordersids m where CAST(m.booked AS int)=o.orderid) then sum(i.numitems)
                     when Exists(select paymentodate from payentmethodvalidation q where q.paymentodate = o.ocardtype) AND odate is not null AND NOT EXISTS(SELECT booked FROM bookedordersids m where CAST(m.booked AS int)=o.orderid) then sum(i.numitems)
                 else 0 end numitems,
                 sum(numitems) allitems 
             from 
                 "orders o
             inner join
                 "oitems i 
             on 
                 "i.orderid=o.orderid
             inner join
                 "products T1
             on 
                 "T1.catalogid = i.catalogid
             group by 
                 "i.catalogid, ocardtype, odate,o.orderid
         ) A
     ) B
     INNER JOIN
     (
         SELECT 
             catalogId, 
             ProcessedSucssessfully = 
                STUFF((SELECT ', ' + CAST( b.orderid as varchar(10))
                       FROM oitems b JOIN orders o ON b.orderid = o.orderid 
                       WHERE b.catalogId = a.catalogId 
                       AND  NOT EXISTS(SELECT booked FROM bookedordersids m where CAST(m.booked AS int)=o.orderid) AND (Exists(select paymentodate from payentmethodvalidation q where q.paymentnoodate = o.ocardtype) OR Exists(select paymentodate from payentmethodvalidation q where q.paymentodate = o.ocardtype) and o.odate is not null)
                       FOR XML PATH('')), 1, 2, ''), 
                           "NotProcessed = 
                 STUFF((SELECT ', ' + CAST( c.orderid as varchar(10))
                        FROM oitems c JOIN orders o ON c.orderId = o.orderid
                        WHERE c.catalogid = a.catalogid 
                        AND (o.ocardtype in ('mastercard') OR o.ocardtype is null) and o.odate is null
                        FOR XML PATH('')), 1, 2, '') 
         FROM 
             oitems a 
         GROUP BY 
             a.catalogid 
     )C
         ON 
             B.catalogid = C.catalogid

hasil query ini dapat anda lihat pada gambar berikut masukkan deskripsi gambar di sini

Anda melihat 2 baris yang dilingkari itu, saya ingin baris tersebut berada dalam satu baris yang hanya akan menjumlahkan angka, nilai berhasil diproses dan semua nilai lainnya akan selalu sama untuk catatan katalog berbagi jadi tidak ada masalah dengan mereka.

pada dasarnya baris hasil harus memiliki jumlah nilai numitem di semua baris yang memiliki katalogid yang sama

jadi bagaimana saya bisa mengatasi masalah ini?


sql sum sql-server-2008-r2
person user1570048    schedule 14.10.2012    source sumber

Jawaban (1)


arrow_upward
0
arrow_downward

Jika, seperti yang Anda sebutkan, semua nilai lainnya sama, solusinya sederhana:

SELECT CATALOGID, 
       Sum(NUMITEMS), 
       ALLITEMS - NUMITEMS ignoreditems 
FROM   (SELECT CATALOGID, 
               NUMITEMS, 
               ALLITEMS - NUMITEMS ignoreditems 
        FROM   (SELECT i.CATALOGID, 
                       CASE 
                         WHEN EXISTS(SELECT PAYMENTODATE 
                                     FROM   PAYENTMETHODVALIDATION q 
                                     WHERE  q.PAYMENTNOODATE = o.OCARDTYPE) 
                              AND NOT EXISTS(SELECT BOOKED 
                                             FROM   BOOKEDORDERSIDS m 
                                             WHERE  Cast(m.BOOKED AS 
                                                    INT) = o.ORDERID) 
                       THEN 
                         Sum(i.NUMITEMS) 
                         WHEN EXISTS(SELECT PAYMENTODATE 
                                     FROM   PAYENTMETHODVALIDATION q 
                                     WHERE  q.PAYMENTODATE = o.OCARDTYPE) 
                              AND ODATE IS NOT NULL 
                              AND NOT EXISTS(SELECT BOOKED 
                                             FROM   BOOKEDORDERSIDS m 
                                             WHERE  Cast(m.BOOKED AS 
                                                    INT) = o.ORDERID) 
                       THEN 
                         Sum(i.NUMITEMS) 
                         ELSE 0 
                       END           numitems, 
                       Sum(NUMITEMS) allitems 
                FROM   ORDERS o 
                       INNER JOIN OITEMS i 
                               ON i.ORDERID = o.ORDERID 
                       INNER JOIN PRODUCTS T1 
                               ON T1.CATALOGID = i.CATALOGID 
                GROUP  BY i.CATALOGID, 
                          OCARDTYPE, 
                          ODATE, 
                          o.ORDERID) A) B 
       INNER JOIN (SELECT CATALOGID, 
                          ProcessedSucssessfully = Stuff( 
                          (SELECT ', ' + Cast( b.ORDERID 
                                  AS VARCHAR 
                                  (10)) 
                           FROM   OITEMS b 
                                  JOIN ORDERS o 
                                    ON 
                          b.ORDERID = o.ORDERID 
                           WHERE  b.CATALOGID = 
                                  a.CATALOGID 
                                  AND NOT 
                          EXISTS(SELECT BOOKED 
                                 FROM   BOOKEDORDERSIDS 
                                        m 
                                 WHERE 
                          Cast( 
                                m.BOOKED AS 
                          INT) = o.ORDERID) 
                                  AND ( EXISTS 
                                  (SELECT PAYMENTODATE 
                                   FROM 
                                        PAYENTMETHODVALIDATION q 
                                               WHERE 
                                  q.PAYMENTNOODATE = 
                                  o.OCARDTYPE) 
                                         OR EXISTS 
                                        (SELECT 
                                            PAYMENTODATE 
                                                   FROM 
                                            PAYENTMETHODVALIDATION 
                                            q 
                                                   WHERE 
                                        q.PAYMENTODATE = 
                  o.OCARDTYPE) 
                  AND o.ODATE IS NOT NULL ) 
                  FOR XML PATH('')), 1, 2, ''), 
                  NotProcessed = Stuff((SELECT ', ' + Cast( c.ORDERID AS VARCHAR 
                                               (10)) 
                  FROM   OITEMS c 
                  JOIN ORDERS o 
                  ON c.ORDERID = o.ORDERID 
                  WHERE  c.CATALOGID = a.CATALOGID 
                  AND ( o.OCARDTYPE IN ( 'mastercard' ) 
                  OR o.OCARDTYPE IS NULL ) 
                  AND o.ODATE IS NULL 
                  FOR XML PATH('')), 1, 2, '') 
                   FROM   OITEMS a 
                   GROUP  BY a.CATALOGID)C 
               ON B.CATALOGID = C.CATALOGID 
GROUP  BY CATALOGID, 
          ALLITEMS - NUMITEMS
person Gidil    schedule 14.10.2012