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

不存在返回空结果集

mysql

Dipak · 技术社区 · 6 年前

数据库包含4个表-费用,月份,客户,发票。

month (收费期)

id  |   ttl |   year
====================
1   |   Jan |   1
2   |   Feb |   1

表- fee

id  |   ttl     |   year
========================
1   |   Annual  |   1
2   |   Monthly |   1
3   |   Extra   |   1

表- customer

id  |   ttl     |   year
========================
1   |   Rahul   |   1

表- invoice (发票列表)

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返回了一个空的结果集

1 回复 | 直到 6 年前

Madhur Bhaiya 6 年前

您缺少之间的联接条件 invoice 和 customer 为客户准备的桌子 id .
由于 month 和 fee 表中,您将获得多行的情况下 fee.ttl = 'Annual' .
UNION ALL . 第一选择查询 fee.ttl != 'Annual' fee.ttl='年度' . 在第二个Select查询中,我们不与 月 表,从而避免了“Annual”的多行。
请注意 我把“年度”和 ttl 字段,而不是 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
)