代码之家 › 专栏 › 技术社区 › kristofer

组合两个查询

subquery sql

kristofer · 技术社区 · 17 年前

第一个查询获取特定日期范围的所有事务详细信息虽然这很棘手,但如果这有意义的话,我实际上需要这个查询中的总和。

SELECT  td.transaction_id,
    sum(    IF( coalesce(ti.na, -1) = 0
            AND coalesce(ti.special_info_type, -1) = 0
            AND coalesce(ti.item_type, '') = 'P'
            AND coalesce(ti.comp_id, 0) <= 0,
            coalesce(disc_amt, 0),
            0
        )
    ) as disc_sum,

    sum(    IF( coalesce(ti.na, -1) = 0
            AND coalesce(ti.special_info_type, -1) = 0
            AND coalesce(ti.item_type, '') = 'P'
            AND coalesce(ti.comp_id, 0) > 0,
            coalesce(comp_amt, 0),
            0
        )
    ) as cSM,

    sum(    IF( coalesce(ti.na, -1) = 0
            AND coalesce(ti.special_info_type, -1) = 0
            AND coalesce(ti.item_type, '') = 'P'
            AND coalesce(ti.comp_id, 0) > 0,
            coalesce(comp_tax, 0),
            0
        )
    ) as cTX

FROM transaction_details td

LEFT OUTER JOIN transaction_items ti
    ON ti.transaction_id = td.transaction_id

WHERE   td.na = 0
    AND td.entry_TS >= ?
    AND td.entry_TS <  ?

GROUP BY td.transaction_id;

对于从上一个查询返回的每个事务,此查询在循环中执行。

SELECT  count(x.id) as refCnt,
    coalesce(sum(x.item_price + x.sub_price), 0) as refAmt,
    coalesce(sum(x.efftax), 0) as refTax

from(
    SELECT  (tiP.item_price - tiP.comp_amt) as item_price,
        coalesce(sum(tiA.item_price), 0) as sub_price,
        (tiP.efftax - tiP.comp_tax) as efftax,
        tiP.id

    from transaction_items tiP

    left outer join transaction_items tiA
        on( tiP.id = tiA.ref_id
            and tiA.item_type = 'A'
            and tiA.na = 0
        ) 

    where   tiP.item_type = 'P'
        and tiP.na = 0
        and tiP.refund = 1
        #and tiP.transaction_id = 

    group by tiP.id
    order by tiP.transaction_id, tiP.order_id
) as x;

2 回复 | 直到 17 年前

Quassnoi 17 年前

首先,你可以移动你的手机 COALESCE “它进入查询。

NULL 在逻辑中,值不构成总和,这就是为什么您可以提前筛选它们:

SELECT  td.transaction_id,
        SUM(IF(ti.comp_id < 0 OR ti.comp_id IS NULL, disc_amt, 0),
        SUM(IF(ti.comp_id > 0, comp_amt, 0),
        SUM(IF(ti.comp_id > 0, comp_tax, 0)
FROM    transaction_details td
LEFT OUTER JOIN
        transaction_items ti
ON      ti.transaction_id = td.transaction_id
WHERE   td.entry_TS >= ?
        AND td.entry_TS <  ?
        AND ti.na = 0
        AND ti.special_info_type = 0
        AND ti.item_type = 'P'
GROUP BY
        td.transaction_id;

其次是 SUM 总和 的 总和

您可以跳过计算中间值 总和 是的。

Lance Roberts 17 年前

试试这个:

SELECT td.transaction_id, SUM(disc_sum)+SUM(cSM)+SUM(cTX)
FROM (
  SELECT SUM(...) As disc_sum, SUM(...) As cSM, SUM(...) As cTX
  FROM transaction_details td
  ....
)