Sqlite从同一个表和GROUP BY中选择和连接

jfewjypa 于 2022-11-15 发布在 SQLite

关注(0)|答案(1)|浏览(383)

我有一张如下所示的Sqlitetable。
Bill_no|Bill_Date|折扣|费用|合计|Bill_type
-|
12745|2022-08-30|200|普通话单
15246|2022-08-30|100|退票
12345|2022-08-31||35|920|普通账单
56789|2022-08-31|10||310|普通账单
15248|2022-08-31|320|普通话单
现在我想对此执行一个查询，其中使用BILL_TYPE和GROUP BY得到折扣之和作为折扣，费用之和为费用，总计之和为总数。
Bill_type还可以具有“Return Bill”值。(可能是也可能不是同一天)
我想要的输出如下。
Bill_Date|折扣|费用|退货|合计
-|
2022-08-30|0|0|100|100
2022-08-31|10|35|0|1575
输出表中的合计=合计+费用-折扣-第一个表中的退货
输出表中的退货量=总计的总和，其中Bill_type=当日的‘退货单’
如果Bill_type为‘Return Bill’，则它将没有折扣、费用值
我试着跟着他。

select bill_date,discount,expense,returns, total FROM 
(select bill_date, sum(discount) as discount,sum(expense) as expense,
sum(total) as total from invoice where bill_type='Normal Bill' 
and bill_date between '2022-08-30' and '2022-08-31' group by bill_date) 
JOIN 
(select bill_date, sum(total) as returns from invoice where 
bill_type='Return Bill' and bill_date 
between '2022-08-30' and '2022-08-31' group by bill_date) 
USING (bill_date);

select bill_date,discount,expense,returns, total FROM 
(select bill_date, sum(discount) as discount,sum(expense) as expense,
sum(total) as total from invoice where bill_type='Normal Bill' 
and bill_date between '2022-08-30' and '2022-08-31') 
JOIN 
(select bill_date, sum(total) as returns from invoice where 
bill_type='Return Bill' and bill_date 
between '2022-08-30' and '2022-08-31') 
USING (bill_date)  
group by bill_date;

这将产生一些输出，但使用GROUPBY语句，但是当我添加GROUPBY语句时，输出将消失。
有没有人可以帮我做一个查询，它能产生第二个表中所需的输出？

sqlite

来源：https://stackoverflow.com/questions/73559048/sqlite-select-and-join-from-same-table-and-group-by

1条答案

按热度按时间

klr1opcd1#

您可以使用条件聚合：

SELECT bill_date,
       TOTAL(discount) discount,
       TOTAL(expense) expense,
       TOTAL(CASE WHEN bill_type = 'Return Bill' THEN total END) returns,
       TOTAL(
         total * CASE bill_type WHEN 'Normal Bill' THEN 1 WHEN 'Return Bill' THEN -1 END +
         COALESCE(expense, 0) -
         COALESCE(discount, 0)
       ) total
FROM invoice
WHERE bill_date BETWEEN '2022-08-30' AND '2022-08-31'
GROUP BY bill_date;

请参阅demo。

赞(0）回复(0）举报 2022-11-15

我来回答

Sqlite从同一个表和GROUP BY中选择和连接

1条答案

相关问题

热门标签

最新问答