SQL Count或Max（SQLite）

发布于 2025-01-27 02:05:01 字数 324 浏览 2 评论 0原文

表就是这样：

ratio|user
  0.1|2
  0.3|2
  1  |2
  1  |2
  0.4|3
  0.7|3

查询应返回每个用户的比率为1的次数，或者如果该用户没有1比率，则最高比率。因此，给定上表的情况下，查询应返回：

2|2
3|0.7

因为用户2的比率有两个“ 1”值，而用户3的最高比率为0.7。

我目前拥有的是选择用户，从表计数（比率），其中比率= 1组乘用户;，但显然在要求的第二部分失败。我应该让第一个查询的结果填充一个子桌吗？

原文

The table is like this:

ratio|user
  0.1|2
  0.3|2
  1  |2
  1  |2
  0.4|3
  0.7|3

The query should return, for each user, either the number of times the ratio is 1, or, if there is no 1 ratio for that user, the highest ratio. So given the table above, the query should return:

2|2
3|0.7

because there are two "1" values for ratio for user 2, and the highest ratio for user 3 is 0.7.

What I have for now is select user,count(ratio) from table where ratio = 1 group by user; but obviously that fails at the second part of the requirement. Should I make a sub-table already populated with the result of that first query...?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

幸福％小乖 2025-02-03 02:05:01

使用案例表达式在计数和最高值之间进行选择：

SELECT user, CASE WHEN SUM(ratio = 1) > 0
                  THEN SUM(ratio = 1) ELSE MAX(ratio) END AS ratio
FROM yourTable
GROUP BY user;

Use a CASE expression to choose between the count and the highest value:

SELECT user, CASE WHEN SUM(ratio = 1) > 0
                  THEN SUM(ratio = 1) ELSE MAX(ratio) END AS ratio
FROM yourTable
GROUP BY user;

回复收藏 0 原文

少女七分熟 2025-02-03 02:05:01

我认为这可以通过2个步骤完成，例如：

select user,count(ratio) as column2 from table where ratio = 1 group by user
union 
select user,max(ratio) as column2 from table where ratio <> 1 group by user

I think this can be done in 2 steps like:

select user,count(ratio) as column2 from table where ratio = 1 group by user
union 
select user,max(ratio) as column2 from table where ratio <> 1 group by user

回复收藏 0 原文

~没有更多了~