我有一个MySQL查询，我运行了一个while循环，为我提供了一个多维关联数组。
$query2 = "SELECT DISTINCT
soc.rowid as id,
fc.paye as paid
FROM invoices fc
LEFT JOIN customers soc on fc.fk_soc = soc.rowid
AND fc.paidBy < '" . $date . "'";
$unpaidInv = $db->query($query2);
while($row1 = mysqli_fetch_array($unpaidInv, MYSQLI_ASSOC)){
$params1[] = $row1;
}
var_dump($params1);
var_dump结果如下:
array(10) {
[0]=>
array(2) {
["id"]=>
string(2) "19"
["paid"]=>
string(1) "1"
}
[1]=>
array(2) {
["id"]=>
string(2) "22"
["paid"]=>
string(1) "1"
}
[2]=>
array(2) {
["id"]=>
string(2) "21"
["paid"]=>
string(1) "0"
}
[3]=>
array(2) {
["id"]=>
string(2) "23"
["paid"]=>
string(1) "1"
}
[4]=>
array(2) {
["id"]=>
string(2) "21"
["paid"]=>
string(1) "1"
}
[5]=>
array(2) {
["id"]=>
string(2) "32"
["paid"]=>
string(1) "1"
}
[6]=>
array(2) {
["id"]=>
string(2) "22"
["paid"]=>
string(1) "0"
}
[7]=>
array(2) {
["id"]=>
string(2) "23"
["paid"]=>
string(1) "0"
}
[8]=>
array(2) {
["id"]=>
string(2) "32"
["paid"]=>
string(1) "0"
}
[9]=>
array(2) {
["id"]=>
NULL
["paid"]=>
string(1) "0"
}
}
string(120) "
当前有相同id的数组项具有不同的"paid"值。例如:Array item2,id→21,支付→0- Array item4,id→21,支付→1.
我需要我的数组只返回全部为1的结果。所以如果array有id->21两次，但已付费项已付费->1和支付→0它不应该出现在数组中
只包含全部为1的数组项。
我会在请求中尝试这样做:
SELECT DISTINCT
soc.rowid as id,
fc.paye as paid
FROM invoices fc
LEFT JOIN customers soc on fc.fk_soc = soc.rowid
WHERE fc.paidBy < '" . $date . "'"
AND soc.rowid NOT IN (SELECT DISTINCT
soc2.rowid as id,
FROM invoices fc2
LEFT JOIN customers soc2 on fc2.fk_soc = soc2.rowid
WHERE fc2.paidBy < '" . $date . "'"
AND fc2.paye = '0');
为了更精确地适应，但考虑到这一点，您将只保留不在子查询中的行，子查询检索值为0的行
对于任何对可用解决方案感兴趣的人，请参阅下文。
SELECT DISTINCT
soc.rowid as id
FROM invoices fc
LEFT JOIN customers soc on fc.fk_soc = soc.rowid
WHERE soc.rowid IS NOT NULL
AND fc.paidBy < '" . $date . "'"
AND soc.rowid NOT IN(
SELECT DISTINCT
soc.rowid as id
FROM invoices fc
LEFT JOIN customers soc on fc.fk_soc = soc.rowid
AND fc.paye = '0'
WHERE soc.rowid IS NOT NULL
AND fc.paidBy < '" . $date . "'");