Mysql自定义排序

Question

我有一个包含不同类别的数据库表，其中包含不同的产品，每个类别都有一些优先级。假设cat-1有5个产品，cat-2包含3个产品，cat-3包含3个产品，cat-4包含2个产品。

在展示产品时，顺序如下所示。

如果类别具有相同的优先级(假设cat-1，cat-2优先级= 1，cat-3优先级= 2，cat-4优先级= NULL)，则产品将显示如下。

c1p1、c2p1、c1p2、c2p2、c1p3、c2p3、c1p4、c1p5、c3p1、c3p2、c3p3、c4p1、c4p2。

如果类别具有相同的优先级(假设cat-1、cat-2优先级= 1、cat-3和cat-4优先级= 2)，则产品将显示如下。

c1p1、c2p1、c1p2、c2p2、c1p3、c2p3、c1p4、c1p5、c3p1、c4p1、c3p2、c4p2、c3p3。

如果类别具有不同的优先级(假设cat-1优先级= 2，cat-2优先级= 1，cat-3优先级=3，cat-4优先级= Null)，则产品将显示如下。

c2p1、c2p2、c2p3、c1p1、c1p2、c1p3、c1p4、c1p5、c3p1、c3p2、c3p3、c4p1、c4p2。

这里c=类别，p=产品。

在Mysql中，这种类型的排序是可能的。请帮帮忙。

以下是数据库表的结构和示例数据-

   CREATE TABLE IF NOT EXISTS `categories` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `priority` int(11) DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ;

   INSERT INTO `categories` (`id`, `name`, `priority`) VALUES
   (1, 'c1', 1),
   (2, 'c2', 1),
   (3, 'c3', 2),
   (4, 'c4', NULL);

   CREATE TABLE IF NOT EXISTS `products` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `category_id` int(11) NOT NULL,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
   ) ;

INSERT INTO `products` (`id`, `category_id`, `name`) VALUES
(1, 1, 'c1p1'),
(2, 1, 'c1p2'),
(3, 1, 'c1p3'),
(4, 1, 'c1p4'),
(5, 1, 'c1p5'),
(6, 2, 'c2p1'),
(7, 2, 'c2p2'),
(8, 2, 'c2p3'),
(9, 3, 'c3p1'),
(10, 3, 'c3p2'),
(11, 3, 'c3p3'),
(12, 4, 'c4p1'),
(13, 4, 'c4p2');

Answer 1

最后我得到了一个更好的解决方案。我在products表中添加了一个额外的字段。这样做的目的是给每个类别的产品一个序列号。我最终的products表结构如下所示。

CREATE TABLE IF NOT EXISTS `products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`sl_no` int(11) NOT NULL,
 PRIMARY KEY (`id`)
);

INSERT INTO `products` (`id`, `category_id`, `name`, `sl_no`) VALUES
(1, 1, 'c1p1', 1),
(2, 1, 'c1p2', 2),
(3, 1, 'c1p3', 3),
(4, 1, 'c1p4', 4),
(5, 1, 'c1p5', 5),
(6, 2, 'c2p1', 1),
(7, 2, 'c2p2', 2),
(8, 2, 'c2p3', 3),
(9, 3, 'c3p1', 1),
(10, 3, 'c3p2', 2),
(11, 3, 'c3p3', 3),
(12, 4, 'c4p1', 1),
(13, 4, 'c4p2', 2);

查询看起来像这样-

SELECT  `Category`.`id` ,  `Category`.`name` ,  `Category`.`priority` ,    `Product`.`name` ,  `Product`.`category_id` 
FROM  `categories` AS  `Category` 
INNER JOIN  `products` AS  `Product` 
WHERE  `Category`.`id` =  `Product`.`category_id` 
ORDER BY CASE WHEN  `Category`.`priority` IS NULL 
THEN 1 
ELSE 0 
END ASC ,  `Category`.`priority` ASC ,  `Product`.`sl_no` ,  `Product`.`category_id`;

Answer 2

您可以创建一个具有优先级的表：

create table priorities (
  category varchar(255),
  priority int null);

然后您可以通过以下方式选择产品：

select
  products.*
from
  products inner join priorities
  on products.category = priorities.category
order by
  priorities.priority is null, -- this to put null values at the end
  priorities.priority,
  products.id                  -- or some other field

EDIT:这可能就是您要查找的内容：

select
  products.name
from
  products inner join categories
  on products.category_id = categories.id
order by
  categories.priority is null,
  categories.priority,
  substr(products.name,3),
  categories.name

Answer 3

这是根据需求获得序列的间接解决方案。

当我使用PHP获取数据时，首先我编写了一个sql查询来根据优先级获取数据。

SELECT `Category`.`id`, `Category`.`name`, `Category`.`priority` , `Product`.`name`, `Product`.`category_id` FROM `categories` AS `Category` INNER JOIN `products` AS `Product` WHERE `Category`.`id` = `Product`.`category_id` ORDER BY CASE WHEN `Category`.`priority` IS NULL THEN 1 ELSE 0 END ASC, `Category`.`priority` ASC;

然后，我做了一个简单的数组操作来获得一个数组，如下所示。

array(
(int) 1 => array(
    (int) 0 => array(
        (int) 0 => '1',
        (int) 1 => '6'
    ),
    (int) 1 => array(
        (int) 0 => '2',
        (int) 1 => '7'
    ),
    (int) 2 => array(
        (int) 0 => '3',
        (int) 1 => '8'
    ),
    (int) 3 => array(
        (int) 0 => '4'
    ),
    (int) 4 => array(
        (int) 0 => '5'
    ),
),  
(int) 2 => array(
    (int) 0 => array(
        (int) 0 => '9'
    ),
    (int) 1 => array(
        (int) 0 => '10'
    ),
    (int) 2 => array(
        (int) 0 => '11'
    ),      
),
'' => array(
    (int) 0 => array(
        (int) 0 => '12'         
    ),
    (int) 1 => array(
        (int) 0 => '13'

    )
)
)

适用于系列c1p1、c2p1、c1p2、c2p2、c1p3、c2p3、c1p4、c1p5、c3p1、c3p2、c3p3、c4p1、c4p2。其中数组键是优先级，叶值是产品ids。

然后，我从上面的数组中生成一个i字符串，如下所示。

  1,6,2,7,3,8,4,5,9,10,11,12,13

并最终按如下方式编写查询。

 SELECT * 
 FROM  `products` 
 WHERE  `id` 
 IN ( 1, 6, 2, 7, 3, 8, 4, 5, 9, 10, 11, 12, 13 ) 
 ORDER BY FIELD( id, 1, 6, 2, 7, 3, 8, 4, 5, 9, 10, 11, 12, 13 );