sql - 如何在 SQL Server 中使用别名对多列进行分组？

Question

从一些旧代码中获取查询，我正在尝试使其工作。每次我提交查询时都会收到错误:

Error code 207, SQL state S0001: Invalid column name 'Grade'

代码:

SELECT 
    COUNT(*) AS PieceCount
    ,shifttimes.shiftid AS ShiftId
    ,specienames.NameText AS Specie
    ,gradenames.NameText AS Grade
    ,DryerNum
    ,CreatedLocal
FROM 
    sheets, shifttimes, Specienames, GradeNames
WHERE 
    sheets.ShiftIndex = shifttimes.ShiftIndex
    AND sheets.SpecieNameIndex = specienames.NameIndex
    AND sheets.gradenameindex = gradenames.NameIndex
    AND CreatedLocal >= '2015-04-01'
    AND CreatedLocal < '2015-06-01'
GROUP BY 
    ShiftId, Grade, DryerNum, Specie

Answer 1

对于我认为你应该更改的错误

GROUP BY ShiftId
    ,Grade
    ,DryerNum
    ,Specie

致

GROUP BY shifttimes.shiftid
    ,gradenames.NameText
    ,DryerNum
    ,specienames.NameText

注意:

在您的选择中，您有 ,CreatedLocal 但不在您的 group by 中，您必须将其从选择中删除或包含在 group by

建议:

尝试使用别名和隐式联接(from 子句中有两个表)是一种已弃用的语法，建议切换到现代的显式语法:

SELECT COUNT(*) AS PieceCount
    ,ST.shiftid AS ShiftId
    ,SN.NameText AS Specie
    ,GN.NameText AS Grade
    ,DryerNum    
FROM 
   sheets S
inner join shifttimes ST
   ON S.ShiftIndex = ST.ShiftIndex
inner join Specienames SN
   ON S.SpecieNameIndex = SN.NameIndex
inner join GradeNames GN
   ON S.gradenameindex = GN.NameIndex
WHERE 
    CreatedLocal >= '2015-04-01'
AND CreatedLocal < '2015-06-01'
GROUP BY 
     ST.shiftid
    ,GN.NameText
    ,DryerNum
    ,SN.NameText