需要获取所有未支付当月费用的学生

问题描述：

I have two tables

Students table :

id   studentname   admissionno
3    test3               3
2    test2               2
1    test                1

2nd table is fee :

id   studentid  created
1       3       2015-06-06 22:55:34
2       2       2015-05-07 13:32:48
3       1       2015-06-07 17:47:46

I need to fetch the students who haven't paid for the current month,
I'm performing the following query:

SELECT studentname FROM students 
    WHERE studentname != (select students.studentname from students
    JOIN submit_fee
    ON (students.id=submit_fee.studentid)
    WHERE MONTH(CURDATE()) = MONTH(submit_fee.created)) ;

and I'm getting error:

'#1242 - Subquery returns more than 1 row'

Can you tell me what the correct query is to fetch all the students who haven't paid for the current month?

我有两个表 p>

学生表： p>

  id studentname admissionno 
3 test3 3 
2 test2 2 
1 test 1 
  code>  pre> 
 
 第二张表是费用： p> \  n 
 
  id studentid created 
1 2015-06-06 22:55:34 
2 2 2015-05-07 13：32：48 
3 2015-06-07 17:47：  46 
  code>  pre> 
 
 我需要获取当月没有付款的学生，
 
我正在执行以下查询： p>  
 
 
  SELECT studentname FROM students 
 WHERE studentname！=（从学生中选择students.studentname 
 JOIN submit_fee 
 ON（students.id = submit_fee.studentid）
 WHERE MONTH（CURDATE（  ））= MONTH（submit_fee.created））; 
  code>  pre> 
 
 我收到错误： p> 
 
 
 
 '＃1242  - 子查询返回超过1行' p> 
  blockquote> 
 
 
您能告诉我获取所有学生的正确查询是什么 谁没有支付当月？ p> 
  div>

答

Use not in, please try query below :

SELECT s.*
FROM students s
WHERE s.id NOT IN ( SELECT sf.studentid FROM studentfees sf WHERE month(sf.created) = EXTRACT(month FROM (NOW())) )

答

You want to use not exists or a left join for this:

select s.*
from students s
where not exists (select 1
                  from studentfees sf
                  where s.id = sf.studentid and
                        sf.created >= date_sub(curdate(), interval day(curdate) - 1) and
                        sf.created < date_add(date_sub(curdate(), interval day(curdate) - 1), 1 month)
                 )

Note the careful construction of the date arithmetic. All the functions are on curdate() rather than on created. This allows MySQL to use an index for the where clause, if one is appropriate. One error in your query is the use of MONTH() without using YEAR(). In general, the two would normally be used together, unless you really want to combine months from different years.

Also, note that paying or not paying for the current month may not really answer the question. What if a student paid for the current month but missed the previous month's payment?

需要获取所有未支付当月费用的学生

相关推荐