gpt4 book ai didi

php - 复杂的 SQL 查询 JOIN 和 WHERE

转载 作者:行者123 更新时间:2023-11-29 02:28:42 25 4
gpt4 key购买 nike

我有一张表,我需要从中获取联系人。所以我使用一个复杂的查询从我的“短信”表中检索所有联系人。

但是,它应该只计算提供的“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

我怀疑可能还有其他简化,具体取决于 midmcontact 在数据库结构方面的含义。

关于php - 复杂的 SQL 查询 JOIN 和 WHERE,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/16465316/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com