mysql通过具有两个不同值的记录来排序行

i have records that have value equal to 1 or 2 i want to order rows by this record like this

我有值等于1或2的记录我想按此记录订购行

1
2
1
2
1
2
1

how can i do this order by only orders by DESC or ASC ?

我怎么能只通过DESC或ASC的订单来做这个订单？

note : there is a primary key id, and there is a created date

注意：有一个主键ID，并且有一个创建日期

CREATE TABLE `users` (
    `uid` INT(11) NOT NULL AUTO_INCREMENT,
    `phone_number` VARCHAR(16) NOT NULL,
    `password` VARCHAR(100) NOT NULL,
    `first_name` VARCHAR(100) NOT NULL,
    `last_name` VARCHAR(100) NOT NULL,
    `sex` TINYINT(4) NOT NULL, << this will have only 1 or 2
    `created_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `account_status` TINYINT(1) NOT NULL, 
    PRIMARY KEY (`uid`)
);

1 个解决方案

#1

Hope something line this can help you.

希望有些东西可以帮到你。

SELECT t.*
FROM (
  SELECT STRAIGHT_JOIN  uid, first_name , 
    @y:=IF(@sex=sex,@y+1,1) AS rnk, @sex:=sex AS sex
  FROM (SELECT @sex:=NULL) AS x
  JOIN users
  ORDER BY sex, uid DESC
) AS t
ORDER BY rnk ASC, sex ASC;