主页 / 问题 / 1322474
Accepted
chilo5432
Asked:2022-08-30 01:08:51 +0000 UTC2022-08-30 01:08:51 +0000 UTC

如何对前两个元素进行排序和分组？

  • 772

我需要对我需要的对象进行排序并获取前 2 个元素和组。例如：

    CREATE TEMP TABLE signal_info
(
    id      INTEGER NOT NULL,
    signal  INTEGER NOT NULL,
    station CHAR(5) NOT NULL,
    ownerid INTEGER NOT NULL
);
INSERT INTO signal_info VALUES(111, 120, 'Home', 1);
INSERT INTO signal_info VALUES(111, 130, 'Car' , 1);
INSERT INTO signal_info VALUES(111, 135, 'Work', 2);
INSERT INTO signal_info VALUES(222, 98 , 'Home', 2);
INSERT INTO signal_info VALUES(222, 95 , 'Work', 1);
INSERT INTO signal_info VALUES(111, 140, 'Home', 1);
INSERT INTO signal_info VALUES(222, 160 , 'Work', 1);

SELECT station, count(station) as countStation, sum(Signal) as sum
            FROM signal_info
            where station in ('Home', 'Work')
            GROUP BY station

例如，我需要按最大信号排序并取前 2 个元素。需要得到： 工作 - 2 - 295 家庭 - 2 - 260

怎么做？我正在使用 SQL 服务器

sql

1 个回答

  1. Best Answer
    ;with cte as
(select station, signal, row_number() over(partition by station order by signal desc) rn
from signal_info)
select station, sum(signal) from cte
where rn<=2 and station in
(select top 2 station from signal_info order by signal desc)
group by station;
    • 3

相关问题