Mysql的选择在哪里NOT EXISTS

问题描述：

表名学生::

studentid  studentname 
1001   Charlie Sheen 
1002   John Cryer

表名:: studentpayment1

paymentid studentid fee_month fee_year totalamount 
    1234   1001  February 2012  $500 
    4321   1002  January  2012  $1500

表名:: studentpayment2

id   pid  fee_type  fee_amount 
1   1234  Monthly Fee   $500 
2   4321  Exam Fee    $1500

现在，我试图找出2012年2月 - 2012年未支付“月费”的学生的名字。

我试过下面的代码，但它没有生成正确的信息。

$year="2012"; $month="February"; $fee_type="Monthly Fee"; 


SELECT DISTINCT studentid, 
       studentname 
FROM student 
WHERE NOT EXISTS (SELECT * 
        FROM  studentpayment1 
        JOIN studentpayment2 
        ON  studentpayment1.paymentid = studentpayment2.pid 
        WHERE fee_month = '$month' 
        AND fee_type = '$fee_type' 
        AND fee_year = '$year' 
        AND student.studentid = studentpayment1.studentid) 
LIMIT $perPage

如何解决此查询？

发生了什么事？ – 2012-02-20 17:53:02

@black_belt尝试此查询：'SELECT studentname FROM studentname，studentpayment1，studentpayment2 WHERE student.studentid = studentpayment1.studentid AND pid = paymentid AND fee_type！=“月费”AND fee_year =“2012”AND fee_month =“February”' – SIFE 2012-02-20 17:56:53

答

你的变量不应该是单引号内：

"WHERE fee_month = '".$month."' "

更妙的是绑定的变量。

论结合在这里：How to bind SQL variables in Php?

OP可能需要一个绑定变量的例子。 – 2012-02-20 17:56:52

答

我还没有尝试运行实时此查询，但这应该帮助你。

select distinct studentid from student where studentid NOT EXISTS (select studentid from studentpayment1 where fee_month = '$month' 
        AND fee_type = '$fee_type' 
        AND fee_year = '$year')

或

select distinct studentid from student where NOT EXISTS (select studentid from studentpayment1 where fee_month = '$month' 
        AND fee_type = '$fee_type' 
        AND fee_year = '$year')

答

如果您的查询是正确的（我还没有看它）查询波纹管应该工作

SELECT DISTINCT studentid,studentname FROM student WHERE 
studentid NOT IN (SELECT studentid FROM studentpayment1 JOIN studentpayment2 ON studentpayment1.paymentid=studentpayment2.pid WHERE 
fee_month='$month' AND fee_type='$fee_type' AND fee_year='$year' AND student.studentid=studentpayment1.studentid) LIMIT $perPage

感谢您的回复。我尝试了这里发布的所有解决方案，但没有一个能够工作。但经过12个小时的工作后，我发现我的db查询没问题，我在mysql查询上面的一些代码出现了一些问题。我很抱歉创建此帖子。我应该删除这篇文章吗？ – 2012-02-21 05:56:54

答

尝试使用LEFT JOIN的替代：

SELECT student.studentid, student.studentname FROM student 
LEFT JOIN studentpayment1 ON studentpayment1.studentid = student.studentid 
    AND studentpayment1.fee_month = ? 
    AND studentpayment1.fee_year = ? 
LEFT JOIN studentpayment2 ON studentpayment2.pid = studentpayment1.paymentid 
    AND studentpayment2.fee_type = ? 
WHERE studentpayment2.id IS NULL

确保将索引放在f ee_month，fee_year和fee_type，并全部替换？与你的变量。

OP可能会赞赏解释为什么这种方法在原始失败的情况下起作用。什么是添加索引的好处/缺点。 – 2012-02-20 18:00:32

答

看起来这会做的事：

$year="2012"; $month="February"; $fee_type="Monthly Fee"; 

$students_with_debt = "SELECT name FROM student WHERE studentid NOT IN (SELECT studentid FROM studentpayment1 p1 INNER JOIN studentpayment2 p2 ON p1.paymentid = p2.pid WHERE fee_month = '$month' AND p1.fee_year = '$year' AND p2.fee_type = '$fee_type');"

答

这会给你所有谁在2012年2月什么事也没，没什么支付的学生，还是2月份没有月租费，2011

SELECT * FROM student s 
    LEFT JOIN studentpayment1 sp1 
     ON s.studentid = sp1.studentid 
     AND ((sp1.fee_month = 'February' AND sp1.fee_year = '2012') OR sp1.fee_month is null) 
    LEFT JOIN studentpayment2 sp2 
     ON sp1.paymentid = sp2.pid AND (sp2.fee_type = 'Monthly Fee' OR sp2.fee_type is null) 
    WHERE sp1.fee_month is null or sp2.fee_type is null

祝你好运

这里是一些很好的模式建议：将选项卡上的所有主键改为'id'并且与你一致命名foriegn键：st的主键udent表应该只是'id'在studentpayment1表中，它应该有一个名为student_id的列。 studentpayment2表应该有一个指向studentpayment1的列，称为studentpayment1_id而不是'pid'。

Mysql的选择在哪里NOT EXISTS

相关推荐