如何在sql结果集的末尾添加一个默认行？ [英] How to add a default row at the end of result set in sql?

问题描述

嗨专家，



我通过低于以下的查询获得此输出

IPS_Facility_Nam e  &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;的百分比 <无线电通信/>
abcefg Certficate&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 28

PQRSTUV证书&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP; 21

XYZABC证书&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 16

xlmnopq证书&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; 9

Yojane cirtificate&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 6

其他&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 20



查询

Hi experts,

I have got this output by below mensioned query
IPS_Facility_Name                      percentage
abcefg Certficate                            28
PQRSTUV Certificate                      21
XYZABC Certificate                        16
xlmnopq Certificate                       9
Yojane cirtificate                            6
Others                                         20

Query

Select top 5 IPS_Facility_Name,
        convert(decimal,(convert(decimal,SUM(IPS_TotRaised))/convert(decimal,@totReceived))*100) as percentage
        from Intermediate_MonthWise_Statistics
        --where IPS_Month =@month  and IPS_Year =@year
        group by IPS_Facility_Name
        having SUM(IPS_TotRaised)>0
        order by SUM(IPS_TotRaised) desc

我想要总和百分比列，将从100减去此结果我想添加为100-（Sum（PercentageColumn））= 20的新行



Plese帮我计算​​sql查询中的其他行



（提前谢谢）

（Keerthi Kumar）

I want to sum the percentage column and that will be subtracted from 100 this result i want to add as a new row that is 100-(Sum(PercentageColumn))=20

Plese help me to calculate others row in sql query

(thanks in advance)
(Keerthi Kumar)

推荐答案

你可以按如下方式使用TempTable：

You can use TempTable as follows :

DECLARE @TempTable TABLE
	(	
		IPS_Facility_Name VARCHAR(100),
		FacilityPercentage int		
	)
	
	INSERT INTO @TempTable
	        ( IPS_Facility_Name,FacilityPercentage )
	Select top 5 IPS_Facility_Name,
        convert(decimal,(convert(decimal,SUM(IPS_TotRaised))/convert(decimal,@totReceived))*100) as percentage
        from Intermediate_MonthWise_Statistics
        --where IPS_Month =@month  and IPS_Year =@year
        group by IPS_Facility_Name
        having SUM(IPS_TotRaised)>0
        order by SUM(IPS_TotRaised) DESC
        
        INSERT INTO @TempTable
                ( IPS_Facility_Name,FacilityPercentage )
        SELECT 'Total Percentage',100 - SUM(FacilityPercentage) FROM @TempTable
        
        SELECT * FROM @TempTable

检查一下，并根据您的需要进行调整：

http://sqlfiddle.com/#!3/6dfc5/11 [ ^ ]

Check this out, and adapt it to your need:
http://sqlfiddle.com/#!3/6dfc5/11[^]

这篇关于如何在sql结果集的末尾添加一个默认行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！