sql - 如何获得每年 SQLite 的 x 个最佳结果
问题描述
我的解决方案有问题,我找到了这个例子:
(SELECT COUNT(*) FROM person AS b
WHERE b.group = a.group AND b.age >= a.age) <= 2
ORDER BY a.group ASC, a.age DESC
(来自:获取每组分组结果的前 n 条记录)
但是我需要根据旧列创建新列,所以我需要进行一些计算,当我尝试添加更多内容时,我会收到一条错误消息。如果我只是简单地添加等式,就可以了,例如:
(SELECT COUNT(*) FROM person AS b
WHERE b.group = a.group AND b.age*100 >= a.age*100) <= 2
ORDER BY a.group ASC, a.age DESC
但是当我试图重命名我的新列时,AS 太多了。
我也尝试使用 UNION ALL,但我的 SQLite 对 () 不满意。这根本不适合我:
(
select *
from mytable
where `year` = 2012
order by score*100/50 AS percent desc
LIMIT 2
)
UNION ALL
(
select *
from mytable
where `year` = 2013
order by score*100/50 AS percent desc
LIMIT 2
)
“结果:靠近“(”:语法错误
在第 1 行:(
“
即使我在 () 之前取出 SELECT 和 FROM,我也会收到错误消息。
select * from mytable
(where `year` = 2012
order by score*100/50 AS percent desc
LIMIT 2)
UNION ALL
select * from mytable
(where `year` = 2013
order by score*100/50 AS percent desc
LIMIT 2)
“WHERE”附近:语法错误
请有人解释一下为什么?
编辑
这是数据。
| 人 | 年份 | 分数 | +--------+-------+--------+ | 鲍勃 | 2013 | 32 | | 吉尔 | 2012 | 34 | | 肖恩 | 2012 | 42 | | 杰克 | 2012 | 29 | | 保罗 | 2013 | 36 | | 劳拉 | 2013 | 39 |
期望的结果集:
| 人 | 年份 | 百分比 | +--------+--------+---------+ | 肖恩 | 2012 | 84 | | 吉尔 | 2012 | 68 | | 劳拉 | 2013 | 78 | | 保罗 | 2013 | 72 | +--------+--------+---------+
其中百分比 = 分数*100/50
解决方案
此语法应该有效:
select *
from (
select *
from mytable
where `group` = 1
order by age desc
LIMIT 2
)
UNION ALL
select *
from (
select *
from mytable
where `group` = 2
order by age desc
LIMIT 2
);
但是你也可以用 ROW_NUMBER() 来做:
select t.`group`, t.age, ....<rest of the columns from mytable>
from (
select *, row_number() over (partition by `group` order by age desc) rn
from mytable
where `group` in (1, 2)
) t
where t.rn <= 2
推荐阅读
- python - 从电子邮件转换字符串
- javascript - How can you console.log tab?
- list - following the class correctly but the code is not accepted
- php - How to include workspcace project folder in run URLs in Ecplise for PHP?
- java - Is calling close() on object returned by MockitoAnnotations.openMocks(Object) is mandatory?
- typescript - 根据未定义检查参数,但编译器仍然警告 TS2532
- xcode - 从 Flutter 项目中删除蓝牙使用
- python - 如何在 python django 的导航栏中获取图像?
- python - 如何通过Django导出多个文件
- python - Python 3 中的堆实现