主页 / dba / 问题 / 177172
Accepted
humphrey
Asked: 2017-06-25 06:38:09 +0800 CST

如何在mysql中只选择本月没有存款的会员

  • 772

我正在从成员和费用这两个表中获取数据。我想只选择那些本月没有存款的成员。这是我尝试过的，但结果不是我想要得到的。任何帮助将不胜感激 。我正在使用 mysql 。

SELECT
    members.memberid, 
    members.companyid, 
    members.name, 
    members.initials, 
    members.surname, 
    members.datejoined,
    members.usercode, 
    members.address, 
    members.cell, 
    members.pic, 
    members.idnumber, members.rank, 
    members.address2,
    fees.feestype,fees.amountpaid,
    fees.companyid,fees.paiddate,
    fees.usercode
FROM 
    members
    INNER JOIN fees
        ON members.memberid = fees.memberid
WHERE 
        MONTH(fees.paiddate) < MONTH(CURDATE())
    AND YEAR(fees.paiddate)  < YEAR(CURDATE())
mysql

2 个回答

  1. Best Answer

    您需要以不同的方式进行：选择不是本月支付IN的成员集合的所有成员：

    SELECT
    memberid, name  /* , and anything you need */
FROM
    members
    /* JOIN fees ON fees.memberid = members.memberid */
WHERE
    memberid NOT IN
    (
    /* members who paid something this month */ 
    SELECT 
        memberid
    FROM
        fees
    WHERE
        YEAR(paiddate)  = YEAR(curdate())  AND
        MONTH(paiddate) = MONTH(curdate())
    ) ;

    dbfiddle在这里

    您实际查询在当前年份之前的年份和当前年份之前的月份支付过费用的特定成员。

    作为第二种选择，您可以使用NOT EXISTS：

    SELECT
    memberid, name  /* , and anything you need */
FROM
    members
    /* JOIN fees ON fees.memberid = members.memberid */
WHERE
    NOT EXISTS
    (
    /* any payment from this specific member this month */ 
    SELECT 
        1
    FROM
        fees
    WHERE
        fees.memberid = members.memberid   AND
        YEAR(paiddate)  = YEAR(curdate())  AND
        MONTH(paiddate) = MONTH(curdate())
    ) ;

    dbfiddle在这里

    还有另一个（我认为不太清楚）替代方案，执行LEFT JOIN并设置一个WHERE条件，该条件将过滤掉实际从 JOIN 右侧检索某些内容的行（即：仅使用所有列的行WHERE，特别memberid是 NULL在右侧）：

    SELECT
    members.memberid, members.name  /* , and anything you need */
FROM
    members
    /* JOIN fees ON fees.memberid = members.memberid */
    LEFT JOIN /* Imperative to be LEFT JOIN */
    (
    /* members who paid this month */ 
    SELECT 
        fees.memberid
    FROM
        fees
    WHERE
        YEAR(paiddate)  = YEAR(curdate())  AND
        MONTH(paiddate) = MONTH(curdate())
    ) AS s0 
    ON s0.memberid = members.memberid
WHERE
    s0.memberid IS NULL /* The LEFT JOIN produced nothing on the right side */

    检查使用 LEFT JOIN 或 NOT EXISTS 之间的最佳实践

    并在此处检查所有dbfiddle

    在关系代数中，这称为反连接

    更新

    从Kondybas的解决方案更进一步，正如 ypercube 在对Kondibas回答的评论中所指出的，这可以进一步简化为：

    SELECT
    members.memberid, members.name  /* , and anything you need */
FROM
    members
    /* JOIN fees f1 ON fees.memberid = members.memberid */
    LEFT JOIN fees f2 ON 
        f2.memberid = members.memberid AND
        f2.paiddate BETWEEN date_format(curdate() ,'%Y-%m-01') AND curdate()
WHERE
    f2.memberid IS NULL ;

    像这样写条件（即：没有应用到 paiddate 的函数），它将使用一个索引，例如：

    CREATE INDEX idx_member_date ON fees (memberid, paiddate);

    ...从执行计划中可以看出：

    编号 | 选择类型 | 表 | 类型 | 可能的键 | 钥匙 | 密钥长度 | 参考 | 行 | 额外的                   
-: | :------------ | :------ | :--- | :---------------- | :---------------- | :------ | :---------------------------------------- | ---： | :------------------------
 1 | 简单 | 成员 | 所有 | 空            | 空            | 空    | 空                                         | 2 |                         
 1 | 简单 | 费用 | 参考 | idx_member_date | idx_member_date | 5 | fiddle_ZCXLEXEURTHEWEPFKPOV.members.memberid | 1 | 在哪里使用；使用索引

    dbfiddle在这里

    • 4

  2. joanolo解决方案的第三种替代方法是从表中获取带有 NULL 的记录LEFT JOIN：

    SELECT w.id
  FROM members AS w
  LEFT JOIN fees AS z ON w.memberid = z.memberid
 WHERE DATE_FORMAT(z.paiddate, '%Y%M') = DATE_FORMAT(NOW(), '%Y%M')
    OR z.paiddate IS NULL
HAVING z.paiddate IS NULL
;

    正如yupecube建议的那样，查询可以简化为：

    SELECT w.id
  FROM members AS w
  LEFT JOIN fees AS z
         ON w.memberid = z.memberid 
        AND DATE_FORMAT(z.paiddate, '%Y%M') = DATE_FORMAT(NOW(), '%Y%M')
 WHERE z.paiddate IS NULL
;

    唯一的缺点是复杂功能条件下的 JOIN 通常非常慢。

    • 3

相关问题