MySQL - 以编程方式设置列顺序

Question

我正在从数据库中检索产品记录/销售订单。我正在使用 MySQL 的 UNION 功能连接表结果。

如果我设置我的查询仅检索 1 个产品，列的顺序看起来不错。附上截图链接: http://i.stack.imgur.com/xJRAb.png

但是，我想检索1 个或更多产品。如果我将查询设置为返回 1 个或多个产品。列的顺序困惑，看起来像屏幕截图链接: http://i.stack.imgur.com/xHRTg.png

这是我使用的查询:

SELECT * FROM(
SELECT bso.ID AS meta_id,  bso.ID AS order_item_id, '_qty' AS meta_key, 1 AS meta_value
FROM b_sale_order bso
WHERE bso.ID IN(832, 830)
UNION
SELECT bso.ID AS meta_id,  bso.ID AS order_item_id, '_tax_class' AS meta_key, '' AS meta_value
FROM b_sale_order bso
WHERE bso.ID IN(832, 830)
UNION
SELECT bsb.ORDER_ID AS meta_id, bsb.ORDER_ID AS order_item_id, '_product_id' AS meta_key, bsb.PRODUCT_ID AS meta_value
FROM b_sale_basket bsb
WHERE bsb.ORDER_id IN(832, 830)
UNION
SELECT bso.ID AS meta_id,  bso.ID AS order_item_id, '_variation_id' AS meta_key, '' AS meta_value
FROM b_sale_order bso
WHERE bso.ID IN(832, 830)
UNION
SELECT bso.ID AS meta_id,  bso.ID AS order_item_id, '_line_subtotal' AS meta_key, '' AS meta_value
FROM b_sale_order bso
WHERE bso.ID IN(832, 830)
UNION
SELECT bso.ID AS meta_id,  bso.ID AS order_item_id, '_line_total' AS meta_key, '' AS meta_value
FROM b_sale_order bso
WHERE bso.ID IN(832, 830)
UNION
SELECT bso.ID AS meta_id,  bso.ID AS order_item_id, '_line_tax' AS meta_key, 0 AS meta_value
FROM b_sale_order bso
WHERE bso.ID IN(832, 830)
UNION
SELECT bso.ID AS meta_id,  bso.ID AS order_item_id, '_line_subtotal_tax' AS meta_key, 0 AS meta_value
FROM b_sale_order bso
WHERE bso.ID IN(832, 830)
UNION
SELECT bso.ID AS meta_id, bso.ID AS order_item_id, 'method_id' AS meta_key, bsd.NAME AS meta_value
FROM b_sale_order bso
LEFT OUTER JOIN b_sale_delivery bsd ON bso.DELIVERY_ID = bsd.ID
WHERE bso.ID IN (832, 830)
UNION
SELECT bso.ID AS meta_id, bso.ID AS order_item_id, 'cost' AS meta_key, bsd.PRICE AS meta_value
FROM b_sale_order bso
LEFT OUTER JOIN b_sale_delivery bsd ON bso.DELIVERY_ID = bsd.ID
WHERE bso.ID IN (832, 830)
) sales_order_meta

注意:即使有 1 个或多个产品被退回，我仍然希望列的顺序看起来像第一个链接/图像。

请帮助我。

Answer 1

我假设您指的是行排序，而不是列排序。如果您希望行按特定顺序排列，则必须使用 order by 语句，否则 DBMS 可以按任何顺序返回行。即

select ... from (
    ...
    LEFT OUTER JOIN b_sale_delivery bsd ON bso.DELIVERY_ID = bsd.ID
    WHERE bso.ID IN (832, 830)
) sales_order_meta
order by ...

作为旁注，请勿使用 select *

如果您想按meta_id排列结果meta_id并以反射(reflect)联合顺序的方式，您可以向每个联合添加排序属性(例如origin)，例如:

SELECT ... FROM (
    SELECT bso.ID AS meta_id,  bso.ID AS order_item_id, '_qty' AS meta_key, 1 AS meta_value
         , 1 as origin
    FROM b_sale_order bso
    WHERE bso.ID IN(832, 830)
    UNION
    SELECT bso.ID AS meta_id,  bso.ID AS order_item_id, '_tax_class' AS meta_key, '' AS  meta_value
         , 2 as origin
    FROM b_sale_order bso
    WHERE bso.ID IN(832, 830)
    UNION
    ...
)
order by meta_id, origin

出于效率原因，我建议如下:

SELECT ... FROM(
    SELECT bso.ID AS meta_id,  bso.ID AS order_item_id
        ,  x.meta_key, x.meta_value, x.meta_key_order
    FROM b_sale_order bso
    CROSS JOIN ( select '_qty' AS meta_key, 1 AS meta_value, 1 as meta_key_order
                 union
                 select '_tax_class' AS meta_key, '' AS meta_value, 2 as meta_key_order
                 union
                 select '_variation_id' AS meta_key, '' AS meta_value, 4 as meta_key_order
                 union
                 select '_line_subtotal' AS meta_key, '' AS meta_value, 5 as meta_key_order
                 union
                 ...
               ) as x 
    WHERE bso.ID IN(832, 830)
   UNION
   -- 8
   SELECT bso.ID AS meta_id, bso.ID AS order_item_id, 'method_id' AS meta_key, bsd.NAME AS meta_value, 8 as meta_key_order
   FROM b_sale_order bso
   LEFT OUTER JOIN b_sale_delivery bsd ON bso.DELIVERY_ID = bsd.ID 
   WHERE bso.ID IN (832, 830)
   UNION
   -- 9
   SELECT bso.ID AS meta_id, bso.ID AS order_item_id, 'cost' AS meta_key, bsd.PRICE AS    meta_value, 9 as meta_key_order
   FROM b_sale_order bso
   LEFT OUTER JOIN b_sale_delivery bsd ON bso.DELIVERY_ID = bsd.ID
   WHERE bso.ID IN (832, 830)
   -- 3
  SELECT bsb.ORDER_ID AS meta_id, bsb.ORDER_ID AS order_item_id, '_product_id' AS meta_key, bsb.PRODUCT_ID AS meta_value, 3 as meta_key_order
  FROM b_sale_basket bsb
  WHERE bsb.ORDER_id IN(832, 830)
) sales_order_meta
order by meta_id, meta_key_order