SQL:计算一个表中的一列除以另一个表的总数并从大到小排序,如果有小数,则保留两位

本文深入解析了SQL中select语句的高级用法,包括如何使用count(1)统计记录数,trunc函数处理小数,以及orderby进行排序。同时介绍了groupby用于数据分组的方法,帮助读者提升数据处理能力。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

select c.name, trunc((c.num / b.total) * 100, 2) amount
  from (select b.name, a.jfdd, count(1) num
          from yy a
          left join xx b on b.id = a.jfdd
         where a.jflx = '1'
         group by b.name, a.jfdd) c,
       (select count(1) total from nn) b
 order by amount desc

 

count(1)统计表中的总记录数,

trunc(小数,需要保留几位)   对小数进行处理,根据第二位参数的值确定保留几位小数

order by  字段名 desc 从大到小排序,

order by  字段名  asc  从小到大排序

group by  字段名,根据字段进行分组,字段名一致的为一组

用户: Users +-------------+---------+ | Column Name | Type | +-------------+---------+ | user_id | int | | user_name | varchar | +-------------+---------+ user_id 是该的主键(具有唯一值的列)。 该中的每行包括用户 ID 和用户名。 注册: Register +-------------+---------+ | Column Name | Type | +-------------+---------+ | contest_id | int | | user_id | int | +-------------+---------+ (contest_id, user_id) 是该的主键(具有唯一值的列的组合)。 该中的每行包含用户的 ID 和他们注册的赛事。 编写解决方案统计出各赛事的用户注册百分率,保留两位小数。 返回的结果按 percentage 的 降序 排序,若相同则按 contest_id 的 升序 排序。 返回结果如下示例所示。 示例 1: 输入: Users : +---------+-----------+ | user_id | user_name | +---------+-----------+ | 6 | Alice | | 2 | Bob | | 7 | Alex | +---------+-----------+ Register : +------------+---------+ | contest_id | user_id | +------------+---------+ | 215 | 6 | | 209 | 2 | | 208 | 2 | | 210 | 6 | | 208 | 6 | | 209 | 7 | | 209 | 6 | | 215 | 7 | | 208 | 7 | | 210 | 2 | | 207 | 2 | | 210 | 7 | +------------+---------+ 输出: +------------+------------+ | contest_id | percentage | +------------+------------+ | 208 | 100.0 | | 209 | 100.0 | | 210 | 100.0 | | 215 | 66.67 | | 207 | 33.33 | +------------+------------+ 解释: 所有用户都注册了 208、209 和 210 赛事,因此这些赛事的注册率为 100% ,我们按 contest_id 的降序排序加入结果中。 Alice 和 Alex 注册了 215 赛事,注册率为 ((2/3) * 100) = 66.67% Bob 注册了 207 赛事,注册率为 ((1/3) * 100) = 33.33%
最新发布
03-18
<think>好的,我需要解决这个问题。首先,我得仔细理解题目要求。题目是要统计每个赛事的用户注册百分比,保留两位小数,然后按百分比降序,如果相同的话按contest_id升序排列。给出的示例可以帮助我验证思路是否正确。 首先,我需要确定如何计算每个赛事的百分比。百分比应该是该赛事注册的用户数除以总用户数,然后乘以100,保留两位小数。总用户数来自Users,因为每个用户可能注册多个赛事,但总用户数是固定的,不管他们注册了多少赛事。比如示例中Users有3个用户,所以每个赛事的注册用户数除以3,再乘以100得到百分比。 那首先,我需要从Users中获取总用户数。可以用COUNT(DISTINCT user_id)来统计总人数。然后,对于Register中的每个contest_id,计算该赛事的注册用户数,再除以总用户数,得到百分比。 接下来是SQL的写法。可能需要子查询或者JOIN来结合这两个的数据。不过,实际上Register中每个contest_id对应的用户数可以用GROUP BY contest_id来计算每个赛事的用户数量。然后将这个数量除以总用户数,乘以100,然后四舍五入到两位小数。 那步骤概是: 1. 计算总用户数:SELECT COUNT(*) FROM Users; 2. 对Register按contest_id分组,统计每个分组的用户数; 3. 将每个分组的用户数除以总用户数,得到百分比; 4. 按百分比降序和contest_id升序排序。 需要注意的是,总用户数可能需要作为子查询或者变量来处理。例如,在SELECT语句中可能需要用到子查询来获取总用户数。 比如,可以这样写: SELECT contest_id, ROUND( (COUNT(user_id) / (SELECT COUNT(*) FROM Users)) * 100, 2 ) AS percentage FROM Register GROUP BY contest_id ORDER BY percentage DESC, contest_id ASC; 但这里有个问题,是否需要考虑重复的user_id在同一个赛事中的情况?不过根据题目,Register的主键是(contest_id, user_id),所以每个用户在一个赛事中只能注册一次。因此,每个分组的COUNT(user_id)就是该赛事的不同用户数。而总用户数是Users中的总人数,不管他们是否注册了任何赛事。例如,如果有用户没有注册任何赛事,总用户数仍然包括他们,但该用户不会被统计在任何赛事的注册数中。 比如示例中的Users有3个用户,每个赛事的注册用户数是该赛事有多少不同的user_id记录,比如contest_id 207在Register中只有user_id 2,所以注册数是1,总用户数是3,所以百分比是33.33%。 那上述的SQL是否正确? 测试一下示例的情况。示例中的Register对于contest_id=208,有三个不同的user_id吗?根据输入: Register中contest_id=208的有user_id=2、6、7?比如输入中的Register: 208的条目有: 208 | 2 208 | 6 208 | 7 所以在contest_id=208的分组中,COUNT(user_id)是3。总用户数是3,所以3/3=1,即100%。和示例的输出一致。 那上述的SQL应该能正确计算。 但是,当使用COUNT(user_id)的时候,如果某个用户没有注册任何赛事,他们不会被统计进来,但总用户数仍然会包含他们。例如,如果Users中有一个用户没有在Register中出现,那总用户数是正确的,而每个赛事的百分比是注册用户数/总用户数,包括这个未被注册的用户。这种情况下,计算是正确的。 那现在,这个SQL是否正确? 是的。因为总用户数是来自Users总数,而每个赛事的注册用户数是该赛事中的用户数,所以除法是正确的。 然后,ROUND函数保留两位小数。比如在示例中的215赛事,注册用户数是2(用户6和7),总用户数是3,所以2/3=0.666666…,乘以100是66.666…,四舍五入到两位小数是66.67,符合示例。 那这个时候,这个SQL应该是正确的。然后需要将结果按percentage降序,contest_id升序排列。 但是示例的输出中,208、209、210都是100%,按contest_id降序排列吗?例如,输出中的顺序是208、209、210、215、207。但是contest_id的值是208,209,210的数值是递增的,所以按contest_id升序排列的话,这三个的排列顺序应该是208、209、210。这与示例的输出中的顺序一致。所以ORDER BY子句中的第二部分是contest_id ASC,这正确。 所以整个SQL应该是: SELECT contest_id, ROUND( (COUNT(*) * 100.0) / (SELECT COUNT(*) FROM Users), 2 ) AS percentage FROM Register GROUP BY contest_id ORDER BY percentage DESC, contest_id ASC; 这里需要注意,乘以100.0而不是100,以确保得到浮点数的结果,避免整数除法的问题。例如,在MySQL中,如果分母是整数,那么除法会得到整数部分。所以需要使用浮点数运算。 比如,在示例中的207赛事,注册用户数是1,总用户数是3。1/3是0.333…,乘以100是33.333…。保留两位小数是33.33。如果使用整数运算,可能得到33.00或者错误的数值。所以必须确保计算时使用浮点数。 因此,在SQL中,将分子或分母中的一个转换为浮点数,例如乘以100.0,或者将COUNT(*)转换为小数。 所以,正确的写法应该是: ROUND( (COUNT(user_id) * 100.0) / total_users, 2 ) 其中total_users是用户总数。或者,在MySQL中,可能不需要,但为了保险,乘以100.0是正确的。 现在,在编写这个SQL时,是否应该使用COUNT(*),或者COUNT(user_id)?在GROUP BY contest_id之后,每个分组的行数等于该赛事中的注册用户数目,因为每个用户只能注册一次同一赛事。所以COUNT(*)和COUNT(user_id)的结果是一样的。所以两种写法都可以。 因此,最终的SQL语句应该是: SELECT contest_id, ROUND( (COUNT(*) * 100.0) / (SELECT COUNT(*) FROM Users), 2 ) AS percentage FROM Register GROUP BY contest_id ORDER BY percentage DESC, contest_id; 这样应该就能得到正确的结果。然后,按照示例的输出,验证是否正确。 测试示例中的输入: Users有3个用户。Register中的数据: contest_id 215有用户6和7,即COUNT(*)为2。2*100/3=66.666...,四舍五入到两位小数是66.67。符合示例的输出。 contest_id 207有一个用户,计算1*100/3=33.33,正确。 其他三个赛事208、209、210的注册用户数都是3,所以3/3=100%正确。 所以SQL的输出应该与示例一致。 那这个应该就是正确的解决方案了。</think>要解决这个问题,我们需要统计每个赛事的用户注册百分比,按特定规则排序结果。以下是详细的步骤说明和解决方案: ### 方法思路 1. **计算总用户数**:从 `Users` 中获取所有用户的总数。 2. **统计各赛事注册用户数**:在 `Register` 中按 `contest_id` 分组,统计每个赛事的注册用户数。 3. **计算百分比**:将每个赛事的注册用户数除以总用户数,乘以 100 保留两位小数。 4. **排序结果**:按百分比降序排序,若百分比相同则按赛事 ID 升序排序。 ### 解决代码 ```sql SELECT contest_id, ROUND( (COUNT(*) * 100.0) / (SELECT COUNT(*) FROM Users), 2 ) AS percentage FROM Register GROUP BY contest_id ORDER BY percentage DESC, contest_id ASC; ``` ### 代码解释 1. **总用户数计算**:通过子查询 `(SELECT COUNT(*) FROM Users)` 获取 `Users` 中的总用户数。 2. **分组统计注册用户数**:使用 `GROUP BY contest_id` 对 `Register` 按赛事分组,`COUNT(*)` 统计每个赛事的注册用户数。 3. **计算百分比**:将每个分组的注册用户数乘以 100.0(确保浮点数运算),除以总用户数,用 `ROUND` 函数保留两位小数。 4. **排序规则**:结果首先按百分比降序 (`percentage DESC`) 排列,若百分比相同则按赛事 ID 升序 (`contest_id ASC`) 排列。 这种方法确保准确计算各赛事的注册率,按要求排序结果。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

iamlzjoco

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值