时间戳

首页 > 解决方案 > 在语法中添加变量

mysql - 在语法中添加变量

问题描述

在这个现场运行的例子中

我想在检索到的数据中添加用户 ID

我用这个

;with postvotes as
(
select v.creationdate,
       v.userid
     , p.id postid 
     , case v.votetypeid
       when  1 then 'accepts'
       when 2 then 'up-votes'
       when 3 then 'down-votes'
       when 9 then 'bounty_recieved'
       end  vote_type
     , case p.posttypeid 
       when 1 then 'question'
       when 2 then 'answer'
       end post_type
     , sum(case  v.votetypeid 
           when 1 then 15
           when 2 then 10
           when 3 then -2
           when 9 then bountyamount
           end
       ) reputation_change

from votes v
inner join posts p on p.id = v.postid
where p.owneruserid = ##userid?1719510##
and posttypeid in (1,2)
and votetypeid in (1,2,3,9)
group by v.creationdate, p.id, votetypeid, posttypeid
),
bounties as 
(
select v.creationdate
     , v.postid postid 
     , 'bounty-offered' vote_type
     , 'question' post_type
     , sum(bountyamount) reputation_change
from votes v
where v.userid = ##userid?1719510##
and votetypeid = 8
group by v.creationdate, v.postid, votetypeid
),
approvededits as
(
select se.creationdate
     , se.postid postid 
     , 'approved-edit' vote_type
     , case p.posttypeid 
       when 1 then 'question'
       when 2 then 'answer'
       end  post_type
     , sum(2) reputation_change
from suggestededits se
inner join posts p on p.id = se.postid
where se.Owneruserid = ##userid?1719510##
and approvaldate is not null
group by se.creationdate, se.postid, posttypeid
)


select *
from postvotes
union
select *
from bounties
union 
select *
from approvededits

但我收到此错误：选择列表中的“votes.UserId”列无效，因为它既不包含在聚合函数中，也不包含在 GROUP BY 子句中。

我该如何解决？

标签： mysqlsql

解决方案

您忘记按列表在您的组中添加用户 ID。所以你更新的查询可能看起来像 -

;with postvotes as
(
select v.creationdate,
     , p.id postid 
     , case v.votetypeid
       when  1 then 'accepts'
       when 2 then 'up-votes'
       when 3 then 'down-votes'
       when 9 then 'bounty_recieved'
       end  vote_type
     , case p.posttypeid 
       when 1 then 'question'
       when 2 then 'answer'
       end post_type
     , sum(case  v.votetypeid 
           when 1 then 15
           when 2 then 10
           when 3 then -2
           when 9 then bountyamount
           end
       ) reputation_change

from votes v
inner join posts p on p.id = v.postid
where p.owneruserid = ##userid?1719510##
and posttypeid in (1,2)
and votetypeid in (1,2,3,9)
group by v.creationdate, p.id, votetypeid, posttypeid
),
bounties as 
(
select v.creationdate
     , v.postid postid 
     , 'bounty-offered' vote_type
     , 'question' post_type
     , sum(bountyamount) reputation_change
from votes v
where v.userid = ##userid?1719510##
and votetypeid = 8
group by v.creationdate, v.postid, votetypeid
),
approvededits as
(
select se.creationdate
     , se.postid postid 
     , 'approved-edit' vote_type
     , case p.posttypeid 
       when 1 then 'question'
       when 2 then 'answer'
       end  post_type
     , sum(2) reputation_change
from suggestededits se
inner join posts p on p.id = se.postid
where se.Owneruserid = ##userid?1719510##
and approvaldate is not null
group by se.creationdate, se.postid, posttypeid
)
select *
from postvotes
union
select *
from bounties
union 
select *
from approvededits

推荐阅读