SELECT pid,COUNT(*) AS total FROM `pre_forum_hotreply_member` WHERE tid = 181501 and attitude = 1 GROUP BY pid 如何优化?
时间: 2024-01-12 13:03:32 浏览: 30
可以考虑以下优化方式:
1. 索引优化:为 `pre_forum_hotreply_member` 表中的 `tid` 和 `attitude` 字段分别添加索引,可以加快查询速度。
2. 子查询优化:可以将子查询转换成 JOIN 操作,例如:
```
SELECT pid, COUNT(*) AS total
FROM `pre_forum_hotreply_member` AS a
JOIN (
SELECT pid
FROM `pre_forum_hotreply_member`
WHERE tid = 181501 and attitude = 1
GROUP BY pid
) AS b ON a.pid = b.pid
GROUP BY pid
```
这样可以避免多次查询和排序操作,提高查询效率。
3. 分页查询优化:如果需要进行分页查询,可以使用 LIMIT 关键字限制返回结果的数量,同时尽可能减少查询返回的记录数,以提高查询速度。例如:
```
SELECT pid, COUNT(*) AS total
FROM `pre_forum_hotreply_member`
WHERE tid = 181501 and attitude = 1
GROUP BY pid
LIMIT 0, 10
```
这样可以只返回前 10 条记录,减少查询时间。
以上是一些基本的优化方式,具体优化方案应该根据具体情况来定。
相关问题
select * FROM sockpuppet_cust_extend where partner_id = '1000163' and HAVING COUNT(cbm_work_id) > 0
在的SQL语句中,`HAVING` 子句应该紧跟在 `GROUP BY` 子句之后,用于在分组后对结果进行过滤。在您的语句中,似乎缺少 `GROUP BY` 子句。如果您想查询 `sockpuppet_cust_extend` 表中 `partner_id` 为 '1000163' 且 `cbm_work_id` 记录数大于 0 的记录,可以使用以下SQL语句:
```
SELECT * FROM sockpuppet_cust_extend WHERE partner_id = '1000163' GROUP BY partner_id HAVING COUNT(cbm_work_id) > 0;
```
这条语句首先通过 `WHERE` 子句筛选出 `partner_id` 为 '1000163' 的记录,然后通过 `GROUP BY` 子句对这些记录进行分组,最后通过 `HAVING` 子句筛选出 `cbm_work_id` 记录数大于 0 的记录。
SELECT COUNT( * ) FROM ( SELECT t.* FROM ( SELECT p.* FROM package_channel_price_plan p a WHERE a.sys_default = 0 UNION SELECT c.* FROM package_commodity_main_info b LEFT JOIN package_channel_price_plan c ON b.plan_code = c.plan_code WHERE 1 = 1 AND b.commodity_code = '3323070713301825957052' ) AS t WHERE 1 = 1 AND t.plan_status = '1' ORDER BY t.updated_time DESC ) TOTAL 这个sql错在哪里
这个 SQL 语句中的错误在于使用了不正确的表别名和列名。以下是修正后的 SQL 语句:
SELECT COUNT(*) FROM (
SELECT t.* FROM (
SELECT p.* FROM package_channel_price_plan p
WHERE p.sys_default = 0
UNION
SELECT c.* FROM package_commodity_main_info b
LEFT JOIN package_channel_price_plan c ON b.plan_code = c.plan_code
WHERE 1 = 1 AND b.commodity_code = '3323070713301825957052'
) AS t
WHERE 1 = 1 AND t.plan_status = '1'
ORDER BY t.updated_time DESC
) AS TOTAL