>我有一个包含以下字段的SQL表:
- 公司编号
- 公司名称
- 财年
- 财政季度
每个公司都有不同会计年度和会计季度的多个记录。我想根据"最大财政年度"和"最大财政季度"检索每个公司的行。例如,如果表具有以下内容:
Company ID | Company Name | Fiscal Year | Fiscal Quarter
1 | Test1 | 2017 | 1
1 | Test1 | 2017 | 2
1 | Test1 | 2018 | 1
1 | Test1 | 2018 | 2
2 | Test2 | 2018 | 3
2 | Test2 | 2018 | 4
查询应返回以下内容(仅具有该年度最大会计年度和最大会计季度的记录(:
Company ID | Company Name | Fiscal Year | Fiscal Quarter
1 | Test1 | 2018 | 2
2 | Test2 | 2018 | 4
我能够使用以下查询来获取具有最大财政年度的记录,但不确定如何进一步选择一年内的最大季度:
SELECT fp.companyId, fp.companyname, fp.fiscalyear,fp.fiscalquarter
FROM dbo.ciqFinPeriod fp
LEFT OUTER JOIN dbo.ciqFinPeriod fp2
ON (fp.companyId = fp2.companyId AND fp.fiscalyear < fp2.fiscalyear)
WHERE fp2.companyId IS NULL
非常感谢您的任何帮助!
如果你有一个公司名单,我会简单地做:
select fp.*
from Companies c outer apply
(select top (1) fp.*
from dbo.ciqFinPeriod fp
where fp.companyId = c.companyId
order by fp.fiscalyear desc, fp.fiscalquarter desc
) fp;
如果没有,那么row_number()
可能是最简单的方法:
select fp.*
from (select fp.*,
row_number() over (partition by fp.companyId order by order by fp.fiscalyear desc, fp.fiscalquarter desc) as seqnum
from dbo.ciqFinPeriod fp
) fp
where seqnum = 1;
或者更深奥(聪明?
select top (1) with ties fp.*
from dbo.ciqFinPeriod fp
order by row_number() over (partition by fp.companyId order by order by fp.fiscalyear desc, fp.fiscalquarter desc)
我在以下与您相同的输出方面取得了一些成功。
create table #table
(
CompanyID int,
CompanyName varchar(200),
Year int,
Quater int
)
insert into #table (CompanyID,CompanyName,Year,Quater)
VALUES
('1','Test1','2017','1'),
('1','Test1','2017','2'),
('1','Test1','2018','1'),
('1','Test1','2018','2'),
('2','Test2','2018','3'),
('2','Test2','2018','4')
SELECT CompanyID,CompanyName,Year,Quater
FROM
(
Select CompanyID,CompanyName,Year,Quater
, ROW_NUMBER() OVER(PARTITION BY CompanyID ORDER BY Year desc,Quater DESC)
as RowNum
from #table
) X WHERE RowNum = 1
drop table #table
选择公司 I'd,公司名称,最大值(年(,最大值(季度(组 1,2