DL部落

Table name : car

欄位 : ('id' => 流水號, 'category' => 分類, 'clicks' => 點擊次數)

要搜尋car內的所有分類點擊次數的前4名, 用一段SQL處理

--
-- 表的結構 `car`
--

CREATE TABLE IF NOT EXISTS `car` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category` tinyint(4) NOT NULL,
`clicks` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=23 ;

--
-- 表中的數據 `car`
--

INSERT INTO `car` (`id`, `category`, `clicks`) VALUES
(1, 1, 10),
(2, 1, 12),
(3, 1, 5),
(4, 1, 7),
(5, 1, 5),
(6, 1, 9),
(7, 1, 7),
(8, 2, 58),
(9, 2, 0),
(10, 2, 5),
(11, 2, 4),
(12, 2, 1),
(13, 3, 4),
(14, 4, 78),
(15, 4, 4),
(16, 4, 87),
(17, 3, 4),
(18, 3, 45),
(19, 3, 5),
(20, 4, 58),
(21, 4, 4),
(22, 4, 12);

執行這段SQL:

SELECT c . *
FROM (
    car AS c
)
WHERE 4 > (
    SELECT COUNT( * )
    FROM car
    WHERE category = c.category
    AND clicks > c.clicks
)
ORDER BY c.category ASC , c.clicks DESC

結果: