新手求问一个简单的查询优化问题。

select ID,rType,rCount,
TotalCount, temp.rCount/temp2.TotalCount as rRate from(
    select ID,rType,count(*) rCount from item_list
	where rType=5 and CreateDate>=@StartDate and CreateDate<@EndDate
	group by ID,rType
) temp,(
    select ID ID2 ,count(*) TotalCount from item_list
	where CreateDate>=@StartDate and CreateDate<@EndDate
	group by ID
) temp2 where temp.ID = temp2.ID2
order by temp.rType desc,rareRate desc

目前这条语句可以正常工作，但是显然，两次查询查的都是同一个表，而且第一次查询的 temp 其实就只是 temp2 的查询加上了一个 rType=5 的限制条件。

所以想问，这种情况的查询，是不是应该有更好的办法，只对原表一次查询，就获取到我需要的全部信息呢？还是说这种情况本身就应该需要对原表 2 次不同的查询（我感觉应该不会吧…）
多谢指导！

表的内容大概是这样：

ID	itemName	rType	CreateDate
1	item1	5	2022 年 1 月 29 日
1	item2	4	2022 年 1 月 29 日
2	item1	5	2022 年 1 月 29 日
1	item2	4	2022 年 1 月 29 日
1	item3	3	2022 年 1 月 29 日
2	item4	3	2022 年 1 月 29 日
3	item5	4	2022 年 1 月 29 日

rtype

查询

createdate

temp

10 条回复 • 2022-01-30 12:14:10 +08:00

512357301

2022-01-29 12:53:39 +08:00 via Android

逻辑不复杂，可以用一条语句实现的，核心就是用 sum 函数搭配 if 函数。
思路如下(主要看个思路哈，sql 可能没法直接执行)

select ID
,sum(if(rType=5,1,0) as fiveStarCnt
,count(*) TotalCount
,sum(if(rType=5,1,0)/count(*) as rRate
from item_list
where CreateDate>=@StartDate
and CreateDate<@EndDate
group by ID

512357301

2022-01-29 12:59:19 +08:00 via Android

emmm ，刚才 sql 少了两个括号，尴尬。。。
修改如下：
select ID
,sum(if(rType=5,1,0)) as fiveStarCnt
,count(*) TotalCount
,sum(if(rType=5,1,0))/count(*) as rRate
from item_list
where CreateDate>=@StartDate
and CreateDate<@EndDate
group by ID

rabbbit

2022-01-29 14:17:17 +08:00

SELECT
　 ID, TotalCount, rCount, t.rCount/t.TotalCount AS rRate
FROM (
　 SELECT
　　 ID, rType,
　　 COUNT(ID) AS TotalCount,
　　 SUM(CASE rType WHEN 5 THEN 1 ELSE 0 END) AS rCount
　　 FROM ` item_list` GROUP BY ID
) t

natsukage

2022-01-29 23:28:39 +08:00

@512357301 多谢解答！很有启发，因为我对 SQL 只是粗浅了解，现在才第一次知道原来 Sum()是这么使用的。感谢指导！
不过这个方法我实践了一下，还是有个问题就是它和前面我自己折腾的例子不一样，最后查询出的表，会把 rCount=0 也就是完全没有出过货的角色也统计进去，所以最后我还是在外面套了一层 select where rCount>0 。不过除此以外的内容都非常详细，让我受益匪浅。

natsukage

2022-01-29 23:33:55 +08:00

@rabbbit 感谢回答！这条回复也非常有启发，一开始我想的是有没有可能只用一条 select 完成全部，但是发现 2 层 select 的查询确实读起来清晰得多。而且如同前面回复的，因为我原本的查询中会过滤掉所有完全没有出货（ rCount=0 ）的结果，所以如果只是一次 select 的话查询出来的临时表里面还是带有不需要的信息。看到您的这条回复意识到外面再套一层 select where rCount>0 就可以解决这个问题了。

rabbbit

2022-01-29 23:39:36 +08:00

也可以把过滤写里头, 例如:

SELECT
　 ID, TotalCount, rCount, t.rCount/t.TotalCount AS rRate
FROM (
　 SELECT
　　 ID, rType,
　　 COUNT(ID) AS TotalCount,
　　 SUM(CASE rType WHEN 5 THEN 1 ELSE 0 END) AS rCount
　 FROM ` item_list`
　 GROUP BY ID
　 HAVING rCount>0
) t

natsukage

2022-01-29 23:53:27 +08:00

@rabbbit …原来 HAVING 是这么用的 233 。这确实完全是我知识盲区了，之前从来没用过…
多谢解答

leetcode2020

2022-01-30 10:32:34 +08:00

SELECT
tt.ID,
count( tt.ID ) AS ttCountt,
CONCAT(
ROUND( SUM( CASE tt.rType WHEN 5 THEN 1 ELSE 0 END ) / COUNT( tt.ID ), 2 ) * 100,
'%'
) rRate
FROM
item_list tt
GROUP BY
tt.ID;

512357301

2022-01-30 12:11:55 +08:00 via Android

@natsukage 你原来的查询方法其实本质上是内连接，而 5 星的货品估计出货次数都不为 0 在内连接的时候就无形中过滤掉了。
如果把内连接换成右连接或者全连接，那等于 0 那部分还是能查出来的

512357301

2022-01-30 12:14:10 +08:00 via Android

@natsukage 你的那个两步查询你可以复核下，看下是不是还缺少“完全没有 5 星货品的玩家”，这部分可能也被无形中过滤了