基于B_id字段汇总两表列值的SQL查询需求

Alright, let's solve this SQL merging and aggregation problem for you. Based on your requirements, here's a straightforward approach to combine the two datasets and get the desired results:

Final SQL Query #

SELECT 
    '2016-2017' AS bill_no,
    B_id,
    COALESCE(SUM(amount), 0) AS amount,
    COALESCE(SUM(tax), 0) AS tax,
    COALESCE(SUM(amount_paid), 0) AS amount_paid,
    COALESCE(SUM(gr), 0) AS gr,
    'sun4269' AS forUser
FROM (
    -- Dataset 1: Aggregated 2016-2017 records
    SELECT 
        B_id,
        COALESCE(sum(amount-gr), 0) AS amount,
        COALESCE(sum(tax), 0) AS tax,
        COALESCE(sum(amount_paid), 0) AS amount_paid,
        COALESCE(sum(gr), 0) AS gr
    FROM tbl_addbill 
    WHERE forUser='sun4269' 
      AND bill_date BETWEEN '2016-04-01' AND '2017-03-31' 
    GROUP BY B_id
    
    UNION ALL
    
    -- Dataset 2: 2015-2016 records with null bill_date
    SELECT 
        B_id,
        amount,
        tax,
        amount_paid,
        gr
    FROM tbl_addbill 
    WHERE forUser='sun4269' 
      AND bill_date IS NULL 
      AND bill_no='2015-2016'
) AS combined_data
GROUP BY B_id;

How This Works #

Let's break down the logic step by step:

• Combine Datasets with UNION ALL: We first merge the results of your two original queries into a single temporary dataset combined_data. Using UNION ALL ensures we keep all records (including duplicate B_ids) so we can sum their values later.
• Group and Aggregate by B_id: The outer query groups the combined data by B_id. For each group, we sum the amount, tax, amount_paid, and gr fields. COALESCE ensures we get 0 instead of NULL if there's no data for a field in one of the datasets.
• Standardize bill_no: We explicitly set bill_no to '2016-2017' for all records, which covers both duplicate and unique B_ids as per your requirements.

Example Verification #

For the duplicate B_id L-1:

• From Dataset 1: amount=21014, amount_paid=19363
• From Dataset 2: amount=25266, amount_paid=4644
• The final result for L-1 will have amount=21014+25266=46280, amount_paid=19363+4644=24007, with bill_no='2016-2017'.

For unique B_ids like B-1 (only in Dataset 1) or K-1 (only in Dataset 2), their original values are retained, and bill_no is updated to '2016-2017'.

内容的提问来源于stack exchange，提问作者sam5808