数据库包含4个表格-费用,月,客户,发票。
month 表- (收费期)
(包含所有月份一月至十二月,只有两个月在这里缩短问题)
id | ttl | year
====================
1 | Jan | 1
2 | Feb | 1fee 表- (收费名称)
id | ttl | year
========================
1 | Annual | 1
2 | Monthly | 1
3 | Extra | 1customer 表- (客户名称)
id | ttl | year
========================
1 | Rahul | 1invoice 表- (发票列表)
id | cid | fid | mid
===========================
1 | 1 | 1 | 1
2 | 1 | 2 | 1在12个月内,每月和额外的费用必须在每月支付。
但年费是立即支付的,在该月份不固定,它可以支付1月或12月或任何一个月之间的月份。
现在我的要求是显示发票尚未准备好的相关月份的每一项费用(未在发票表中的每个月的费用名称),只需列出未支付的月份费用。所以我将LEFT JOIN与NULL结合使用
SELECT
customer.id AS cid,
month.id AS mid,
fee.id AS fid,
month.ttl AS mth,
fee.ttl AS fee,
FROM
fee
LEFT JOIN
month ON month.year = fee.year
LEFT JOIN
customer ON customer.year = fee.year
LEFT JOIN
invoice ON invoice.mid = month.id AND invoice.fid = fee.id
WHERE
customer.id=1 AND invoice.fid IS NULL结果是-
cid | mid | fid | mth | fee
=======================================
1 | 1 | 3 | Jan | Extra
1 | 2 | 1 | Feb | Annual
1 | 2 | 2 | Feb | Monthly
1 | 2 | 3 | Feb | Extra但是,我想要的--就像我上面说的,年费只付一次,所以如果年度在发票中,如果不包括,就排除这一点,以得到以下结果
cid | mid | fid | mth | fee
=======================================
1 | 1 | 3 | Jan | Extra
1 | 2 | 2 | Feb | Monthly
1 | 2 | 3 | Feb | Extra因为年费已经在一月交了。
所以我编辑了哪里的条件
WHERE
customer.id=1
AND
invoice.fid IS NULL
AND
NOT EXISTS (select invoice.fid from invoice where invoice.fid = 1 AND invoice.cid=customer.id)但是MySQL返回了一个空的结果集
发布于 2018-10-06 09:37:29
invoice和客户id的customer表之间的一个连接条件。month和fee表之间的连接,您将在fee.ttl = 'Annual'时获得多个行。UNION ALL。首先选择查询考虑fee.ttl != 'Annual'。第二,select只考虑那些fee.ttl = 'Annual'的情况。在第二个选择查询中,我们不加入month表,从而避免了“年度”的多行。ttl字段相匹配,而不是fid。由于年份不同,您似乎将有多个“年度”的fid值。因此,检查fid = 1只会达到year = 1的目的。尝试以下几点:
(SELECT
customer.id AS cid,
month.id AS mid,
fee.id AS fid,
month.ttl AS mth,
fee.ttl AS fee
FROM
fee
INNER JOIN
month ON month.year = fee.year
INNER JOIN
customer ON customer.year = fee.year
LEFT JOIN
invoice ON invoice.mid = month.id AND
invoice.fid = fee.id AND
invoice.cid = customer.id
WHERE
customer.id = 1 AND
fee.ttl != 'Annual' AND
invoice.fid IS NULL
)
UNION ALL
(SELECT
customer.id AS cid,
'' AS mid,
fee.id AS fid,
'' AS mth,
fee.ttl AS fee
FROM
fee
INNER JOIN
customer ON customer.year = fee.year
LEFT JOIN
invoice ON invoice.fid = fee.id AND
invoice.cid = customer.id
WHERE
customer.id = 1 AND
fee.ttl = 'Annual' AND
invoice.fid IS NULL
)https://stackoverflow.com/questions/52677334
复制相似问题