我有一个表,我需要从中获取联系人。因此,我使用一个复杂的查询从"sms"表中检索所有联系人。
但是,它应该只计算提供的"imei"字段的记录,但是我的查询一直返回特定联系人的所有计数。
下面是使用的代码:
$sql = $this->db->query('
SELECT
t1.mnumber AS t1_number, t1.mcontent AS t1_content, t1.mcontact as t1_contact, SUM(t2.TOTAL) AS mTotal
FROM
sms t1
JOIN
(SELECT
mContent, mcontact, COUNT(mNumber) AS TOTAL, mnumber, MAX(mID) mID
FROM
sms
GROUP BY mContact) t2 ON t1.mcontact = t2.mcontact
AND t1.mid = t2.mid
WHERE t1.mIMEI = ' . (IMEI) . '
GROUP BY t1.mContact
ORDER BY t1.mid DESC');
return $sql->result();
格式更好,查询是:
SELECT t1.mnumber AS t1_number, t1.mcontent AS t1_content, t1.mcontact as t1_contact, SUM(t2.TOTAL) AS mTotal
FROM sms t1 JOIN
(SELECT mContent, mcontact, COUNT(mNumber) AS TOTAL, mnumber, MAX(mID) mID
FROM sms
GROUP BY mContact
) t2
ON t1.mcontact = t2.mcontact AND t1.mid = t2.mid
WHERE t1.mIMEI = ' . (IMEI) . '
GROUP BY t1.mContact
ORDER BY t1.mid DESC
问题是where
子句在错误的地方。为了影响计数,您需要在子查询中使用它。为了影响输出行,您需要在外部查询中使用它。我建议:
SELECT t1.mnumber AS t1_number, t1.mcontent AS t1_content, t1.mcontact as t1_contact, SUM(t2.TOTAL) AS mTotal
FROM sms t1 JOIN
(SELECT mContent, mcontact, COUNT(mNumber) AS TOTAL, mnumber, MAX(mID) as maxmid
FROM sms
WHERE sms.mIMEI = ' . (IMEI) . '
GROUP BY mContact
) t2
ON t1.mcontact = t2.mcontact AND t1.mid = t2.maxmid
GROUP BY t1.mContact
ORDER BY t1.mid DESC
我怀疑可能还有其他的简化,这取决于mid
和mcontact
在数据库结构方面的含义。
在两个SELECT
语句中都需要WHERE
子句。
SELECT t1.mnumber AS t1_number, t1.mcontent AS t1_content, t1.mcontact as t1_contact, SUM(t2.TOTAL) AS mTotal
FROM sms t1 JOIN
(SELECT mContent, mcontact, COUNT(mNumber) AS TOTAL, mnumber, MAX(mID) mID
FROM sms
WHERE mIMEI = ' . (IMEI) . '
GROUP BY mContact
) t2
ON t1.mcontact = t2.mcontact AND t1.mid = t2.mid
WHERE t1.mIMEI = ' . (IMEI) . '
GROUP BY t1.mContact
ORDER BY t1.mid DESC
如果两个地方都没有WHERE
子句,您将连接所有匹配mContact
的记录。即使是不符合t1.mIMEI = ' . (IMEI) . '
条件的
@Gordon Linoff给出的答案是正确的。然而,真正的问题在于表本身。
它存储所有的消息,但有时'mNumber'是一个国际电话号码,有时它是用户存储到手机的格式。
因此,当我使用'mNumber'字段加载'mContent'时,它只给我一个部分结果!!
所以还在寻找解决方案吗?也许是多个WHERE子句?