举例1

有一张项目表

CREATE TABLE [ProjectTable] (
[ProjectID] NVARCHAR(16) NOT NULL,
[ProjectName] NVARCHAR(20) NOT NULL ON CONFLICT REPLACE DEFAULT Null,
[DeviceID] INTEGER(16) NOT NULL ON CONFLICT FAIL,
[Description] NVARCHAR(255) NOT NULL ON CONFLICT REPLACE DEFAULT 工程描述);

group by的SQL语句-LMLPHP

使用一下的sql语句

select

distinct

a.[ProjectID],

a.[ProjectName]

from

projecttable as a

得出的结果为

group by的SQL语句-LMLPHP

其实想要的数据，只是不同的ProjectID所对应的ProjectName

修改sql语句为

select

distinct

a.[ProjectID],

a.[ProjectName]

from

projecttable as a

group by a.[ProjectID]

group by的SQL语句-LMLPHP

举例2

https://stackoverflow.com/questions/13998552/why-do-we-need-group-by-with-aggregate-functions

假如现在有一张points detail表，3个字段，user id,company id,points

CREATE TABLE PointsDetail

    (

        UserId INT NOT NULL ,

        CompanyId INT NOT NULL ,

        Points INT NOT NULL

    );

INSERT INTO dbo.PointsDetail (   UserId ,

                                 CompanyId ,

                                 Points

                             )

VALUES (   1 , -- UserId - int

           1 , -- CompanyId - int

           6   -- Points - int

       );

INSERT INTO dbo.PointsDetail (   UserId ,

                                 CompanyId ,

                                 Points

                             )

VALUES (   2 , -- UserId - int

           1 , -- CompanyId - int

           7   -- Points - int

       );

INSERT INTO dbo.PointsDetail (   UserId ,

                                 CompanyId ,

                                 Points

                             )

VALUES (   1 , -- UserId - int

           2 , -- CompanyId - int

           4   -- Points - int

       );

INSERT INTO dbo.PointsDetail (   UserId ,

                                 CompanyId ,

                                 Points

                             )

VALUES (   2 , -- UserId - int

           2 , -- CompanyId - int

           3   -- Points - int

       );

获取所有数据

SELECT *

FROM   PointsDetail;

group by的SQL语句-LMLPHP

按照用户group汇总积分，得到每个用户的积分总和

SELECT   UserId ,

         SUM(Points) AS TotalPointsForUser

FROM     PointsDetail

GROUP BY UserId;

group by的SQL语句-LMLPHP

按照公司group汇总积分，得到每个公司的积分总和

SELECT   CompanyId ,

         SUM(Points) AS TotalPointsForCompany

FROM     PointsDetail

GROUP BY CompanyId;

group by的SQL语句-LMLPHP

举例3 特殊情况

BeneficiaryAccountID 由BeneficiaryId和CompanyId决定，所以下面的2个sql语句执行的points列是一样的

SELECT   BeneficiaryAccountID ,

         SUM(Points) AS TotalPoints

FROM     dbo.tbm_cti_CustomTableItem_BeneficiaryPointDetail

WHERE    PointsType = 1

GROUP BY BeneficiaryAccountID

ORDER BY TotalPoints;

SELECT   BeneficiaryID ,

         CompanyID ,

         SUM(Points) AS TotalPoints

FROM     dbo.tbm_cti_CustomTableItem_BeneficiaryPointDetail

WHERE    PointsType = 1

GROUP BY BeneficiaryID ,

         CompanyID

ORDER BY TotalPoints;