SQL从“自定义” post_type计数的用户自定义post_type的个体数量

Question

我想获取X最后的条目，如果可能的话在一个查询来获取：

• 最后4个不同的用户
• 排除'ID' = '1'
WITH post_type
• = 'custom'
• 通过date或ID（DESC）
• 计数 '自定义' PO的总数目有序st_type为每个用户（COUNT（））

这里是一个数据例如：

Table Name: 'post' 

    ID | user |   Date   | title  |  status | post_type 
     |  |       |    |    | 
"2785" | "1" | "2016-05-24 18:49:15" | "Title"  | "published" | "page_post" 
"2783" | "5" | "2016-05-24 11:24:08" | "Title"  | "published" | "custom" 
"2781" | "1" | "2016-05-18 20:40:11" | "Title"  | "published" | "custom" 
"2759" | "3" | "2016-05-07 14:00:22" | "Title"  | "published" | "custom" 
"2757" | "12" | "2016-05-02 12:41:00" | "Title"  | "published" | "custom" 
"2756" | "1" | "2016-04-30 22:47:07" | "Title"  | "published" | "custom" 
"2755" | "5" | "2016-04-29 13:54:21" | "Title"  | "published" | "blog_post" 
"2754" | "1" | "2016-04-29 11:33:36" | "Title"  | "published" | "page_post" 
"2738" | "3" | "2016-05-06 12:45:58" | "Title"  | "published" | "custom" 
"2736" | "12" | "2016-04-24 17:17:04" | "Title"  | "published" | "custom" 
"2683" | "15" | "2016-04-22 20:27:45" | "Title"  | "published" | "custom" 
"2681" | "18" | "2016-04-21 00:20:55" | "Title"  | "published" | "custom" 
"2671" | "1" | "2016-04-11 18:38:57" | "Title"  | "published" | "other_post" 
"2652" | "4" | "2016-04-02 17:43:41" | "Title"  | "published" | "custom" 
"2651" | "5" | "2016-03-28 17:12:00" | "Title"  | "published" | "custom" 
"2639" | "18" | "2016-03-22 14:58:00" | "Title"  | "published" | "custom" 
"2630" | "19" | "2016-03-21 15:27:00" | "Title"  | "published" | "custom" 
"2617" | "14" | "2016-03-17 12:22:06" | "Title"  | "published" | "custom" 
"2616" | "5" | "2016-03-16 15:23:00" | "Title"  | "published" | "page_post" 
"2598" | "4" | "2016-03-14 15:27:29" | "Title"  | "published" | "custom" 
"2596" | "2" | "2016-03-10 17:43:00" | "Title"  | "published" | "custom" 
"2571" | "1" | "2016-03-09 14:19:31" | "Title"  | "published" | "blog_post" 
"2250" | "19" | "2016-02-29 12:15:48" | "Title"  | "published" | "custom" 
"2249" | "15" | "2016-02-29 09:45:35" | "Title"  | "published" | "custom" 
"2215" | "13" | "2016-02-22 18:21:54" | "Title"  | "published" | "custom" 
"2201" | "3" | "2016-02-15 17:40:00" | "Title"  | "published" | "custom" 
"1914" | "2" | "2015-11-13 12:08:00" | "Title"  | "published" | "other_post" 

，我有不完整的查询：

SELECT * 
FROM 'posts' 
WHERE 'user' != 1 AND 'post_type' = 'custom_type' 
GROUP BY 'user' 
ORDER BY 'ID' DESC LIMIT 4 

排序方式ID是这里类似比通过date订购。 没有GROUP BY 'user'这个工作，但问题是，我想避免在这个选择2个'自定义'发布一个用户：我需要选择4个不同的用户。 所以我的问题是GROUP BY。

我该如何解决这个问题？

最后一件事：
是否有可能与COUNT()，本次评选共统计“自定义”的帖子为每个用户，并在新列返回值？

Answer 1

试试这个;）

查询1：

select t1.*, t2.userCnt 
from `posts` t1 
inner join (
    select max(`ID`) as `ID`, `user`, count(1) as userCnt 
    from `posts` 
    where `user` != '1' 
    and `post_type` = 'custom' 
    group by `user` 
) t2 on t1.`ID` = t2.`ID` and t1.`user` = t2.`user` 
order by t1.`ID` desc limit 4 

入住这SqlFiddle Results：

| ID | user |    Date | title | status | post_type | userCnt | 
|------|------|---------------------|-------|-----------|-----------|---------| 
| 2783 | 5 | 2016-05-24 11:24:08 | Title | published | custom |  2 | 
| 2759 | 3 | 2016-05-07 14:00:22 | Title | published | custom |  3 | 
| 2757 | 12 | 2016-05-02 12:41:00 | Title | published | custom |  2 | 
| 2683 | 15 | 2016-04-22 20:27:45 | Title | published | custom |  2 | 

子查询t2将得到最大ID在每个用户在user != '1' and post_type = 'custom' ，然后inner join t1与t1.ID = t2.ID and t1.user = t2.user上的t2将得到我们最多ID的记录，其中每个user在表post中。如：“2783”，“2759”，“2757”，“2683”，“2681”，“2652”，“2630”，“2617”，“2596”，“2215”。

最后是order by和limit，当然你可以得到“2783”，“2759”，“2757”，“2683”。希望我没有把你的问题搞错。

Answer 2

您可以使用变量的查询，以获得最后4个用户的用户ID：

SELECT DISTINCT `user` 
FROM (
    SELECT user, 
     @cnt := IF(FIND_IN_SET(`user`, @uid) > 0, @cnt, 
        IF(@uid := CONCAT(@uid, ',', CAST(`user` AS CHAR(4))), 
         @cnt + 1, @cnt + 1)) AS cnt 
    FROM posts 
    CROSS JOIN (SELECT @cnt := 0, @uid := '') AS vars 
    WHERE `user` != 1 AND `post_type` = 'custom' 
    ORDER BY ID DESC) AS t 
WHERE t.cnt <= 4 

该查询返回以下值：

user 
---- 
5 
3 
12 
15 

使用上面的查询作为一个子查询，你可以得到预期的结果：

SELECT `user`, SUM(`post_type` = 'custom') AS cnt 
FROM posts 
WHERE `user` IN (... above query goes here ...) 
GROUP BY `user` 

输出：

user | cnt 
-----+----- 
3 | 3 
5 | 4 
12 | 2 
15 | 2 

Demo here