时间戳

首页 > 解决方案 > SQL 计数（最大值（））

sql - SQL 计数（最大值（））

问题描述

我正在尝试使用 Microsoft SQl Server Management Studio 计算我的查询中最大的发行者。在这种情况下，我希望它计算最大的实例数ISSUER_COUNTRY，在这种情况下6，最大的发行者是US.

    Declare @T as varchar(10) ='2019-08-01', @PortfolioID as Varchar(10)='B4'
    ;
    select [value_date], [SHORT_DESCRIPTON], [SM_SEC_GROUP],[ULT_ISSUER_NAME], [ISSUER_COUNTRY] 
    from [DATABASE]
    where [value_date] in (@T) 
    and  PORTF_LIST=@portfolioID
    and [SM_SEC_GROUP] in ('IBND','BND')

查询的片段。

value_date  SHORT_DESCRIPTON    SM_SEC_GROUP    PORTF_LIST  ULT_ISSUER_NAME ISSUER_COUNTRY
2019-08-01  ITALY (REPUBLIC OF)        RegS BND B4  ITALY (REPUBLIC OF) IT
2019-08-01  GERMANY (FEDERAL REPUBLIC OF) RegS  BND B4  GERMANY (FEDERAL REPUBLIC OF)   DE
2019-08-01  TREASURY NOTE   BND B4  UNITED STATES OF AMERICA    US
2019-08-01  TREASURY NOTE   BND B4  UNITED STATES OF AMERICA    US
2019-08-01  GERMANY (FEDERAL REPUBLIC OF) RegS  BND B4  GERMANY (FEDERAL REPUBLIC OF)   DE
2019-08-01  ITALY (REPUBLIC OF)        RegS BND B4  ITALY (REPUBLIC OF) IT
2019-08-01  FRANCE (REPUBLIC OF)       RegS BND B4  FRANCE (REPUBLIC OF)    FR
2019-08-01  SPAIN (KINGDOM OF)  BND B4  SPAIN (KINGDOM OF)  ES
2019-08-01  SPAIN (KINGDOM OF)  BND B4  SPAIN (KINGDOM OF)  ES
2019-08-01  TREASURY NOTE   BND B4  UNITED STATES OF AMERICA    US
2019-08-01  TREASURY NOTE   BND B4  UNITED STATES OF AMERICA    US
2019-08-01  TREASURY NOTE   BND B4  UNITED STATES OF AMERICA    US
2019-08-01  TREASURY NOTE   BND B4  UNITED STATES OF AMERICA    US

标签： sqlsql-server

解决方案

我想你只是想要top (1)和group by：

select top (1) [ISSUER_COUNTRY], count(*)
from [DATABASE]
where [value_date] in (@T) and
      PORTF_LIST = @portfolioID  and
      [SM_SEC_GROUP] in ('IBND', 'BND')
group by ISSUER_COUNTRY
order by count(*) desc;

如果您想要多行以防出现平局，请使用top (1) with ties.

推荐阅读