SQL是在选择列表中无效

问题描述：

我有，因为它并不在聚合函数或包含此SQL查询SQL是在选择列表中无效

列“StudentSIP.StartDate”下面的错误是在选择列表中无效GROUP BY 子句。

select concat(sc.StartDate, sc.EndDate) SIPDate, 
     count (sj.LOComment) WeekReviewed, 
     count(sjd.WeekNo) TotalWeek, 
     sum(sjj.TotalDaysRecord) TotalDaysRecord, 
     count(case sjd.JournalStatusCode when 'D' then 1 else null end) PendingComplete 
from StudentJournalDate sjd 
left join StudentJournal sj 
    on sjd.WeekNo = sj.WeekNo 
left outer join 
(
    select sj.WeekNo, 
      case when RTRIM(sj.Day1Journal) = '' or sj.Day1Journal is null then 0 else 1 end + 
      case when RTRIM(sj.Day2Journal) = '' or sj.Day2Journal is null then 0 else 1 end + 
      case when RTRIM(sj.Day3Journal) = '' or sj.Day3Journal is null then 0 else 1 end + 
      case when RTRIM(sj.Day4Journal) = '' or sj.Day4Journal is null then 0 else 1 end + 
      case when RTRIM(sj.Day5Journal) = '' or sj.Day5Journal is null then 0 else 1 end + 
      case when RTRIM(sj.Day6Journal) = '' or sj.Day6Journal is null then 0 else 1 end + 
      case when RTRIM(sj.Day7Journal) = '' or sj.Day7Journal is null then 0 else 1 end as TotalDaysRecord 
    from StudentJournal sj 
) as sjj 
    on sjj.WeekNo = sj.WeekNo 
left outer join StudentSIP sc 
    on sc.AdminNo = sjd.AdminNo

这是产生错误的实际查询？该错误消息似乎是说某个分组已完成，但我没有看到它。事实上，我并没有看到你在上面发布的查询有任何明显的问题。 –

是的，这是实际的查询。 – user3807187

正如您使用聚合函数使用分组为sc.StartDate，sc.EndDate –

答

你从来没有告诉我们您正在使用哪个版本的SQL，但我怀疑的是，集合函数在SELECT（例如COUNT和SUM）的存在是将数据库置于一个分组方式。在此模式下，您只能使用SELECT中列的聚合函数或GROUP BY子句中出现的列。由于sc.StartDate既不是一个聚合，也没有GROUP BY子句，因此会出现此错误。

一个快速的修复，使这个错误消失将是GROUP BY级联的开始和结束日期，即添加GROUP BY CONCAT(sc.StartDate, sc.ENDDate)到您当前查询的结尾：

SELECT CONCAT(sc.StartDate, sc.ENDDate) SIPDate, 
     COUNT (sj.LOComment) WeekReviewed, 
     COUNT(sjd.WeekNo) TotalWeek, 
     SUM(sjj.TotalDaysRecord) TotalDaysRecord, 
     COUNT(CASE sjd.JournalStatusCode WHEN 'D' THEN 1 ELSE NULL END) PENDingComplete 
FROM StudentJournalDate sjd 
LEFT JOIN StudentJournal sj 
    ON sjd.WeekNo = sj.WeekNo 
LEFT OUTER JOIN 
(
    SELECT sj.WeekNo, 
      CASE WHEN RTRIM(sj.Day1Journal) = '' OR sj.Day1Journal IS NULL THEN 0 ELSE 1 END + 
      CASE WHEN RTRIM(sj.Day2Journal) = '' OR sj.Day2Journal IS NULL THEN 0 ELSE 1 END + 
      CASE WHEN RTRIM(sj.Day3Journal) = '' OR sj.Day3Journal IS NULL THEN 0 ELSE 1 END + 
      CASE WHEN RTRIM(sj.Day4Journal) = '' OR sj.Day4Journal IS NULL THEN 0 ELSE 1 END + 
      CASE WHEN RTRIM(sj.Day5Journal) = '' OR sj.Day5Journal IS NULL THEN 0 ELSE 1 END + 
      CASE WHEN RTRIM(sj.Day6Journal) = '' OR sj.Day6Journal IS NULL THEN 0 ELSE 1 END + 
      CASE WHEN RTRIM(sj.Day7Journal) = '' OR sj.Day7Journal IS NULL THEN 0 ELSE 1 END AS TotalDaysRecord 
    FROM StudentJournal sj 
) AS sjj 
    ON sjj.WeekNo = sj.WeekNo 
LEFT OUTER JOIN StudentSIP sc 
    ON sc.AdminNo = sjd.AdminNo 
GROUP BY CONCAT(sc.StartDate, sc.ENDDate) -- make error go away so you are happy again :-)

我正在使用sql server 2014.它的工作原理！谢谢 – user3807187

嗨，我尝试使用此函数转换日期CONVERT（varchar，sc.StartDate，103）+'“+”“+”'+'“+” - “+”'+ CONVERT（varchar，sc.EndDate ，103）SIPDate将以dd/mm/yyyy - dd/mm/yyyy显示。我如何将它添加到查询中？谢谢 – user3807187

你可以通过改变选择和分组，或者你可以子查询它。 –

答

在SQL中使用时，不工作ggregate功能没有group by子句;

前select key1,key2,count(*),sum(*) from tableA group by key1,key2

SQL是在选择列表中无效

相关推荐