下面这是一个表invoice, 其中id表示invoice表的主键,mid是用户表(member)的id,paied_status 是支付的状态位,1代表已经支付,period代表支付的年份
+----+-----+--------------+--------+
| id | mid | paied_status | period |
+----+-----+--------------+--------+
| 1 | 1 | 0 | 2009 |
| 2 | 1 | 1 | 2010 |
| 3 | 2 | 0 | 2006 |
| 4 | 2 | 0 | 2007 |
| 5 | 2 | 1 | 2009 |
| 6 | 2 | 1 | 2010 |
| 7 | 3 | 1 | 2008 |
| 8 | 3 | 0 | 2009 |
| 9 | 3 | 0 | 2010 |
| 10 | 4 | 0 | 2008 |
| 11 | 4 | 1 | 2009 |
| 12 | 5 | 1 | 2010 |
| 13 | 6 | 0 | 2010 |
| 14 | 7 | 1 | 2008 |
| 15 | 7 | 1 | 2010 |
| 16 | 8 | 0 | 2008 |
| 17 | 8 | 1 | 2010 |
+----+-----+--------------+--------+
求一条SQL语句,希望获取所有2010新支付的用户(mid),这里新支付的意思是在2010年支付成功的,并且在2009年没有支付或者是支付不成功的用户,谢谢大侠们! 正确返回的结果应该是:1,5,7,8
+----+-----+--------------+--------+
| id | mid | paied_status | period |
+----+-----+--------------+--------+
| 1 | 1 | 0 | 2009 |
| 2 | 1 | 1 | 2010 |
| 3 | 2 | 0 | 2006 |
| 4 | 2 | 0 | 2007 |
| 5 | 2 | 1 | 2009 |
| 6 | 2 | 1 | 2010 |
| 7 | 3 | 1 | 2008 |
| 8 | 3 | 0 | 2009 |
| 9 | 3 | 0 | 2010 |
| 10 | 4 | 0 | 2008 |
| 11 | 4 | 1 | 2009 |
| 12 | 5 | 1 | 2010 |
| 13 | 6 | 0 | 2010 |
| 14 | 7 | 1 | 2008 |
| 15 | 7 | 1 | 2010 |
| 16 | 8 | 0 | 2008 |
| 17 | 8 | 1 | 2010 |
+----+-----+--------------+--------+
求一条SQL语句,希望获取所有2010新支付的用户(mid),这里新支付的意思是在2010年支付成功的,并且在2009年没有支付或者是支付不成功的用户,谢谢大侠们! 正确返回的结果应该是:1,5,7,8
SELECT MID FROM invoice WHERE period=2010 AND paied_status=1
AND MID NOT IN (SELECT 1 FROM invoice WHERE period=2009)
from invoice a
where period='2010' and paied_status=1
and not exists (select 1 from invoice where `mid`=a.`mid` and period='2009' and paied_status=1)