时间戳

首页 > 解决方案 > Mysql查询以获取第一个位置的特定ID

mysql - Mysql查询以获取第一个位置的特定ID

问题描述

    SELECT `p`.`cat_pid`, `p`.`cat_id`, `c`.`name`, substring_index(group_concat(p.image_1 SEPARATOR ', '), ', ', 3) as images
FROM `products` `p`
LEFT JOIN `categories` `c` ON `p`.`cat_id` = `c`.`id`
WHERE `p`.`admin_response` = 1
AND `p`.`isactive` = 1
GROUP BY `p`.`cat_id`
ORDER BY `p`.`cat_id` ASC, `p`.`created_date` ASC

回应是：

> cat_pid   cat_id Ascending 1  name    images  
3   6   LED TVs     uploads/prod_file/0-1537255915-1.jpeg
3   7   Smart TVs   uploads/prod_file/0-1537256346-1.jpg
3   13  Sony    uploads/prod_file/3-1539672455-1.jpg
3   15  Digital SLRs    uploads/prod_file/0-1539246776-1.jpg
1   21  T- shirt    uploads/prod_file/0-1537179868-1.jpeg
1   22  Shirt   uploads/prod_file/0-1542977731-1.png
1   23  Jeans   uploads/prod_file/0-1539157883-1.jpeg
2   33  Ethnic Wear     uploads/prod_file/4-1539757235-1.png, uploads/prod...
2   34  Earcuff Earrings    uploads/prod_file/4-1539864784-1.jpg
2   36  Sarees  uploads/prod_file/4-1540189359-1.jpg
38  39  Boy T shit  uploads/prod_file/3-1539261170-1.jpg
4   43  Smartphones     uploads/prod_file/0-1537183102-1.jpeg
4   45  Basic Mobiles   uploads/prod_file/3-1539690488-1.jpg, uploads/prod...
38  50  CLOTHING SETS   uploads/prod_file/3-1539253806-1.jpg, uploads/prod...
1   56  Backpacks   uploads/prod_file/3-1539329576-1.jpg, uploads/prod...
1   57  Travel Luggage  uploads/prod_file/0-1539330363-1.jpg
54  63  Cookware    uploads/prod_file/4-1539934604-1.jpg
54  64  Gas stoves  uploads/prod_file/0-1540185182-1.jpg
71  72  Ionizer     uploads/prod_file/0-1543037560-1.png, uploads/prod..

现在我希望 cat_id 71 在第一个位置，其余数据应该与原来相同。

标签： mysql

解决方案

您可以使用 order by field()

SELECT `p`.`cat_pid`
    , `p`.`cat_id`
    , `c`.`name`
    , substring_index(group_concat(p.image_1 SEPARATOR ', '), ', ', 3) as images
FROM `products` `p`
LEFT JOIN `categories` `c` ON `p`.`cat_id` = `c`.`id`
WHERE `p`.`admin_response` = 1
AND `p`.`isactive` = 1
GROUP BY `p`.`cat_id`
ORDER BY field(`p`.`cat_pid`, 71) DESC, `p`.`cat_pid` ASC, `p`.`created_date` ASC

推荐阅读