問題描述
我有 3 個查詢，它們從 3 個不同的表(帶連接)中獲取數(shù)據(jù)，并且它們的列名幾乎相同(或者我使用 AS 關鍵字使它們相同).完成 3 個查詢后，我想合并它們的結果，因此看起來它們來自一張表.請看下面的代碼.
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
完成這些查詢后，我想合并它們的結果.這意味著，2nd Query 返回的行將附加在 1st 查詢返回的行之后. 第三個查詢返回的行將附加在 1st 查詢返回的行之后第二問.最后，我想按 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
我該怎么做?
推薦答案
使用 UNION 組合查詢:
SELECT one_fields FROM Client_Portfolio ...
UNION
SELECT two_fields FROM Client_Portfolio ...
UNION
SELECT three_fields FROM Client_Portfolio ...
排序可以通過在最后一個查詢后附加一個 order by 子句來完成，如下所示:
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...;
注意field1、field2...可以是字段名稱或字段編號(從1開始).
Note that field1, field2... can be field names or field numbers (starting from 1).
這篇關于將從不同查詢返回的行附加到一個的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！