追加行从不同的查询到一个返回 [英] Appending rows returned from different queries into one

问题描述

我有3个查询，从3个不同的表中获取数据（带连接）和列名是pretty很多相同（或我让他们同样使用为关键字）。一旦3查询完成后，我想他们的结果结合起来，所以看起来他们是从一个表来。请看看下面的codeS。

I am having 3 queries, which takes data from 3 different tables (with joins) and their column names are pretty much same (or I made them same by using ASkeyword). Once the 3 queries are completed, I want to combine their results, so it looks like they are coming from one table. Please have a look at the below codes.

第一次查询

SELECT Client_Portfolio.*,
Client.Name,
Provider.Name,
"One" AS Income_Type,
One.`One_Gross_Fee` AS "Gross_Fee",
One.`One_V_Fee` AS "V_Fee",
One.`One_E_Fee` AS "E_Fee",
One.`One_I_Fee` AS "I_Fee",
One.`One_Tax_Provision` AS "Tax_Provision",
One.`One_Net_Income` AS "Net_Income",
"N/A" AS VAT,
One.`Updated_Date`
FROM Client_Portfolio
INNER JOIN Portfolio ON Portfolio.`idPortfolio` = Client_Portfolio.`idPortfolio`
INNER JOIN Client ON Client.idClient = Client_Portfolio.idClient
JOIN Provider ON Provider.idProvider = Portfolio.idProvider
INNER JOIN One ON One.idPortfolio = Portfolio.idPortfolio

第二查询

SELECT Client_Portfolio.*,
Client.Name,
Provider.Name,
"Two" AS Income_Type,
Two.`Two_Gross_Fee` AS "Gross_Fee",
Two.`Two_V_Fee` AS "V_Fee",
Two.`Two_E_Fee` AS "E_Fee",
Two.`Two_I_Fee` AS "I_Fee",
Two.`Two_Tax_Provision` AS "Tax_Provision",
Two.`Two_Net_Income` AS "Net_Income",
Two.`Two_Vat` AS VAT,
Two.`Updated_Date`
FROM Client_Portfolio
INNER JOIN Portfolio ON Portfolio.`idPortfolio` = Client_Portfolio.`idPortfolio`
INNER JOIN Client ON Client.idClient = Client_Portfolio.idClient
JOIN Provider ON Provider.idProvider = Portfolio.idProvider
INNER JOIN Two ON Two.idPortfolio = Portfolio.idPortfolio

第三查询

SELECT Client_Portfolio.*,
Client.Name,
Provider.Name,
"Three" AS Income_Type,
Three.`Three_Gross_Fee` AS "Gross_Fee",
"N\A" AS "V_Fee",
Three.`Three_E_Fee` AS "E_Fee",
"N\A" AS "I_Fee",
Three.`Three_Tax_Provision` AS "Tax_Provision",
Three.`Three_Net_Income` AS "Net_Income",
Three.`Three_Vat` AS VAT,
Three.`Updated_Date`
FROM Client_Portfolio
INNER JOIN Portfolio ON Portfolio.`idPortfolio` = Client_Portfolio.`idPortfolio`
INNER JOIN Client ON Client.idClient = Client_Portfolio.idClient
JOIN Provider ON Provider.idProvider = Portfolio.idProvider
INNER JOIN Three ON Three.idPortfolio = Portfolio.idPortfolio

一旦这些疑问都做了，我想他们的结果结合起来。这意味着，由第二查询的第一次查询返回的行后，将被追加。由第三查询返回的行会被返回的行后追加返回的行第二个查询。最后，我想作为排序依据 Updated_Date

Once these queries are done, I want to combine their results. Which means, Rows returned by the 2nd Query will be appended after the rows returned by the 1st query. Rows returned by the 3rd query will be appended after the rows returned by the 2nd query. Finally, I want to sort the final result by Updated_Date

我怎样才能做到这一点？

How can I do this?

推荐答案

使用 UNION 来查询合并：

SELECT one_fields FROM Client_Portfolio ...
UNION
SELECT two_fields FROM Client_Portfolio ...
UNION
SELECT three_fields FROM Client_Portfolio ...

排序可以通过上次查询后附加条款的顺序进行，具体如下：

Sorting can be done by appending an order by clause after the last query, as follows:

SELECT one_fields FROM Client_Portfolio ...
UNION
SELECT two_fields FROM Client_Portfolio ...
UNION
SELECT three_fields FROM Client_Portfolio ...
ORDER BY field1, field2, field3...;

注意字段1 ，字段2 ...可以现场名称或场数（从1开始）。

Note that field1, field2... can be field names or field numbers (starting from 1).

这篇关于追加行从不同的查询到一个返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！