mysql order by in union语句

irlmq6kh  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(347)

我有这个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 当搜索这个网站和谷歌似乎是一个子查询不能订购?但在这个stackoverflow页面是一个解决方案。
我是不是误解了什么?请给我建议。
按照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";
6vl6ewon

6vl6ewon1#

这两个查询都对结果进行排序,但是当您合并它们时,它们不会按您期望的顺序显示。您可以添加一个envolving select查询,其中包含两个现有查询,然后按所需字段排序。
编辑:
比如:

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;

相关问题