首页 新闻 赞助 找找看

sql 聚合函数或 GROUP BY 子句中。错误

0
[已解决问题] 解决于 2016-09-27 08:43

select * from
            (select k.*,usersso.TRUENAME from
             (
   -- select r.*,ii.REMARK from     (
            select bb.COUNTNAME,ssuser.TRUENAME as SUBMITUSER3  from
            (
            select convert(char(4),year(reportdate))+'-'+right('00'+convert(varchar(2),month(reportdate)),2) as 'REPORTDATE',


            (select count(*)  from SS_REPORT_MANAGEINFO srd
            where srd.isdeleted=1 and srd.REPORTDATE>=convert(datetime, convert(char(4),year(a.reportdate))+'-'+right('00'+convert(varchar(2),month(a.reportdate)),2)+'-01 00:00:00')
             and srd.REPORTDATE< dateadd(month,1, convert(datetime, convert(char(4),year(a.reportdate))+'-'+right('00'+convert(varchar(2),month(a.reportdate)),2)+'-01 00:00:00'))                
              )as COUNTNAME ,


            case
            (select top 1 srd.SCORINGSTATUS from SS_REPORT_MANAGEINFO srd
            where srd.isdeleted=1 and srd.REPORTDATE>=convert(datetime, convert(char(4),year(a.reportdate))+'-'+right('00'+convert(varchar(2),month(a.reportdate)),2)+'-01 00:00:00')
             and srd.REPORTDATE< dateadd(month,1, convert(datetime, convert(char(4),year(a.reportdate))+'-'+right('00'+convert(varchar(2),month(a.reportdate)),2)+'-01 00:00:00'))               
            ) when 0 then '待提交' when 10 then '待评分'  when 20 then '评分中' else '已归档' end   as 'SCORINGSTATUS',


             (select top 1 srd.CREATEUSER from SS_REPORT_MANAGEINFO srd
            where srd.isdeleted=1 and srd.REPORTDATE>=convert(datetime, convert(char(4),year(a.reportdate))+'-'+right('00'+convert(varchar(2),month(a.reportdate)),2)+'-01 00:00:00')
              and srd.REPORTDATE< dateadd(month,1, convert(datetime, convert(char(4),year(a.reportdate))+'-'+right('00'+convert(varchar(2),month(a.reportdate)),2)+'-01 00:00:00'))             
             order by srd.CREATEUSER desc

            ) as CREATEUSER,


             (select top 1 srd1.SUBMITUSER from SS_REPORT_MANAGEINFO srd1
            where srd1.isdeleted=1 and srd1.REPORTDATE>=convert(datetime, convert(char(4),year(a.reportdate))+'-'+right('00'+convert(varchar(2),month(a.reportdate)),2)+'-01 00:00:00')
             and srd1.REPORTDATE< dateadd(month,1, convert(datetime, convert(char(4),year(a.reportdate))+'-'+right('00'+convert(varchar(2),month(a.reportdate)),2)+'-01 00:00:00'))              
             order by srd1.SUBMITUSER desc
            ) as SUBMITUSER2,
   
             (select top 1 yp.REMARK from SS_REPORT_RETURN yp
           where  yp.REPORTID=a.ID and yp.REMARK!=null
             order by yp.CREATEDATE desc
            ) as REMARK,


             (select top 1 srd1.LASTMODIFYDATE from SS_REPORT_MANAGEINFO srd1
            where srd1.isdeleted=1 and srd1.REPORTDATE>=convert(datetime, convert(char(4),year(a.reportdate))+'-'+right('00'+convert(varchar(2),month(a.reportdate)),2)+'-01 00:00:00')
             and srd1.REPORTDATE< dateadd(month,1, convert(datetime, convert(char(4),year(a.reportdate))+'-'+right('00'+convert(varchar(2),month(a.reportdate)),2)+'-01 00:00:00'))               
             order by LASTMODIFYDATE desc
            ) as LASTMODIFYDATE,


             (select top 1 srd1.SUBMITUSERDATE from SS_REPORT_MANAGEINFO srd1
            where srd1.isdeleted=1 and srd1.REPORTDATE>=convert(datetime, convert(char(4),year(a.reportdate))+'-'+right('00'+convert(varchar(2),month(a.reportdate)),2)+'-01 00:00:00')
             and srd1.REPORTDATE< dateadd(month,1, convert(datetime, convert(char(4),year(a.reportdate))+'-'+right('00'+convert(varchar(2),month(a.reportdate)),2)+'-01 00:00:00'))              
             order by srd1.SUBMITUSERDATE desc
            ) as SUBMITUSERDATE,


             (select top 1 srd1.PROJECTNAME from SS_REPORT_MANAGEINFO srd1
            where srd1.isdeleted=1 and srd1.REPORTDATE>=convert(datetime, convert(char(4),year(a.reportdate))+'-'+right('00'+convert(varchar(2),month(a.reportdate)),2)+'-01 00:00:00')
             and srd1.REPORTDATE< dateadd(month,1, convert(datetime, convert(char(4),year(a.reportdate))+'-'+right('00'+convert(varchar(2),month(a.reportdate)),2)+'-01 00:00:00'))              
             order by srd1.PROJECTNAME desc
            ) as PROJECTNAME,


             (select top 1 srd1.REPORTDATE from SS_REPORT_MANAGEINFO srd1
            where srd1.isdeleted=1 and srd1.REPORTDATE>=convert(datetime, convert(char(4),year(a.reportdate))+'-'+right('00'+convert(varchar(2),month(a.reportdate)),2)+'-01 00:00:00')
             and srd1.REPORTDATE< dateadd(month,1, convert(datetime, convert(char(4),year(a.reportdate))+'-'+right('00'+convert(varchar(2),month(a.reportdate)),2)+'-01 00:00:00'))               
            order by srd1.REPORTDATE desc
            ) as QueryDATE,


             convert(datetime, convert(char(4),year(reportdate))+'-'+right('00'+convert(varchar(2),month(reportdate)),2)+'-01 00:00:00') as reportdate1 
             from SS_REPORT_MANAGEINFO  a where isdeleted=1
           group by convert(char(4),year(reportdate))+'-'+right('00'+convert(varchar(2),month(reportdate)),2)--,a.ID--,a.CREATEDATE--,a.ISDELETED
            ) bb left join SSO.DBO.SSO_USER ssuser ON  bb.SUBMITUSER2 = ssuser.userid


   --) r left join SS_REPORT_RETURN ii  on r.ID=ii.REPORTID


   )k inner join SSO.DBO.SSO_USER usersso on usersso.USERID=k.CREATEUSER
   --group by k.REPORTDATE
            )as a where 1=1 
             order by reportdate1 desc


   
  错误提示

消息 8120,级别 16,状态 1,第 34 行
选择列表中的列 'SS_REPORT_MANAGEINFO.ID' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。

 

三张表,时间分组,统计个数,用于主列表展示信息

 

 
 
 
要实现分月统计显示
 
有没有其他方法解决或是替代。
 
 
问题补充:

 

界面图生成图片,最后要实现月统计,备注字段为一张表的,提交的名称为一张表字段。其他为主表内容

过河小斌的主页 过河小斌 | 初学一级 | 园豆:38
提问于:2016-09-26 18:42
< >
分享
最佳答案
0

这么长的代码,谁愿意读下去

奖励园豆:5
悦光阴 | 老鸟四级 |园豆:2251 | 2016-09-26 22:45
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册