mySQL查询 - 显示最受欢迎的项目

Question

I need to find the most popular occurrence of an item grouped by date and display the total of all items along with the name of this item. Is something like this possible in a single query?

note: If they are all of equal occurrence (see last 3 rows in Insert) than I can just show at random or the first or last occurrence (whichever is easiest).

If this can't be done in the sql then will have to run through the result and sort the data via PHP which seems like it'll be quite messy.

Edit: Sorry, I had the wrong total for '2009'08-04'. It should be 4.

An example of what I need:

+------------+---------------------+-------+
| date       | item                | total |
+------------+---------------------+-------+
| 2009-08-02 | Apple               |     5 |
| 2009-08-03 | Pear                |     2 |
| 2009-08-04 | Peach               |     4 |
| 2009-08-05 | Apple               |     1 |
| 2009-08-06 | Apple               |     3 |
+------------+---------------------+-------+

Here's an example table:

CREATE TABLE IF NOT EXISTS `test_popularity` (
  `date` datetime NOT NULL,
  `item` varchar(256) NOT NULL,
  `item_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `test_popularity` (`date`, `item`, `item_id`) VALUES
('2009-08-02 00:00:00', 'Apple', 1),
('2009-08-02 00:00:00', 'Pear', 3),
('2009-08-02 00:00:00', 'Apple', 1),
('2009-08-02 00:00:00', 'Apple', 1),
('2009-08-02 00:00:00', 'Pear', 0),
('2009-08-03 00:00:00', 'Pear', 3),
('2009-08-03 00:00:00', 'Peach', 2),
('2009-08-04 00:00:00', 'Apple', 1),
('2009-08-04 00:00:00', 'Peach', 2),
('2009-08-04 00:00:00', 'Peach', 2),
('2009-08-04 00:00:00', 'Pear', 3),
('2009-08-05 00:00:00', 'Apple', 1),
('2009-08-06 00:00:00', 'Apple', 1),
('2009-08-06 00:00:00', 'Peach', 2),
('2009-08-06 00:00:00', 'Pear', 3);

Answer 1

My initial suggestion was incorrect:

SELECT
  date, item, SUM(cnt)
FROM (
  SELECT
    date, item, count(item_id) AS cnt
  FROM test_popularity
  GROUP BY date, item_id
  ORDER BY cnt DESC
) t
GROUP BY date;

This erroneously assumes that the outside aggregation (by date) will select the first row of the inner derived table which was ordered by cnt. This behavior is, in fact, undefined and not guaranteed to be consistent.

Here is the proper solution:

SELECT
  t1.date, t1.item, 
  (SELECT COUNT(*) FROM test_popularity WHERE date = t1.date) as total
  # see note!
FROM test_popularity t1
JOIN (
  SELECT date, item, item_id, COUNT(item_id) as count
  FROM test_popularity
  GROUP BY date, item_id
) AS t2
ON t1.date = t2.date AND t1.item_id = t2.item_id
GROUP BY t1.date;

Note:

I added the (SELECT COUNT(*)) AS total because the question asked for this in one query. However, this will not scale as it is a correlated subquery. This means that for every t1.date the SELECT COUNT(*) subquery will run. Please benchmark and see if it performs suitably for your needs. If not, then I suggest getting the daily totals in a separate query. You would merge these results in your application.