mysql 过程 order by 子句条件-6ren

mysql 过程 order by 子句条件

转载作者：行者123 更新时间：2023-11-29 20:58:32

美好的一天

如何使条件语句下面的 sql 代码更改 asc 或 desc 值:

ORDER BY
  inventory_quantity.product_color_name DESC

我尝试过 case 语句，但出现错误

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 |表达式 |位置} [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 子句中的逻辑，以便按新创建的列进行排序

关于mysql 过程 order by 子句条件，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/37426555/

文章推荐： php - 将数组从 Controller 传递到模型 CodeIgniter

文章推荐： android - 这个字段怎么注入(inject)github android？

文章推荐： php - Select/SelectAll 删除在 PHP MySQL/AJAX Jquery 中不起作用

sql-server - 非聚集索引上的 Where 子句 Vs 额外连接和聚集索引上的 where 子句
我试图从一些 sql 查询中获得一些额外的性能，这些查询在一个字段上有一个 where 子句，它是一个非唯一的非聚集索引，它也是表 A 中的一个外键。该外键是主键在表 B 上，是聚集索引。我想知道的
batch-file - 结合 For/F 与 WMIC + WHERE 子句 + AND 子句
当包含在 FOR 中时，应该如何编写此 WMIC 命令脚本中的命令？ wmic service where (name="themes" and state="running") get 下面的代码不
TSQL:over 子句
请帮我理解如何订购对over子句的影响。我已经阅读了 msdn 和一本书，但仍然误解了。假设我们有这样的查询: SELECT Count(OrderID) over(Partition By Ye
SQL:HAVING 子句
参见如下SQL语句: SELECT datediff("d", MAX(invoice.date), Now) As Date_Diff , MAX(invoice.date) AS ma
CodeigniterWhere 子句
不知何故，对我来说构建这样的查询有点困难:给我所有链接名称不为空的导航条目 $query = $this->db->get_where('navigation',array('linkname'!==
SQL ALL IN 子句
我一直在寻找这个，但没有发现任何特别的东西。是否可以有一个像 ALL IN 一样的 SQL 查询？为了更好地解释，这是一个表结构。 Orders table OrderItem table (hav
SQL:Where 子句
SELECT DISTINCT Campaign_id FROM Impressions WHERE Date BETWEEN '2015-03-01' AND '2015-03-31' ; 上述查询
MyBatis if 子句
我尝试在 MyBatis 中遵循 if 子句并得到以下异常请帮助我确定这里的问题.. public class Student{ private Integer studId; private Str
sql - & IN WHERE 子句
很难说出这里要问什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或夸夸其谈，无法以目前的形式得到合理的回答。如需帮助澄清此问题以便重新打开，visit the help center . 关闭 1
MyBatis if 子句
我尝试在 MyBatis 中遵循 if 子句并得到以下异常请帮助我确定这里的问题.. public class Student{ private Integer studId; private Str
mySQL where in 子句
是否可以用 where in 子句做这样的事情，我需要使用 where in 查询以下数据。 select * FROM instructor AS i INNER JOIN teaches AS t
mysql - IF on WHERE 子句
嗨，我怎样才能让这个查询工作。我想要一个关于 where 子句的条件，如果 @BACHNUMB = ''，那么 WHERE 是 (h.sopnumbe = @SOPNUMBE) 否则 WHERE 是
c# - WHERE IN 子句
我在 MVC3 项目中工作。我浏览了一段时间并尝试了几个示例，但无法正常工作。我需要从 OrderForm 表中获取记录列表，其 DeptID 在我已经获得的另一个列表中。我知道我需要使用 Con
Mysql where 子句
select * from staff LEFT JOIN servicereservation on servicereservation.snic = staff.snic where servi
MySQL "WITH"子句
我正在尝试使用 MySQL 创建带有“WITH”子句的 View WITH authorRating(aname, rating) AS SELECT aname, AVG(quantity)
MySQL When 子句
我正在尝试使用 MySQL 创建触发器，但遇到错误。限制是:用户不得对他或她同时销售的商品出价。 Create Trigger before_insert_bid Before Insert on B
PostgreSQL WHERE IN 子句
我正在尝试在 PostgreSql 的 WHERE IN 子句中使用 split_part，如下所示。这里 Objcode 是 small int 类型，objection 可能像 1374,824,
mysql if in where 子句
这可能很简单，只是我太厚了 - 我试图阻止保留的元素在记录中被拾取，但只有当库存大于 0 时，我不知道该怎么做除非 "....WHERE blah blah AND (reserved = 0 OR
MySQL if then 子句
我总结了两个表中两列的行，即如下所示: SUM( tableA.age ) + sum( tableB.age) as 'Total Ages' 但在某些情况下，A表的结果为空，而B表的结果则不是。在
mysql AS 子句
我写了一个查询，从出生日期字段开始计算出一个人的年龄，然后使用 AS age 创建一个年龄字段。我的问题是，是否可以再次匹配那个年龄字段？像这样， SELECT `candidates`.`can

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

mysql 过程 order by 子句条件