时间戳

首页 > 解决方案 > 查询返回每个产品名称及其到期、支付、取消和退款金额

mysql - 查询返回每个产品名称及其到期、支付、取消和退款金额

问题描述

给出了三个表来获取产品详细信息，例如product_name、due_amount、paid_amount、canceled、refunded。三个表及其架构：

产品

列名 列类型 键/NULL
ID 整数 PK
库存单位 varchar(32)
产品名称 varchar(128)
产品描述 文本
时价 十进制（8,2）
qty_in_stock 整数

发票

列名 列类型 键/NULL
ID 整数 PK
发票号码 整数
客户ID 整数
user_account_id 整数
总价 十进制（8,2）
time_issued varchar ñ
time_due varchar ñ
time_paid varchar ñ
time_cancelled varchar ñ
time_refunded varchar ñ

发票项目

列名 列类型 键/NULL
ID 整数 PK
invoice_id 整数 fk
product_id 整数 fk
数量 整数 fk
价格 十进制（8,2）
line_total_price 十进制（8,2）

我试过这样：

SELECT Distinct product, amount_due,amount_paid,cancelled_prod,refund_prod
    FROM   (SELECT p.product_name AS product,
                    ((p.current_price * ii.quantity) - (ii.quantity * ii.price)) AS amount_due,
                    ( ii.quantity * ii.price )  AS amount_paid,
                    'N/A' AS Cancelled_prod,
                    'N/A' AS refund_prod
            FROM   product p
            INNER JOIN invoice_item ii
            ON p.id = ii.product_id
            INNER JOIN invoice i
            ON i.id = ii.invoice_id
            WHERE  i.time_due IS NOT NULL

            UNION

            SELECT p.product_name AS product,
                    0 AS amount_due,
                    ( ii.quantity * ii.price ) AS amount_paid,
                   'N/A' AS Cancelled_prod,
                   'N/A' AS refund_prod
            FROM   product p
            INNER JOIN invoice_item ii
            ON p.id = ii.product_id
            INNER JOIN invoice i
            ON i.id = ii.invoice_id
            WHERE  i.time_paid IS NOT NULL

            UNION

            SELECT p.product_name AS product,
                    0 AS amount_due,
                    0 AS amount_paid,
                    'cancelled' AS Cancelled_prod,
                    'N/A' AS refund_prod
            FROM   product p
            INNER JOIN invoice_item ii
            ON p.id = ii.product_id
            INNER JOIN invoice i
            ON i.id = ii.invoice_id
            WHERE  i.time_cancelled IS NOT NULL

            UNION

            SELECT p.product_name AS product,
                    0 AS amount_due,
                    0 AS amount_paid,
                    'N/A' AS Cancelled_prod,
                    'refunded' AS refund_prod
            FROM   product p
            INNER JOIN invoice_item ii
            ON p.id = ii.product_id
            INNER JOIN invoice i
            ON i.id = ii.invoice_id
            WHERE  i.time_refunded IS NOT NULL
        ) Z
    ORDER  BY product ASC



Is there a better way to do and also i am not sure whether the query will give correct result?

标签： mysql

解决方案

select product_name, sum(amount_due), sum(amount_paid), sum(amount_cancelled), sum(amount_refunded) 
from (
select product_name,
case when time_due is not null then line_total_price end amount_due,
case when time_paid is not null then line_total_price end amount_paid,
case when time_cancelled is not null then line_total_price end amount_cancelled,
case when time_refunded is not null then line_total_price end amount_refunded
from product a left join invoice_item c on a.id = c.product_id
left join invoice b on b.id = c.invoice_id 
) temp_table 
group by product_name order by product_name

推荐阅读