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

MySQL联接查询未按预期工作

join mysql

Kushan · 技术社区 · 6 年前

我基本上有两张桌子:

1)计费费用:这里我们存储餐厅的ID(restid)、费用ID(charge id)、收费时间(发生收费时的时间单位毫秒)、收费金额(实际收费的利息金额)。费用ID是BillingChargeDetails表的外键。

2)计费费用明细:这里我们存储所有可能费用的明细。ChargeID(主键int)、ChargeName(费用名称)、PerdayCost(每天费用)

我期望的是:

每个餐厅每项收费的汇总报告。

表中的当前条目是:

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 |
+----------+--------------------+------------------+

ChargeID上的一个简单连接最终没有按预期提供数量和总和,所以我需要某种形式的左或右外部连接,这是我所知道和尝试的。

我尝试了如下左连接:

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'

1 回复 | 直到 6 年前

Bill Karwin 6 年前

在进行外部连接之前,您需要生成餐馆的交叉产品来收费类型。

类似于以下内容(但我没有测试它):

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;

在这个例子中,R和D的交叉积是每个餐馆与每个收费类型交叉的结果。

当然,不是所有餐馆都有这些收费。因此,与BillingCharges的外部连接发现,对于餐厅和收费类型的每个组合,确实存在这些行。