我有以下mysql表。
1. members
----------
|id |name|
----------
|001|aaa |
|002|bbb |
|003|ccc |
|004|ddd |
----------
2. membershipfees
----------------
|id |date |
----------------
|001|2012-01-01|
|002|2012-01-01|
----------------
我想显示当月未缴纳费用的会员名单。我的php代码是
SELECT Members.id, Members.Name FROM Members
LEFT JOIN MembershipFees
ON Members.id = MembersipFees.id
WHERE DATE_FORMAT(MembershipFees.Date, '%Y%m') <> DATE_FORMAT(NOW(), '%Y%m')
ORDER BY Members.id ASC
但它显示0行。有什么建议吗。
您需要设置MembersipFees.id IS NULL条件为where子句。另外，如果date字段是varchar列，那么您应该使用STR_TO_DATE函数。
SELECT Members.id, Members.Name
FROM Members
LEFT JOIN membershipfees
ON Members.id = membershipfees.id
AND DATE_FORMAT(STR_TO_DATE(membershipfees.`Date`, '%d-%m-%Y'), '%Y%m') =
DATE_FORMAT(NOW(), '%Y%m')
WHERE membershipfees.id IS NULL
ORDER BY Members.id ASC;
请在此处查看SQLFIDDLE演示
我现在不能测试它，但请试着让我知道它是否适合你：
SELECT m.id, m.Name FROM Members m
Where not exists
(select 1 from membershipfees mm
where
mm.Date > DATE_FORMAT(NOW(), '%Y-%m')
and mm.id = m.id)
更新：
它对我有效
试试这个：
您只需在查询中添加membershipfees.id IS NULL条件即可
SELECT members.id, members.Name
FROM members
LEFT outer JOIN membershipfees
ON members.id = membershipfees.id
and DATE_FORMAT(membershipfees.Date, '%Y%m')
<> DATE_FORMAT(NOW(), '%Y%m')
where membershipfees.id IS NULL
ORDER BY members.id ASC
尝试此查询。。
SELECT Members.id, Members.Name FROM Members where id not in (select id from membershipfees WHERE DATE_FORMAT(MembershipFees.Date, '%Y%m') <> DATE_FORMAT(NOW(), '%Y%m') ORDER BY Members.id ASC)