用if else和order by语句选择mysql

Without any training (and deaf) I developed a town portal system but I gave up on a specific select statement.

没有任何培训（和聋），我开发了一个城镇门户系统，但我放弃了一个特定的选择声明。

SELECT `id` FROM `test` ORDER BY FIND_IN_SET(`townid`,'townA'), date DESC LIMIT 3 ;

CREATE TABLE `test` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `townid` varchar(10) NOT NULL,
  `data` text NOT NULL,
  `date` date NOT NULL DEFAULT '2017-08-30'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test` (`id`, `townid`, `data`, `date`) VALUES
(1, 'townB', 'Data for Town B', '2017-08-29'),
(2, 'townA', 'Data for Town A', '2017-08-28'),
(3, 'townA', 'Data for Town A', '2017-08-27'),
(4, 'townA', 'Data for Town A', '2017-08-26'),
(5, 'townC', 'Data for Town C', '2017-08-25'),
(6, 'townB', 'Data for Town B', '2017-08-24');

ALTER TABLE `test`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `test`
  MODIFY `id` int(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;COMMIT;

The goal is that

目标是

townA select ID 2, 3 and 4
townA选择ID 2,3和4
townB select ID 1, 6 and 2
townB选择ID 1,6和2
townC select ID 5, 1 and 2
townC选择ID 5,1和2

In other words the town select his OWN articles first in date order with limit 3, ELSE select the limit 3 or remaining of the limit 3 in date order from any other town.

换句话说，城镇首先按照限制3的日期顺序选择他自己的OWN文章，ELSE从任何其他城镇选择日期顺序的限制3或剩余的限制3。

Neither select WHERE townid = 'townB' or WHERE townid IN ('townB') can deal with the 3 limit problem where I need THREE results even if there is only 1 of 2 entries for Town B

选择WHERE townid ='townB'或WHERE townid IN（'townB'）可以处理3限制问题，即使城镇B只有2个条目中的1个，我需要三个结果

2 个解决方案

#1

You were almost perfect in your sample provided and your query. The only thing you need to change is the order by find-in-set clause to DESCENDING.

在您提供的示例和查询中，您几乎是完美的。您需要更改的唯一内容是find-in-set子句到DESCENDING的顺序。

ORDER BY FIND_IN_SET(townid,'townA') DESC, date DESC

The find_in_set() will return a 1 or 0 based on the thing being found (1) or not (0). So you are ordering by those NOT found first, THEN found. You want the reverse. So, even if you have 99 items, and evenly split over each town A, B and C over a span of several days, having the "DESC"ending order on the FIND_IN_SET will say give me all of 'townA' records first, then anyone else after that. THEN, within each set of FOUND (or NOT) records, sort them in descending date order.

find_in_set（）将根据找到的东西（1）返回1或0或不返回0（0）。所以你先找到那些先找不到的人，然后找到。你想反过来。所以，即使你有99个项目，并且在几天的时间内在每个城镇A，B和C上均匀分配，在FIND_IN_SET上有“DESC”结束顺序会说先给我所有'townA'记录，然后之后的其他人。然后，在每组FOUND（或NOT）记录中，按降序日期顺序对它们进行排序。

Your query is actually just the one "DESC" clause away.

您的查询实际上只是一个“DESC”子句。

#2

SELECT * 
  FROM test 
 ORDER 
    BY townid = 'townc' DESC -- or 'towna' or 'townb'
     , date DESC
 LIMIT 3;

#1