SQL Get X最后一个条目从'自定义'post_type计数用户个人的自定义post_type [英] SQL Get X last entries from a 'custom' post_type counting users individual number of custom post_type

查看：213
本文介绍了SQL Get X最后一个条目从'自定义'post_type计数用户个人的自定义post_type的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

如果可能，我想要查询一下：




  • 最后4位不同的使用者

  • 排除'ID'='1'

  • WITH post_type = 'custom'

  • 订阅日期或 ID $ c> DESC ）

  • 计算每个用户的自定义post_type总数（COUNT（）） >


这里是一个数据示例：

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

我有不完整的查询：

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

订阅 ID 在此类似于 date 的排序。
没有 GROUP BY'user'这样工作，但问题是，我想避免在这个选择2'自定义'一个用户的帖子：我需要一个选择4个不同的用户。 所以我的问题是 GROUP BY 。



如何解决这个问题？ / p>

最后一件事：

可以使用 COUNT（）

解决方案

尝试使用此选项中的每个用户的总自定义帖子this;）



查询1 ：

 从`posts`中选择t1。*，t2.userCnt 
 t1 
内部连接（
 select max（`ID`）as`ID`，`user`，count as userCnt 
 from`posts` 
其中`user`！='1'
和`post_type` ='custom'
 group by`user` 
）t2 t1.`ID` = t2.`ID`和t1.`user` = t2.`user` 
 order by t1.`ID` desc limit 4

查看此SqlFiddle 结果 ：

  | ID |用户|日期| title |状态| post_type | userCnt | 
 | ------ | ------ | --------------------- | ------- |  - ---------- | ----------- | --------- | 
 | 2783 | 5 | 2016-05-24 11:24:08 |标题|发表|定制| 2 | 
 | 2759 | 3 | 2016-05-07 14:00:22 |标题|发表|定制| 3 | 
 | 2757 | 12 | 2016-05-02 12:41:00 |标题|发表|定制| 2 | 
 | 2683 | 15 | 2016-04-22 20:27:45 |标题|发表|定制| 2 |

子查询 t2 将获得最大<$当 user！='1'和post_type ='custom'时，每个用户中的 ID 在 t1.ID = t2.ID和t1.user = t2.user 上加入 t1会得到我们的记录，其中max <$ c表 post 中的每个用户创建$ c> ID 。喜欢：2783，2759，2757，2683，2681，2652，2630，2617，2596，2215

最后用顺序和限制，当然你可以得到2783 2759，2757，2683。希望我没有误解你的问题。


I would like, if possible to get in one query:

  • The last 4 different users
  • Excluding 'ID' = '1'
  • WITH post_type = 'custom'
  • Ordered by date or ID (DESC)
  • Counting the total number of 'custom' post_type for each user (COUNT())

Here is a data example:

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"

The incomplete query that I have:

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

Ordering by ID is similar here than ordering by date. Without GROUP BY 'user' this works, but the problem is that I want to avoid in this selection 2 'custom' post for one user: I need a selection of 4 different users. So my problem is GROUP BY.

How can I solve this issue?

Last thing:
Is it possible with COUNT(), counting total 'custom' posts for each user in this selection, and returning the value in a new column?

解决方案

Try this;)

Query 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

Check this 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 |

Subquery t2 will get the max ID in each user when user != '1' and post_type = 'custom', then inner join t1 with t2 on t1.ID = t2.ID and t1.user = t2.user will get us record which has max ID by each user in table post. Like : "2783", "2759", "2757", "2683", "2681", "2652", "2630" , "2617", "2596", "2215".

And last with order by and limit, of course you can get "2783", "2759", "2757", "2683". Hope I did not mistake your question.

这篇关于SQL Get X最后一个条目从'自定义'post_type计数用户个人的自定义post_type的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆