我以为我有这个,但很明显我没有。从下表中,我试图在顶部显示做出最积极贡献(文章)的用户,然后是没有做出贡献的用户。该表很简单,artc_id是商品ID,artc_status是显示商品是否已获批准的状态。 0已获批准,1未获批准,然后是撰写本文的用户。

我想要达到的结果如下:

Total Contributions Positive    Contributing User
4                   4           2
3                   2           1
1                   1           4
3                   0           3




"id"    "artc_id"   "artc_status"   "artc_user" "artc_country"
"1"     "1"         "0"             "1"         "US"
"2"     "2"         "0"             "1"         "US"
"3"     "3"         "1"             "1"         "US"
"4"     "4"         "0"             "2"         "US"
"5"     "5"         "0"             "2"         "US"
"6"     "6"         "0"             "2"         "US"
"7"     "7"         "0"             "2"         "US"
"8"     "8"         "1"             "3"         "US"
"9"     "9"         "1"             "3"         "US"
"10"    "10"        "1"             "3"         "US"
"11"    "11"        "0"             "4"         "US"


我想出的SQL

select count(artc_status) as stats , artc_user from contributions where artc_status = 0 group by artc_user order by  stats desc;


我运气不佳,就像我上面发布的那样。你能帮忙吗?这完全超出了我。

最佳答案

select
     count(artc_status) as stats ,
     count(case when artc_status=1 then 1 end) Positive,
     artc_user[Contributing User]
from
    contributions
group by
    artc_user
order by  stats desc;

关于mysql - 选择做出最积极贡献的用户,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/18874518/

10-12 23:38