为SQL报告创建最高值查询

我在为SQL报告创建顶部值查询时遇到麻烦。

我在数据库中有一个表(同伙(，其中有以下列

CUML_DEBT_P90 CUML_DEBT_P75 CUML_DEBT_P25 CUML_DEBT_P10

我需要创建正确的SQL代码来执行以下操作并遇到问题。我的问题是我对SQL并不太熟悉，并且在语法和参数方面遇到困难。任何帮助将不胜感激。

10％学位完成贷款债务的最高和最低学校
贷款债务的10最高和最低的学校，达到25％的完成
贷款债务的10最高和最低的学校，达到75％的学位
贷款债务的10最高和最低的学校，达到90％的完成

我正在使用 SQL Server 2012 通过管理工作室

sql语句选择顶级10 *来自cuml_debt_p10 desc

的订单

结果14177 11250 10000 9500 9299 8544 8350 8250 8125 8108

但是，这并未显示该列的整个数据集的前十个最高值，/此列的Hi0ghest值为2172

表中的数据类型为

CREATE TABLE [dbo].[Cohorts](
[UNITID] [float] NULL,
[INSTNM] [nvarchar](255) NULL,
[CUML_DEBT_N] [float] NULL,
[CUML_DEBT_P90] [float] NULL,
[CUML_DEBT_P75] [float] NULL,
[CUML_DEBT_P25] [float] NULL,
[CUML_DEBT_P10] [float] NULL,
[C150_4_POOLED_SUPP] [float] NULL

这是指向SQL Server导入的数据的链接，它是Excel文件。使用的表结构相同。

这是一些示例数据

unitid Instnm cuml_debt_n cuml_debt_p90 cuml_debt_p75 cuml_debt_p25 cuml_debt_p10 c150_4_pooled_pooled_supp

100654阿拉巴马州A＆amp;M大学279701 57500 34308 5860 2172 0.819

100663阿拉巴马大学伯明翰279701 57500 34308 5860 2172 0.710515789

100706阿拉巴马大学亨茨维尔279701 57500 34308 5860 2172 0.250007692

100724阿拉巴马州立大学279701 57500 34308 5860 2172 0.268253659

100751阿拉巴马大学279701 57499 34308 5860 2172 0.44262623

100830蒙哥马利的奥本大学279701 57490.5 34308 5860 2172 0.627122034

100858奥本大学279701 57490.5 34308 5860 2172 0.9674

100937伯明翰南部学院279701 57490.5 34308 5860 2172 0.557726923

101073 Alabama Concordia College 279701 57490.5 34308 5860 2172 0.705856303

这是一个预期结果的一个示例，但是我需要显示的不仅仅是第90个百分位数，还需要显示上述所有百分位

机构名称累计贷款债务90％

Aoma综合医学研究生院$ 131,335.00

国际设计与技术学院 - 芝加哥$ 57,500.00

国际设计与技术学院 - 特洛伊$ 57,500.00

国际设计与技术学院 - 纳什维尔$ 57,500.00

柯林斯学院$ 57,500.00

Beulah Heights大学$ 57,499.00

南海岸大学$ 57,496.00

Westwood College-Denver South $ 57,490.50

Westwood College-Anaheim $ 57,490.50

韦斯特伍德学院 - 内地帝国$ 57,490.50

这显示了第90位最高的也需要最低和其他列，例如cuml_debt_p10 etc

未测试，

                    SELECT  TOP 10
                            ROW_NUMBER() OVER (ORDER BY CUML_DEBT_P10 ASC) ctr,
                            UNITID,INSTNM,
                            CUML_DEBT_P10,'CUML_DEBT_P10_ASC' Label
                    FROM    [dbo].[Cohorts]

                    SELECT  TOP 10
                            ROW_NUMBER() OVER (ORDER BY CUML_DEBT_P10 DESC) ctr,
                            UNITID,INSTNM,
                            CUML_DEBT_P10,'CUML_DEBT_P10_DESC' Label
                    FROM    [dbo].[Cohorts]

                    SELECT  TOP 10
                            ROW_NUMBER() OVER (ORDER BY CUML_DEBT_P25 ASC) ctr,
                            UNITID,INSTNM,
                            CUML_DEBT_P25,'CUML_DEBT_P25_ASC' Label
                    FROM    [dbo].[Cohorts]

                    SELECT  TOP 10
                            ROW_NUMBER() OVER (ORDER BY CUML_DEBT_P25 ASC) ctr,
                            UNITID,INSTNM,
                            CUML_DEBT_P25,'CUML_DEBT_P25_DESC' Label
                    FROM    [dbo].[Cohorts]

                    SELECT  TOP 10
                            ROW_NUMBER() OVER (ORDER BY CUML_DEBT_P25 ASC) ctr,
                            UNITID,INSTNM,
                            CUML_DEBT_P25,'CUML_DEBT_P25_ASC' Label
                    FROM    [dbo].[Cohorts]

                    SELECT  TOP 10
                            ROW_NUMBER() OVER (ORDER BY CUML_DEBT_P25 ASC) ctr,
                            UNITID,INSTNM,
                            CUML_DEBT_P25,'CUML_DEBT_P25_DESC' Label
                    FROM    [dbo].[Cohorts]

                    SELECT  TOP 10
                            ROW_NUMBER() OVER (ORDER BY CUML_DEBT_P75 ASC) ctr,
                            UNITID,INSTNM,
                            CUML_DEBT_P75,'CUML_DEBT_P75_ASC' Label
                    FROM    [dbo].[Cohorts]
                    SELECT  TOP 10
                            ROW_NUMBER() OVER (ORDER BY CUML_DEBT_P75 ASC) ctr,
                            UNITID,INSTNM,
                            CUML_DEBT_P75,'CUML_DEBT_P75_DESC' Label
                    FROM    [dbo].[Cohorts]

                    SELECT  TOP 10
                            ROW_NUMBER() OVER (ORDER BY CUML_DEBT_P90 ASC) ctr,
                            UNITID,INSTNM,
                            CUML_DEBT_P90,'CUML_DEBT_P90_ASC' Label
                    FROM    [dbo].[Cohorts]

                    SELECT  TOP 10
                            ROW_NUMBER() OVER (ORDER BY CUML_DEBT_P90 ASC) ctr,
                            UNITID,INSTNM,
                            CUML_DEBT_P90,'CUML_DEBT_P90_DESC' Label
                    FROM    [dbo].[Cohorts]

单独运行每个操作，您应该拥有所需的数据。

SELECT TOP 10 * FROM COHORTS ORDER BY CUML_DEBT_P90 ASC
SELECT TOP 10 * FROM COHORTS ORDER BY CUML_DEBT_P90 DESC
SELECT TOP 10 * FROM COHORTS ORDER BY CUML_DEBT_P75 ASC
SELECT TOP 10 * FROM COHORTS ORDER BY CUML_DEBT_P75 DESC
SELECT TOP 10 * FROM COHORTS ORDER BY CUML_DEBT_P25 ASC
SELECT TOP 10 * FROM COHORTS ORDER BY CUML_DEBT_P25 DESC
SELECT TOP 10 * FROM COHORTS ORDER BY CUML_DEBT_P10 ASC
SELECT TOP 10 * FROM COHORTS ORDER BY CUML_DEBT_P10 DESC

由于值是float数据类型的值，因此您可以使用STR函数转换。

请参见下面，此处的值以7位数字排序为两个小数点。您可以选择要按要订购的数字的最大长度(这里是7位数字(和点之后的数字数(此处，该数字中的2位数字中的2位始终是在点之后的(通过此(如下(订购，这应该解决问题。

SELECT TOP 10 * FROM COHORTS ORDER BY CONVERT(DECIMAL,STR(CUML_DEBT_P90,7,2))  ASC
SELECT TOP 10 * FROM COHORTS ORDER BY CONVERT(DECIMAL,STR(CUML_DEBT_P90,7,2))  DESC
SELECT TOP 10 * FROM COHORTS ORDER BY CONVERT(DECIMAL,STR(CUML_DEBT_P75,7,2))  ASC
SELECT TOP 10 * FROM COHORTS ORDER BY CONVERT(DECIMAL,STR(CUML_DEBT_P75,7,2))  DESC
SELECT TOP 10 * FROM COHORTS ORDER BY CONVERT(DECIMAL,STR(CUML_DEBT_P25,7,2))  ASC
SELECT TOP 10 * FROM COHORTS ORDER BY CONVERT(DECIMAL,STR(CUML_DEBT_P25,7,2))  DESC
SELECT TOP 10 * FROM COHORTS ORDER BY CONVERT(DECIMAL,STR(CUML_DEBT_P10,7,2))  ASC
SELECT TOP 10 * FROM COHORTS ORDER BY CONVERT(DECIMAL,STR(CUML_DEBT_P10,7,2)) DESC

或作为金钱：

SELECT TOP 10 * FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P90,8,2))  ASC
SELECT TOP 10 * FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P90,8,2))  DESC
SELECT TOP 10 * FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P75,8,2))  ASC
SELECT TOP 10 * FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P75,8,2))  DESC
SELECT TOP 10 * FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P25,8,2))  ASC
SELECT TOP 10 * FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P25,8,2))  DESC
SELECT TOP 10 * FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P10,8,2))  ASC
SELECT TOP 10 * FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P10,8,2)) DESC

选择unitid，instnm＆amp;百分位列在按参数保留订单的同时：

SELECT TOP 10 UNITID,INSTNM,CUML_DEBT_P90 FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P90,8,2))  ASC
SELECT TOP 10 UNITID,INSTNM,CUML_DEBT_P90 FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P90,8,2))  DESC
SELECT TOP 10 UNITID,INSTNM,CUML_DEBT_P75 FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P75,8,2))  ASC
SELECT TOP 10 UNITID,INSTNM,CUML_DEBT_P75 FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P75,8,2))  DESC
SELECT TOP 10 UNITID,INSTNM,CUML_DEBT_P25 FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P25,8,2))  ASC
SELECT TOP 10 UNITID,INSTNM,CUML_DEBT_P25 FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P25,8,2))  DESC
SELECT TOP 10 UNITID,INSTNM,CUML_DEBT_P10 FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P10,8,2))  ASC
SELECT TOP 10 UNITID,INSTNM,CUML_DEBT_P10 FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P10,8,2))  DESC

您可以在选择中转换百分位列，以便它们显示(或移动到另一个表(，作为您订购的数据类型，以达到前10位：

SELECT TOP 10 UNITID,INSTNM,CONVERT(MONEY,STR(CUML_DEBT_P90,8,2)) FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P90,8,2))  ASC
SELECT TOP 10 UNITID,INSTNM,CONVERT(MONEY,STR(CUML_DEBT_P90,8,2)) FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P90,8,2))  DESC
SELECT TOP 10 UNITID,INSTNM,CONVERT(MONEY,STR(CUML_DEBT_P75,8,2)) FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P75,8,2))  ASC
SELECT TOP 10 UNITID,INSTNM,CONVERT(MONEY,STR(CUML_DEBT_P75,8,2)) FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P75,8,2))  DESC
SELECT TOP 10 UNITID,INSTNM,CONVERT(MONEY,STR(CUML_DEBT_P25,8,2)) FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P25,8,2))  ASC
SELECT TOP 10 UNITID,INSTNM,CONVERT(MONEY,STR(CUML_DEBT_P25,8,2)) FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P25,8,2))  DESC
SELECT TOP 10 UNITID,INSTNM,CONVERT(MONEY,STR(CUML_DEBT_P10,8,2)) FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P10,8,2))  ASC
SELECT TOP 10 UNITID,INSTNM,CONVERT(MONEY,STR(CUML_DEBT_P10,8,2)) FROM COHORTS ORDER BY CONVERT(MONEY,STR(CUML_DEBT_P10,8,2))  DESC

相关内容

最新更新

热门标签：