SELECT u.*, s.date, s.type
FROM user u
LEFT JOIN (
SELECT ss.user_id, MAX(ss.date) AS max_date
FROM stats AS ss
GROUP BY ss.user_id
) AS ms
ON ms.user_id = u.id
LEFT JOIN stats AS s
ON s.user_id = ms.user_id AND s.date = ms.max_date
UPDATE category SET number = 0;
SET @old_category = -1;
SET @num = -1;
DROP TABLE IF EXISTS temp;
CREATE TABLE temp (
article_id int,
sort int,
category_id int,
number integer
) ENGINE = MEMORY;
INSERT INTO temp
SELECT
t.article_id,
t.sort,
t.category_id,
t.number
FROM(
SELECT
t.article_id,
t.sort,
t.category_id,
@old_category,
@num := CASE
WHEN @old_category <> t.category_id THEN 0
ELSE @num + 1
END AS number,
@old_category := t.category_id
FROM (
SELECT
a.article_id,
a.sort,
c.category_id
FROM category AS c
INNER JOIN article AS a ON a.article_id = c.article_id
ORDER BY c.category_id, a.sort
) AS t
) AS t;
UPDATE category AS c
INNER JOIN temp AS t ON t.article_id = c.article_id
SET c.number = t.number;
DROP TABLE temp;
SELECT c.*, a.sort FROM category AS c
INNER JOIN article AS a ON a.article_id = c.article_id
ORDER BY c.category_id, a.sort