单个结果表中的两个查询? [英] Two query in single result table?

问题描述

我是 SQL Server 2008 的新手.有一个 Proc.在这个 Proc 中，我有两个 select 语句.当我执行我的 Proc 时，我会在我接受的两个表中得到结果.但我想在单表中返回.

I am newbie in SQL Server 2008.There is a Proc.In this Proc, I have two select statements.When I execute my Proc, I will get results in two table which I accepted. But I want to this return in single table.

我的进程 -

 ALTER PROC [GetPaymentGateway]  
 @CompanyID VARCHAR(3),  
 @ChannelType varchar(15)=null
AS  

IF @ChannelType='BODC' OR @ChannelType='BO-DC'  
BEGIN
    SELECT [card_name], [card_type], [BODC_Amount], [BODC_Amount_Type], PGM.PG_Type FROM credit_card_master CCM
    INNER JOIN PaymentGateway_master PGM
    ON PGM.payment_gateway_code = CCM.payment_gateway_code  
    WHERE CCM.company_id = @CompanyID and CCM.disabled = '1'

    SELECT PGM.Payment_Gateway_Name, PGNBC.BODC_Charge_Amt, PGNBC.BODC_Charge_type, PGM.PG_Type 
    FROM PG_NetBanking_Charges PGNBC
    INNER JOIN PaymentGateway_master PGM
    ON PGM.payment_gateway_code = PGNBC.payment_gateway_code
    WHERE PGNBC.company_id = @CompanyID
END

IF @ChannelType='B2C' OR @ChannelType='ONLINE-DC'  
BEGIN
    SELECT [card_name], [card_type], [charge_amount], [B2C_Amount_type], PGM.PG_Type FROM credit_card_master CCM
    INNER JOIN PaymentGateway_master PGM
    ON PGM.payment_gateway_code = CCM.payment_gateway_code 
    WHERE CCM.company_id = @CompanyID and CCM.disabled = '1' 

    SELECT PGM.Payment_Gateway_Name, PGNBC.Online_DC_Charge_Amt, PGNBC.Online_DC_Charge_type, PGM.PG_Type 
    FROM PG_NetBanking_Charges PGNBC
    INNER JOIN PaymentGateway_master PGM
    ON PGM.payment_gateway_code = PGNBC.payment_gateway_code
    WHERE PGNBC.company_id = @CompanyID
END 

请建议我怎么可能??

提前致谢.

推荐答案

要将两个查询合并到一个表中，您需要 UNION 操作.这需要两个结果集，并且基本上将它们粘合在一起.
Union 的限制很少，最重要的是查询的列数必须相同.

To compbine two queries in one table, you need the UNION operation. That takes two result sets, and basically glues them together.
Union has few restrictions, and the most important is that it's necessary that the queries have the same number of columns.

在您的查询中，您选择了不同数量的列，credit_card_master 查询各有 5 列，PG_NetBanking_Charges 查询各有 4 列.

In you queries you have different number of columns selected, the credit_card_master queries have 5 columns each, and the PG_NetBanking_Charges queries have 4 columns each.

据我所知，我猜第一个查询中的 card_type 列在第二个查询中没有等效项，因此您可以将第二个查询重写为:

From what I can see, i guess that the card_type column from the first query has no equivalent in the second query, so you could rewrite the second query as:

SELECT card_name, card_type, charge_amount, B2C_Amount_type, PGM.PG_Type 
  FROM ...
  WHERE ...
UNION
SELECT PGM.Payment_Gateway_Name, null, PGNBC.Online_DC_Charge_Amt,
       PGNBC.Online_DC_Charge_type, PGM.PG_Type
  FROM ...
  WHERE ...

另请注意，结果集中的列将从第一个查询中获取列的名称，因此您可能需要添加列别名以获得更有意义/通用的列名称.此外，我通常添加一个源"列，使我能够跟踪联合中行的来源，因此我的最终查询将如下所示:

Also note that the columns in the result set will take the names of the columns from the first query, so you might want to add a column alias to get a more meaningful / generic name for the column. Also I usually add a "Source" column that enables me to trace the origin of the row in the union, so my final query would look like:

SELECT 1 as Source, card_name as Name, card_type as Type, 
       charge_amount as Ammount, B2C_Amount_type as AmmountType,
       PGM.PG_Type as PG_Type
  FROM ...
  WHERE ...
UNION
SELECT 2, PGM.Payment_Gateway_Name, null, PGNBC.Online_DC_Charge_Amt,
       PGNBC.Online_DC_Charge_type, PGM.PG_Type
  FROM ...
  WHERE ...

结果将包含列 Source、Name、Type、Ammount、AmmountType 和 PG_Type，其中 Source 对于第一个查询中的行将为 1，对于第二个查询中的行为 2.

and the result will have the columns Source, Name, Type, Ammount, AmmountType and PG_Type, where Source will be 1 for rows from the first, and 2 for rows from the second query.

这篇关于单个结果表中的两个查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！