时间戳

首页 > 解决方案 > 内部加入两次后分组 - 按升序排序

sql-server - 内部加入两次后分组 - 按升序排序

问题描述

练习 SQL 并想对我的查询结果进行排序 - 原始查询运行良好，但是当按它分组时无法运行。

使用 SSMS '18 - 使用AdventureWorks数据库。

SELECT 
    pp.BusinessEntityID,
    pp.FirstName,
    pp.MiddleName,
    pp.LastName,
    ppw.PasswordHash,
    ppw.PasswordSalt,
    pph.PhoneNumber
FROM
    Person.Person AS pp
INNER JOIN
    Person.Password AS ppw ON pp.BusinessEntityID = ppw.BusinessEntityID
INNER JOIN
    Person.PersonPhone AS pph ON pp.BusinessEntityID = pph.BusinessEntityID
GROUP BY 
    pp.BusinessEntityID ASC, pp.FirstName, pp.MiddleName, pp.LastName, 
    ppw.PasswordHash, ppw.PasswordSalt, pph.PhoneNumber;

导致错误：

消息 156，级别 15，状态 1，第 21 行
关键字“ASC”附近的语法不正确。

如果删除 ASC 关键字并按BusinessEntityID降序分组，则代码将运行。我在这里错过了一些理论吗？

标签： sql-servertsqlgroup-by

解决方案

这将是 order by 而不是 group by

SELECT 
 pp.BusinessEntityID,
 pp.FirstName,
 pp.MiddleName,
 pp.LastName,
 ppw.PasswordHash,
 ppw.PasswordSalt,
 pph.PhoneNumber
FROM
Person.Person as pp
    inner join Person.Password as ppw on  pp.BusinessEntityID=ppw.BusinessEntityID
    inner join Person.PersonPhone as pph on pp.BusinessEntityID=pph.BusinessEntityID
order by  pp.BusinessEntityID ASC, pp.FirstName, pp.MiddleName, pp.LastName, ppw.PasswordHash, ppw.PasswordSalt, pph.PhoneNumber;

但如果你想使用 group by 那么它将是

SELECT 
     pp.BusinessEntityID,
     pp.FirstName,
     pp.MiddleName,
     pp.LastName,
     ppw.PasswordHash,
     ppw.PasswordSalt,
     pph.PhoneNumber
    FROM
    Person.Person as pp
        inner join Person.Password as ppw on  pp.BusinessEntityID=ppw.BusinessEntityID
        inner join Person.PersonPhone as pph on pp.BusinessEntityID=pph.BusinessEntityID
    group  by  pp.BusinessEntityID , pp.FirstName, pp.MiddleName, pp.LastName, ppw.PasswordHash, ppw.PasswordSalt, pph.PhoneNumber;

但是如果没有聚合，则 distinct 更好， group by 用于聚合

推荐阅读