gpt4 book ai didi

mysql - 从 MySql 中的两个字段任意排序

转载 作者:太空宇宙 更新时间:2023-11-03 10:59:53 24 4
gpt4 key购买 nike

我有一个困难的事情要做。我需要能够以任意方式对查询结果进行排序。

这是架构:

CREATE  TABLE `Wines` (
`ID` INT NOT NULL AUTO_INCREMENT,
`Vinyard` VARCHAR(45) NULL ,
`Quality` VARCHAR(45) NULL ,
`Sell_By` DATE NULL ,
PRIMARY KEY (`ID`) );


INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Draper', 'High', '2012-03-22');
INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Draper', 'Medium', '2014-07-16');
INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Draper', 'Medium', '2012-01-01');
INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Becks', 'High', '2014-07-16');
INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Becks', 'Ultra', '2013-02-02');
INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Stevens', 'Crap', '2014-08-16');
INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Stevens', 'Medium', '2014-01-01');
INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Stevens', 'Low', '2013-09-13');
INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Miller', 'Low', '2014-01-01');

现在我知道我可以获得一种简单的质量排序(不是按字母顺序排列的),如下所示:

 SELECT *,
CASE Quality
WHEN 'Ultra' THEN 1
WHEN 'High' THEN 2
WHEN 'Medium' THEN 3
WHEN 'Low' THEN 4
WHEN 'Crap' THEN 5
ELSE 6
END AS myORDER
FROM Wines
ORDER BY myOrder

这给了我

ID  Vinyard Quality Sell_By myORDER
5 Becks Ultra 2013-02-02 1
1 Draper High 2012-03-22 2
4 Becks High 2014-07-16 2
2 Draper Medium 2014-07-16 3
3 Draper Medium 2012-01-01 3
7 Stevens Medium 2014-01-01 3
8 Stevens Low 2013-09-13 4
9 Miller Low 2014-01-01 4
6 Stevens Crap 2014-08-16 5

好的,到目前为止一切顺利。现在是皱纹。

我需要移动中等质量的 Wine 并且只有中等质量的 Wine ,其销售截止日期已过期。所以结果会是

ID  Vinyard Quality Sell_By
5 Becks Ultra 2013-02-02
1 Draper High 2012-03-22
4 Becks High 2014-07-16
2 Draper Medium 2014-07-16
7 Stevens Medium 2014-01-01
8 Stevens Low 2013-09-13
9 Miller Low 2014-01-01
6 Stevens Crap 2014-08-16
3 Draper Medium 2012-01-01 <--Notice this one moved

我该怎么做?

最佳答案

ORDER BY Quality = 'Medium' AND Sell_By < CURDATE(), myOrder

这将按两个条件排序。

关于mysql - 从 MySql 中的两个字段任意排序,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/16721658/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com