美好的一天，
如何使下面的SQL代码成为条件代码更改ASC或DESC值：
ORDER BY
inventory_quantity.product_color_name DESC
我尝试过案例陈述，但出现错误
ORDER BY
CASE product_color_name_sort WHEN product_color_name_sort = 'asc' THEN inventory_quantity.product_color_name END ASC
也尝试了if语句，但仍然错误
ORDER BY
inventory_quantity.product_color_name IF(product_color_name_sort = 'asc', 'ASC', 'DESC')
这可能吗？
过程代码
delimiter //
CREATE DEFINER=`root`@`localhost`
PROCEDURE `stocks_quantities`(
IN `depot_id` INT,
IN `qa` INT,
IN `product_dimension_id` INT,
IN `product_color_id` INT,
IN `product_unit_name` INT,
IN `product_status_id` INT,
IN `product_name_sort` VARCHAR(10) CHARSET utf8,
IN `product_color_name_sort` VARCHAR(10) CHARSET utf8,
IN `product_dimension_name_sort` VARCHAR(10) CHARSET utf8,
IN `i_limit` INT,
IN `i_offset` INT
)
NO SQL
begin
DECLARE i_limit_temp INT DEFAULT 0;
SET i_limit_temp = COALESCE(NULLIF(i_limit, ''));
IF i_limit = 0 OR i_limit = '' OR i_limit IS NULL THEN
SET i_limit_temp = 1000;
END IF;
SELECT
*
FROM
(
SELECT
products.id AS 'product_id',
products.name AS 'product_name',
products.model AS 'product_model',
products.qty_low_alert AS 'product_qty_low_alert',
COALESCE(tbl_included_inventories.product_total,0) AS 'included_inventory_total',
COALESCE(tbl_received_pos.product_total,0) AS 'received_po_total',
COALESCE(tbl_received_tos.product_total,0) AS 'received_to_total',
COALESCE(tbl_excluded_inventories.product_total,0) AS 'excluded_inventory_total',
COALESCE(tbl_transfer_orders.product_total,0) AS 'transfer_order_total',
COALESCE(tbl_official_receipts.product_total,0) AS 'official_receipt_total',
COALESCE(tbl_missed_pos.product_total,0) AS 'missed_purchased_order_total',
COALESCE(tbl_purchase_orders.product_total,0) AS 'purchased_order_total',
COALESCE(tbl_wrong_send_pos.product_total,0) AS 'wrong_send_purchased_order_total',
COALESCE(tbl_sales_returns.product_total,0) AS 'sales_return_total',
(
COALESCE(tbl_purchase_orders.product_total,0)
-
(
COALESCE(tbl_received_pos.product_total,0)
+
COALESCE(tbl_missed_pos.product_total,0)
)
) AS 'af',
(
COALESCE(tbl_excluded_inventories.product_total,0) +
COALESCE(tbl_transfer_orders.product_total,0) +
COALESCE(tbl_official_receipts.product_total,0)
) AS 'tr',
(
COALESCE(tbl_included_inventories.product_total,0) +
COALESCE(tbl_received_pos.product_total,0) +
COALESCE(tbl_received_tos.product_total,0) +
COALESCE(tbl_sales_returns.product_total,0)
) -
(
COALESCE(tbl_excluded_inventories.product_total,0) +
COALESCE(tbl_transfer_orders.product_total,0) +
COALESCE(tbl_official_receipts.product_total,0)
)
+ COALESCE(tbl_wrong_send_pos.product_total,0)
AS 'qa',
tbl_product_colors.id AS product_color_id,
tbl_product_dimensions.id AS product_dimension_id,
products.product_status_id AS product_status_id,
tbl_product_colors.name AS product_color_name,
tbl_product_dimensions.name AS product_dimension_name,
tbl_product_units.name AS product_unit_name
FROM
products
LEFT JOIN
(
SELECT
included_inventory_details.product_id,
SUM(included_inventory_details.quantity_included) AS product_total
FROM
included_inventories
LEFT JOIN
included_inventory_details
ON
included_inventories.id = included_inventory_details.included_inventory_id
WHERE
included_inventories.depot_id = COALESCE(NULLIF(depot_id, ''), included_inventories.depot_id)
GROUP BY
included_inventory_details.product_id
)
AS tbl_included_inventories ON tbl_included_inventories.product_id = products.id
LEFT JOIN
(
SELECT
received_po_details.product_id,
SUM(received_po_details.quantity_received) AS product_total
FROM
received_pos
LEFT JOIN
received_po_details
ON
received_pos.id = received_po_details.received_po_id
WHERE
received_pos.depot_id = COALESCE(NULLIF(depot_id, ''), received_pos.depot_id)
GROUP BY
received_po_details.product_id
)
AS tbl_received_pos ON tbl_received_pos.product_id = products.id
LEFT JOIN
(
SELECT
received_to_details.product_id,
SUM(received_to_details.quantity_received) AS product_total
FROM
received_tos
LEFT JOIN
received_to_details
ON
received_tos.id = received_to_details.received_to_id
WHERE
received_tos.depot_id = COALESCE(NULLIF(depot_id, ''), received_tos.depot_id)
GROUP BY
received_to_details.product_id
)
AS tbl_received_tos ON tbl_received_tos.product_id = products.id
LEFT JOIN
(
SELECT
excluded_inventory_details.product_id,
SUM(excluded_inventory_details.quantity_excluded) AS product_total
FROM
excluded_inventories
LEFT JOIN
excluded_inventory_details
ON
excluded_inventories.id = excluded_inventory_details.excluded_inventory_id
WHERE
excluded_inventories.depot_id = COALESCE(NULLIF(depot_id, ''), excluded_inventories.depot_id)
GROUP BY
excluded_inventory_details.product_id
)
AS tbl_excluded_inventories ON tbl_excluded_inventories.product_id = products.id
LEFT JOIN
(
SELECT
transfer_order_details.product_id,
SUM(transfer_order_details.quantity_transfering) AS product_total
FROM
transfer_orders
LEFT JOIN
transfer_order_details
ON
transfer_orders.id = transfer_order_details.transfer_order_id
WHERE
transfer_orders.releasing_depot_id = COALESCE(NULLIF(depot_id, ''), transfer_orders.releasing_depot_id)
GROUP BY
transfer_order_details.product_id
)
AS tbl_transfer_orders ON tbl_transfer_orders.product_id = products.id
LEFT JOIN
(
SELECT
official_receipt_details.product_id,
SUM(official_receipt_details.quantity_released) AS product_total
FROM
official_receipts
LEFT JOIN
official_receipt_details
ON
official_receipts.id = official_receipt_details.official_receipt_id
WHERE
official_receipts.depot_id = COALESCE(NULLIF(depot_id, ''), official_receipts.depot_id)
GROUP BY
official_receipt_details.product_id
)
AS tbl_official_receipts on tbl_official_receipts.product_id = products.id
LEFT JOIN
(
SELECT
missed_po_details.product_id,
SUM(missed_po_details.quantity_missed) AS product_total
FROM
missed_pos
LEFT JOIN
missed_po_details
ON
missed_pos.id = missed_po_details.missed_po_id
WHERE
missed_pos.depot_id = COALESCE(NULLIF(depot_id, ''), missed_pos.depot_id)
GROUP BY
missed_po_details.product_id
)
AS tbl_missed_pos ON tbl_missed_pos.product_id = products.id
LEFT JOIN
(
SELECT
purchase_order_details.product_id,
SUM(purchase_order_details.quantity_ordered) AS product_total
FROM
purchase_orders
LEFT JOIN
purchase_order_details
ON
purchase_orders.id = purchase_order_details.purchase_order_id
WHERE
purchase_orders.depot_id = COALESCE(NULLIF(depot_id, ''), purchase_orders.depot_id)
GROUP BY
purchase_order_details.product_id
)
AS tbl_purchase_orders ON tbl_purchase_orders.product_id = products.id
LEFT JOIN
(
SELECT
wrong_send_po_details.product_id,
SUM(wrong_send_po_details.quantity_wrong_send) AS product_total
FROM
wrong_send_pos
LEFT JOIN
wrong_send_po_details
ON
wrong_send_pos.id = wrong_send_po_details.wrong_send_po_id
WHERE
wrong_send_pos.depot_id = COALESCE(NULLIF(depot_id, ''), wrong_send_pos.depot_id)
GROUP BY
wrong_send_po_details.product_id
)
AS tbl_wrong_send_pos ON tbl_wrong_send_pos.product_id = products.id
LEFT JOIN
(
SELECT
sales_return_details.product_id,
SUM(sales_return_details.quantity_received) AS product_total
FROM
sales_returns
LEFT JOIN
sales_return_details
ON
sales_returns.id = sales_return_details.sales_return_id
WHERE
sales_returns.depot_id = COALESCE(NULLIF(depot_id, ''), sales_returns.depot_id)
GROUP BY
sales_return_details.product_id
)
AS tbl_sales_returns ON tbl_sales_returns.product_id = products.id
LEFT JOIN
(
SELECT
product_colors.id,
product_colors.name
FROM
product_colors
)
AS tbl_product_colors ON tbl_product_colors.id = products.product_color_id
LEFT JOIN
(
SELECT
product_dimensions.id,
product_dimensions.name
FROM
product_dimensions
)
AS tbl_product_dimensions ON tbl_product_dimensions.id = products.product_dimension_id
LEFT JOIN
(
SELECT
product_units.id,
product_units.name
FROM
product_units
)
AS tbl_product_units ON tbl_product_units.id = products.product_unit_id
) inventory_quantity
WHERE
inventory_quantity.qa >= COALESCE(NULLIF(qa, ''), 0)
AND
inventory_quantity.product_dimension_id = COALESCE(NULLIF(product_dimension_id, ''), inventory_quantity.product_dimension_id)
AND
inventory_quantity.product_color_id = COALESCE(NULLIF(product_color_id, ''), inventory_quantity.product_color_id)
AND
inventory_quantity.product_unit_name = COALESCE(NULLIF(product_unit_name, ''), inventory_quantity.product_unit_name)
AND
inventory_quantity.product_status_id = COALESCE(NULLIF(product_status_id, ''), inventory_quantity.product_status_id)
ORDER BY
CASE product_name_sort WHEN product_name_sort = 'asc' THEN inventory_quantity.product_name END ASC,
CASE product_name_sort WHEN product_name_sort = 'desc' THEN inventory_quantity.product_name END DESC,
CASE product_color_name_sort WHEN product_color_name_sort = 'asc' THEN inventory_quantity.product_color_name END ASC,
CASE product_color_name_sort WHEN product_color_name_sort = 'desc' THEN inventory_quantity.product_color_name END DESC,
CASE product_dimension_name_sort WHEN product_dimension_name_sort = 'asc' THEN inventory_quantity.product_dimension_name END ASC,
CASE product_dimension_name_sort WHEN product_dimension_name_sort = 'desc' THEN inventory_quantity.product_dimension_name END DESC
LIMIT
i_limit_temp
OFFSET
i_offset;
end; //
delimiter ;
谢谢
根据MySQL文档，这是不可能的。
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
参考： http://dev.mysql.com/doc/refman/5.7/en/select.html
参考2：http://dev.mysql.com/doc/refman/5.7/en/expressions.html
您可以尝试移动子句中的逻辑SELECT以便按新创建的列排序
CASE表达式中存在语法错误：
CASE product_color_name_sort WHEN product_color_name_sort = 'asc' THEN inventory_quantity.product_color_name END ASC
应该是：
CASE WHEN product_color_name_sort = 'asc' THEN inventory_quantity.product_color_name END ASC
关于条件排序，这里有一种方法可以做到：
ORDER BY
CASE
WHEN product_color_name_sort = 'asc' THEN inventory_quantity.product_color_name
END ASC,
CASE
WHEN product_color_name_sort = 'DESC' THEN inventory_quantity.product_color_name
END DESC
我终于明白了
CASE
when product_name_sort <> 'asc' then 0
when product_name_sort = 'asc' then inventory_quantity.product_name
END ASC,
CASE
when product_name_sort <> 'desc' then 0
when product_name_sort = 'desc' then inventory_quantity.product_name
END DESC