我想合并两列，其中在SQL Server中将公共列重复两次 [英] i want to merge two columns in which the common column is repeated twice in 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中将公共列重复两次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！