同时显示已提升链接和未提升链接的一个查询

Question

我正在创建一个链接页面，其中将显示所有链接按live_date排序-这里的陷阱是有两种类型的链接-推广和非推广-另一个捕获是-列表应该按天分组，所以今天的链接将返回推广的链接，然后是非推广的链接-然后昨天推广和非推广。

Fridays links

-------------
Promoted link
Promoted link
Promoted link
Normal link 
Normal link

Thursdays Links
--------------
Promoted link
Normal link 
Normal link 
Normal link 

以此类推-

我的方案：

CREATE TABLE `links` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `email` varchar(200) NOT NULL,
  `link` varchar(200) NOT NULL,
  `reciprocal_checkbox` tinyint(1) NOT NULL,
  `recip` varchar(200) NOT NULL,
  `promoted` tinyint(1) NOT NULL,
  `live` tinyint(1) NOT NULL,
  `live_time` datetime NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

我是这样想的，但我认为它不会先创建所有的提升链接，然后再创建非提升链接(由bool定义)

SELECT title, link, live_time FROM links WHERE promoted = 1 UNION SELECT title, link, live_time FROM links WHERE promoted = 0 GROUP BY live_date

Answer 1

你不想分组，你只想给他们排序...

SELECT title, link, live_time 
FROM links 
ORDER BY live_date DESC, promoted DESC

PS -看起来您没有live_date列。你想要这样的东西吗？

SELECT title, link, live_time 
FROM links 
ORDER BY DATE(live_time) DESC, promoted DESC

Answer 2

这个怎么样？

SELECT title, link, live_time FROM links
ORDER BY live_date, promoted