SELECT item_type_id, COUNT(item_type_id ) AS typeCount FROM prosper_item_base_info WHERE item_id IN ( '1116704448291274752', '1116704567245930496', '1116704728433033216', '1116704644853137408', '1116704823471767552', '1116704898314928128', '1116705032100642816', '1116704823471767552', '1116704898314928128' ) GROUP BY item_type_id ORDER BY typeCount DESC LIMIT 10,在这段sql中重复的item_id就统计一次,我想重复的也统计上,如何优化sql
时间: 2023-12-24 16:05:27 浏览: 28
如果你想要重复的 item_id 也被统计,可以使用 COUNT(DISTINCT item_id) 来统计不同的 item_id 数量,这样重复的 item_id 也会被计算在内。以下是修改后的 SQL 查询语句:
```
SELECT item_type_id, COUNT(DISTINCT item_id) AS typeCount
FROM prosper_item_base_info
WHERE item_id IN ('1116704448291274752', '1116704567245930496', '1116704728433033216', '1116704644853137408', '1116704823471767552', '1116704898314928128', '1116705032100642816', '1116704823471767552', '1116704898314928128')
GROUP BY item_type_id
ORDER BY typeCount DESC
LIMIT 10
```
这样,查询结果中会统计 item_id 的不同数量,并按照 item_type_id 进行分组,统计每个 item_type_id 对应的不同 item_id 的数量,最后将结果按照不同 item_id 的数量从大到小排序,返回前 10 条记录。
相关问题
SELECT item_type_id, COUNT(item_type_id ) AS typeCount, FROM prosper_item_base_info WHERE item_id IN ( '1116704448291274752', '1116704567245930496', '1116704728433033216', '1116704644853137408', '1116704823471767552', '1116704898314928128', '1116705032100642816', '1116704823471767552', '1116704898314928128' ) GROUP BY item_type_id ORDER BY typeCount DESC LIMIT 10哪里出错了
在这个 SQL 查询语句中,有一个多余的逗号,它在 COUNT(item_type_id ) AS typeCount, 语句中出现,应该将它删除。同时,在这个查询语句中,item_id IN (...) 子句中出现了重复的 item_id 值,需要将其去重。以下是修改后的 SQL 查询语句:
```
SELECT item_type_id, COUNT(item_type_id) AS typeCount
FROM prosper_item_base_info
WHERE item_id IN ('1116704448291274752', '1116704567245930496', '1116704728433033216', '1116704644853137408', '1116704823471767552', '1116704898314928128', '1116705032100642816')
GROUP BY item_type_id
ORDER BY typeCount DESC
LIMIT 10
```
SELECT item_type_id, COUNT(item_type_id ) AS typeCount FROM prosper_item_base_info WHERE item_id IN ( '1116704448291274752', '1116704567245930496', '1116704728433033216', '1116704644853137408', '1116704823471767552', '1116704898314928128', '1116705032100642816', '1116704823471767552', '1116704898314928128' ) GROUP BY item_type_id ORDER BY typeCount DESC LIMIT 10,在这段sql中重复的item_id就统计一次,我想重复的item_id也统计上,如何优化sql
如果你想要重复的 item_id 也被统计,可以在 SELECT 子句中同时统计 item_id 和 item_type_id,然后在 GROUP BY 子句中同时按照这两个字段进行分组,以下是修改后的 SQL 查询语句:
```
SELECT item_type_id, item_id, COUNT(*) AS typeCount
FROM prosper_item_base_info
WHERE item_id IN ('1116704448291274752', '1116704567245930496', '1116704728433033216', '1116704644853137408', '1116704823471767552', '1116704898314928128', '1116705032100642816', '1116704823471767552', '1116704898314928128')
GROUP BY item_type_id, item_id
ORDER BY typeCount DESC
LIMIT 10
```
这样,查询结果中会同时统计 item_id 和 item_type_id,按照这两个字段进行分组,最后将结果按照出现次数从大到小排序,返回前 10 条记录。这样就可以统计重复的 item_id 出现的次数了。
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![docx](https://img-home.csdnimg.cn/images/20210720083331.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)