我基本上有两张桌子
1)收费:在这里,我们存储餐厅的id (restid)、收费id (收费id )、收费时间(发生收费时的时间)、收费金额(实际收费的整数)、.charge id是计费明细表的外键。
2)计费细节:在这里我们存储所有可能收费的细节。收费(主键int),chargename (收费名称),每天成本(费用的每日成本)
我所期望的:
每家餐厅每项收费的总金额的摘要报告。
表中的当前条目是:
select * from billingcharges;
+--------+----------+---------------+--------------+
| restid | chargeid | chargetime | chargeamount |
+--------+----------+---------------+--------------+
| 1 | 1 | 1536363636363 | 700 |
| 2 | 1 | 1536363636363 | 500 |
| 1 | 1 | 1568789654123 | 500 |
+--------+----------+---------------+--------------+
select * from billingchargedetails;
+----------+--------------------+------------------+
| chargeid | chargename | chargecostperday |
+----------+--------------------+------------------+
| 1 | Base Charge | 50 |
| 2 | Spotlight Listing | 50 |
| 3 | Gold Notification | 500 |
| 4 | Discount (FIRST50) | 18 |
+----------+--------------------+------------------+一个简单的expected.so连接最终没有给出qty和sum作为,我需要某种形式的左或右外部连接,这是我知道并尝试过的。
我尝试了左联接,如下所示:
select restid, B.chargeid, chargename, count(B.chargeid) as qty,
sum(ifnull(chargeamount,0)) as total
from billingcharges as B
left join billingchargedetails as C on B.chargeid=C.chargeid
group by restid,B.chargeid;
+--------+----------+-------------+-----+-------+
| restid | chargeid | chargename | qty | total |
+--------+----------+-------------+-----+-------+
| 1 | 1 | Base Charge | 2 | 1200 |
| 2 | 1 | Base Charge | 1 | 500 |
+--------+----------+-------------+-----+-------+这确实有效,但每一家餐厅都没有收费。即使他们没有出现在左边的桌子里(左桌子),我也需要它的数量0和总数0。
我尝试了一个右联接,mysql从左侧表中的非现有条目中选择了一个随机值,如下所示:
select restid, B.chargeid, chargename, count(B.chargeid) as qty,
sum(ifnull(chargeamount,0)) as total
from billingcharges as B
right join billingchargedetails as C on B.chargeid=C.chargeid
group by restid,B.chargeid;
+--------+----------+-------------------+-----+-------+
| restid | chargeid | chargename | qty | total |
+--------+----------+-------------------+-----+-------+
| NULL | NULL | Spotlight Listing | 0 | 0 |
| 1 | 1 | Base Charge | 2 | 1200 |
| 2 | 1 | Base Charge | 1 | 500 |
+--------+----------+-------------------+-----+-------+预期的输出类似于:
restid chargeid chargename qty totalamount
1 1 Base Charge 2 1200
1 2 Spotlight 0 0
1 3 Gold 0 0
1 4 Discount 0 0
2 1 Base Charge 1 500
2 2 Spotlight 0 0
2 3 Gold 0 0
2 4 Discount 0 0
'same as above expected for each restid in billingcharges'发布于 2018-06-14 19:28:01
在您可以做外部连接之前,您需要生成跨产品的餐厅收费类型。
如下所示(但我还没有测试它):
SELECT R.restid, D.chargename, COUNT(B.chargeid) AS qty,
SUM(IFNULL(B.chargeamount, 0)) AS total
FROM (SELECT DISTINCT restid FROM billingcharges) AS R
CROSS JOIN billingchargedetails AS D
LEFT JOIN billingcharges AS B ON R.restid=B.restid AND D.chargeid=B.chargeid
GROUP BY R.restid, D.chargename;在这个例子中,研发的交叉产品是每一家餐厅与每一种收费类型的交叉.
当然,并非所有这些费用都存在于每一家餐厅。因此,计费的外部联接找到了每个餐厅和收费类型的组合中确实存在的行。
https://stackoverflow.com/questions/50864291
复制相似问题