时间戳

首页 > 解决方案 > 从sql中按限制分组的表中查找重复次数最多的元素

sql-server - 从sql中按限制分组的表中查找重复次数最多的元素

问题描述

在我的代码中，我有表格：

CREATE TABLE SERVICE
(
    SER_ID      INT primary key NOT NULL,
    SER_TYPE    VARCHAR(35)     NOT NULL,
    SER_STARTDATE DATE          NOT NULL,
    SER_ENDDATE   DATE,
    LAW_SOCNUM    VARCHAR(8),
    FOL_ID  INT,
    CLI_ID      INT,
    FOREIGN KEY (LAW_SOCNUM) REFERENCES LAWYER ON DELETE SET NULL,
    FOREIGN KEY (FOL_ID) REFERENCES FEILD_OF_LAW ON DELETE SET NULL,
    FOREIGN KEY (CLI_ID) REFERENCES CLIENT ON DELETE SET NULL,

);

和表格：

CREATE TABLE FEILD_OF_LAW
(
    FOL_ID INT PRIMARY KEY NOT NULL,
    FOL_NAME VARCHAR(35) NOT NULL
);

我的任务是找到在给定年份中最常见的服务名称，这只是法律名称的字段。我无法获得正确的值，因为在 FEILD_OF_LAW.FOL_NAME 上调用 max 只会让我按字母顺序排列最低。这是我尝试过的：

select distinct year(SER_STARTDATE) as 'YEAR', max(fol) as 'MOST_COMMON_SERV'
from SERVICE join (select SERVICE.SER_ID, FEILD_OF_LAW.FOL_NAME as 'fol'
    from SERVICE join FEILD_OF_LAW on SERVICE.FOL_ID = FEILD_OF_LAW.FOL_ID) a1 on SERVICE.SER_ID = a1.SER_ID 
group by year(SER_STARTDATE)

谁能帮我解释一下如何获得 SERVICE 表中出现的重复次数最多的 FOL_ID 以及如何分组到它的年份。谢谢。

标签： sql-server

解决方案

我认为你必须使用 count 聚合函数而不是 max wit group by fol_id with top 1 它将返回最重复的 Fol_id 年份。如果你想检查所有值然后从查询中删除前 1 个子句。

        select top 1 year(SER_STARTDATE) as 'YEAR', count(fol) as 
       'MOST_COMMON_SERV',fol
        from SERVICE join (select SERVICE.SER_ID, FEILD_OF_LAW.FOL_NAME as 'fol'
         from SERVICE join FEILD_OF_LAW on SERVICE.FOL_ID = FEILD_OF_LAW.FOL_ID) a1 
        on SERVICE.SER_ID = a1.SER_ID 
        group by year(SER_STARTDATE),fol
        order by count(fol) desc

推荐阅读