MySQL UNION 语句中的 Order By

Question

我有这个 MySQL 查询，该查询没有给出任何错误。

$sql_i = "(SELECT
            fld.product_id AS product_id,
            inh_pr.naam AS productnaam,
            fld.aantal AS aantal,
            '' AS afmeting,
            '' AS proces,
            '' AS gewicht,
            '' AS kwaliteit,
            '' AS oppervlak,
            '' AS attest,
            '' AS afschuin_id,
            inh_pr.opmerking AS opmerking
            FROM 3_product_folder AS fld
            LEFT JOIN 0_calculatie_inh_id_geg_product AS inh_pr
            ON inh_pr.calculatie_inh_id = fld.product_id
            LEFT JOIN 0_calculatie_inh_id AS id
            ON id.id = inh_pr.calculatie_inh_id
            LEFT JOIN 0_calculatie_id AS calc
            ON calc.id = id.calculatie_id
            WHERE fld.folder_id = '".$row_b['folder_id']."' AND id.soort = 'product'
            ORDER BY inh_pr.naam ASC)

            UNION ALL
        (SELECT
        fld.product_id AS product_id,
        IF(id_plt.naam_in_pos = 'ja', CONCAT(srt.omschrijving,' ',calc.naam, ' ',inh_plt.pos), CONCAT(srt.omschrijving,' ',inh_plt.pos)) AS productnaam,
        fld.aantal AS aantal,
        CONCAT(inh_plt.lengte,'x',inh_plt.breedte,'x',inh_plt.dikte) AS afmeting,
        inh_plt.proces AS proces,
        fld.aantal * inh_plt.gewicht_stk AS gewicht,
        kwal.kwaliteit AS kwaliteit,
        opp.omschrijving AS oppervlak,
        IF(inh_plt.attest > 0, inh_plt.attest, id_plt.attest_std) AS attest,
        inh_plt.afschuin_id AS afschuin_id,
        inh_plt.opmerking AS opmerking
        FROM 3_product_folder AS fld
        LEFT JOIN 0_calculatie_inh_id AS id
        ON id.id = fld.product_id
        LEFT JOIN 0_calculatie_id AS calc
        ON calc.id = id.calculatie_id
        LEFT JOIN 0_calculatie_id_geg_plaat AS id_plt
        ON id_plt.calculatie_id_id = id.calculatie_id
        LEFT JOIN 0_calculatie_inh_id_geg_plaat AS inh_plt
        ON inh_plt.calculatie_inh_id = id.id
        LEFT JOIN calculatie_omtrek AS srt
        ON srt.id = inh_plt.soort_id
        LEFT JOIN kwaliteit AS kwal
        ON kwal.id = IF(inh_plt.kwaliteit_id > 0, inh_plt.kwaliteit_id, id_plt.kwaliteit_id_std)
        LEFT JOIN kwaliteit_opp AS opp
        ON opp.id = IF(inh_plt.oppervlak_id > 0, inh_plt.oppervlak_id, id_plt.oppervl_id_std)
        WHERE fld.folder_id = '".$row_b['folder_id']."' AND id.soort = 'plaat'
        ORDER BY inh_plt.dikte ASC)
    ";

但是查询没有排序inh_plt.dikte ASC

当搜索这个网站和Google时，似乎子查询无法排序？但是in this Stackoverflow page是一个解决方案。

我是不是误会了什么？请指教。

按照 Steve T 建议进行查询，结果为 6/5/10

$sql_i = "SELECT * FROM
            (SELECT
                fld.product_id AS product_id,
                inh_pr.naam AS productnaam,
                fld.aantal AS aantal,
                '' AS dikte,
                '' AS afmeting,
                '' AS proces,
                '' AS gewicht,
                '' AS kwaliteit,
                '' AS oppervlak,
                '' AS attest,
                '' AS afschuin_id,
                inh_pr.opmerking AS opmerking
                FROM 3_product_folder AS fld
                LEFT JOIN 0_calculatie_inh_id_geg_product AS inh_pr
                ON inh_pr.calculatie_inh_id = fld.product_id
                LEFT JOIN 0_calculatie_inh_id AS id
                ON id.id = inh_pr.calculatie_inh_id
                LEFT JOIN 0_calculatie_id AS calc
                ON calc.id = id.calculatie_id
                WHERE fld.folder_id = '".$row_b['folder_id']."' AND id.soort = 'product'

        UNION ALL

            SELECT
                fld.product_id AS product_id,
                IF(id_plt.naam_in_pos = 'ja', CONCAT(srt.omschrijving,' ',calc.naam, ' ',inh_plt.pos), CONCAT(srt.omschrijving,' ',inh_plt.pos)) AS productnaam,
                fld.aantal AS aantal,
                inh_plt.dikte AS dikte,
            CONCAT(inh_plt.lengte,'x',inh_plt.breedte,'x',inh_plt.dikte) AS afmeting,
            inh_plt.proces AS proces,
            fld.aantal * inh_plt.gewicht_stk AS gewicht,
            kwal.kwaliteit AS kwaliteit,
            opp.omschrijving AS oppervlak,
            IF(inh_plt.attest > 0, inh_plt.attest, id_plt.attest_std) AS attest,
            inh_plt.afschuin_id AS afschuin_id,
            inh_plt.opmerking AS opmerking
            FROM 3_product_folder AS fld
            LEFT JOIN 0_calculatie_inh_id AS id
            ON id.id = fld.product_id
            LEFT JOIN 0_calculatie_id AS calc
            ON calc.id = id.calculatie_id
            LEFT JOIN 0_calculatie_id_geg_plaat AS id_plt
            ON id_plt.calculatie_id_id = id.calculatie_id
            LEFT JOIN 0_calculatie_inh_id_geg_plaat AS inh_plt
            ON inh_plt.calculatie_inh_id = id.id
            LEFT JOIN calculatie_omtrek AS srt
            ON srt.id = inh_plt.soort_id
            LEFT JOIN kwaliteit AS kwal
            ON kwal.id = IF(inh_plt.kwaliteit_id > 0, inh_plt.kwaliteit_id, id_plt.kwaliteit_id_std)
            LEFT JOIN kwaliteit_opp AS opp
            ON opp.id = IF(inh_plt.oppervlak_id > 0, inh_plt.oppervlak_id, id_plt.oppervl_id_std)
            WHERE fld.folder_id = '".$row_b['folder_id']."' AND id.soort = 'plaat'
    ) a
    ORDER BY a.dikte DESC";

Answer 1

这两个查询都对结果进行排序，但是当您合并它们时，它们不会按照您期望的顺序显示。您可以添加一个包含两个现有查询的循环选择查询，然后按您需要的字段进行排序。

编辑:

类似于:

SELECT * FROM (
    SELECT CustomerID as ID, CustomerName as Name FROM Customers
UNION ALL
    SELECT EmployeeID as ID, LastName as Name FROM Employees
) a
ORDER BY a.ID;