我想合并两列，其中在SQL Server中将公共列重复两次

问题描述：

SELECT AIN_AIRLINE_CODE AS'AIRLINECODE',(SUM(AIN_NET_FARE)-(SUM(AIN_DISCOUNT_AMT)+SUM(AIN_P_TDS_AMT)))
AS 'CURRENT_MONTH_INVOICE',0 AS 'CURRENT_MONTH_CN' FROM T_T_AIR_INVOICE A
 WHERE AIN_INVOICE_DT BETWEEN
(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101)) AND GETDATE()
 GROUP BY AIN_AIRLINE_CODE UNION
 (SELECT ACN_AIRLINE_CODE AS'AIRLINECODE',0 AS 'CURRENT_MONTH_INVOICE',
(SUM(ACN_NET_REFUND)-(SUM(ACN_DISCOUNT_AMT)+SUM(ACN_P_TDS_AMT)))
 AS 'CURRENT_MONTH_CN' FROM T_T_AIR_CRNOTE B WHERE ACN_CRN_DT BETWEEN
 (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101)) AND GETDATE()
 GROUP BY ACN_AIRLINE_CODE)

我已经创建了这样的查询....下面给出了我的输出...

AIRLINECODE CURRENT_MONTH_INVOICE CURRENT_MONTH_CN

i had created the query like this....my output is given below...

AIRLINECODE CURRENT_MONTH_INVOICE CURRENT_MONTH_CN

001           0.00                9764.00
        001           20145.00            0.00
        005           59676.00            0.00
        006           6304.00             0.00
        057           24516.00            0.00
        058           0.00                7810.00
        058           728635.00           0.00
        098           0.00                3378.00
        098           5982.00             0.00
        220           0.00                16000.00
        220           21000.00            0.00

我想通过合并发票和贷方通知单列的方式来更改查询，使其只显示一次重复的航空公司代码...在此航空公司代码001中重复两次..我只希望显示一次. .

i want to alter the query in the way that i want to display the repeated airline code only one time by merging the invoice and credit note columns...in this airline code 001 is repeated twice..i want to display only one time..

答

由于我不知道您的数据库有多大，因此不确定其性能，但是一种实现方法是将查询与另一种包装在一起像这样的SELECT语句.

Not sure on the performance of this since I dont know how large your database is, but one way you can do it is you can wrap your query with another SELECT statement, like this.

SELECT AIRLINECODE, SUM(CURRENT_MONTH_INVOICE), SUM(CURRENT_MONTH_INVOICE) FROM(
SELECT AIN_AIRLINE_CODE AS'AIRLINECODE',(SUM(AIN_NET_FARE)-(SUM(AIN_DISCOUNT_AMT)+SUM(AIN_P_TDS_AMT)))
AS 'CURRENT_MONTH_INVOICE',0 AS 'CURRENT_MONTH_CN' FROM T_T_AIR_INVOICE A
 WHERE AIN_INVOICE_DT BETWEEN
(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101)) AND GETDATE()
 GROUP BY AIN_AIRLINE_CODE UNION
 (SELECT ACN_AIRLINE_CODE AS'AIRLINECODE',0 AS 'CURRENT_MONTH_INVOICE',
(SUM(ACN_NET_REFUND)-(SUM(ACN_DISCOUNT_AMT)+SUM(ACN_P_TDS_AMT)))
 AS 'CURRENT_MONTH_CN' FROM T_T_AIR_CRNOTE B WHERE ACN_CRN_DT BETWEEN
 (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101)) AND GETDATE()
 GROUP BY ACN_AIRLINE_CODE)) GROUP BY AIRLINECODE

我想合并两列，其中在SQL Server中将公共列重复两次

相关推荐