從表中獲取每個月和使用者的最高記錄數

我有一個表，其中翻譯與添加翻譯一起儲存user_id。我想創建一個顯示“當月翻譯”（當月翻譯最多的人）的查詢。

我有一個查詢要獲得所有翻譯的頂級翻譯：

SELECT user_id, COUNT(user_id) 
FROM gp_translations
WHERE (STATUS='current' OR STATUS='old')
GROUP BY user_id
ORDER BY COUNT(user_id) 

我有一個顯示年/月的查詢：

SELECT distinct CONCAT(YEAR(`date_added`),'/', MONTH(`date_added`)) AS `Year/Month` 
FROM `gp_translations` t1
GROUP BY YEAR(`date_added`), MONTH(`date_added`)
ORDER BY `date_added` DESC

但是當將兩者結合起來時，我慘遭失敗。查詢無休止地執行：

SELECT distinct CONCAT(YEAR(`date_added`),'/', MONTH(`date_added`)) AS `Year/Month`, (
SELECT distinct user_id
FROM gp_translations t2
WHERE (STATUS='current' OR STATUS='old') AND     YEAR(t2.date_added)=YEAR(t1.date_added)
AND MONTH(t2.date_added)=MONTH(t1.date_added)
GROUP BY user_id
ORDER BY COUNT(user_id) DESC
LIMIT 1)
FROM `gp_translations` t1
GROUP BY YEAR(`date_added`), MONTH(`date_added`)
ORDER BY `date_added` DESC

該表gp_translations具有以下結構：

CREATE TABLE `gp_translations` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`original_id` INT(10) NULL DEFAULT NULL,
`translation_set_id` INT(10) NULL DEFAULT NULL,
`translation_0` TEXT NOT NULL,
`translation_1` TEXT NULL,
`translation_2` TEXT NULL,
`translation_3` TEXT NULL,
`translation_4` TEXT NULL,
`translation_5` TEXT NULL,
`user_id` INT(10) NULL DEFAULT NULL,
`status` VARCHAR(20) NOT NULL DEFAULT 'waiting',
`date_added` DATETIME NULL DEFAULT NULL,
`date_modified` DATETIME NULL DEFAULT NULL,
`warnings` TEXT NULL,
PRIMARY KEY (`id`),
INDEX `original_id` (`original_id`),
INDEX `user_id` (`user_id`),
INDEX `translation_set_id` (`translation_set_id`),
INDEX `translation_set_id_status` (`translation_set_id`, `status`),
INDEX `date_added` (`date_added`),
INDEX `warnings` (`warnings`(1))
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=342587;

我知道我在這裡做錯了什麼。但是我找不到以不錯的速度正確合併兩者的方法。任何幫助表示讚賞。MySQL 版本是 5.5.34

由於缺少 windows 功能和 cte，mysql 版本相當冗長，但我希望它能在合理的時間內給你想要的結果（我知道它一點也不快）：

SELECT a.*, c.*
FROM 
(
  SELECT yr,mn, MAX(translations_per_month) as max_user_translations
  FROM
  (
    SELECT  YEAR(`date_added`) as yr, MONTH(`date_added`) as mn, user_id, 
    COUNT(1) as translations_per_month
    FROM `gp_translations` t1
    INNER JOIN gp_users u ON 
     (u.id = t1.user_id AND u.STATUS='current' OR u.STATUS='old')
    GROUP BY  YEAR(`date_added`), MONTH(`date_added`),user_id
   )b
   GROUP BY yr,mn
)a
INNER JOIN 
(
  SELECT  YEAR(`date_added`) as yr, MONTH(`date_added`) as mn, user_id, display_name,
  COUNT(1) as translations_per_month
  FROM `gp_translations` t1
  INNER JOIN gp_users u ON (u.id = t1.user_id AND u.STATUS='current' OR u.STATUS='old')
  GROUP BY YEAR(`date_added`), MONTH(`date_added`),user_id, display_name
)c ON  (c.yr = a.yr and c.mn = a.mn and c.translations_per_month = a.max_user_translations)

另外，我猜測該status列在gb_userstable 中；如果不是，則需要對查詢進行一些修改。

我不知道您是否仍然需要此查詢，或者即使我正確理解了您的問題。但是，如果您需要一個顯示“本月翻譯者”（每月頂級翻譯者）的查詢。

SELECT CONCAT(yr,'/', mnt) AS `Year/Month`,user_id,totalTrans
FROM             
(                
   SELECT YEAR(date_added) as yr ,MONTH(date_added)as mnt, user_id, COUNT(1) AS totalTrans 
   FROM gp_translations
   WHERE (STATUS='current' OR STATUS='old')
   GROUP BY yr,mnt,user_id
   ORDER BY totalTrans DESC
)a               
GROUP BY yr,mnt ;

此查詢應該更快，但它仍然不使用您的索引。請注意，如果兩個使用者在一個月內翻譯了相同數量的文件，則只會顯示其中一個。

引用自：https://dba.stackexchange.com/questions/53075