group by

举例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 工程描述);

使用一下的sql语句

select 
distinct 
a.[ProjectID],
a.[ProjectName] 
from 
projecttable as a

得出的结果为

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

 

 

修改sql语句为

select 
distinct 
a.[ProjectID],
a.[ProjectName] 
from 
projecttable as a
group by a.[ProjectID]

 

 

举例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汇总积分，得到每个用户的积分总和

SELECT   UserId ,
         SUM(Points) AS TotalPointsForUser
FROM     PointsDetail
GROUP BY UserId;

 

 

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

SELECT   CompanyId ,
         SUM(Points) AS TotalPointsForCompany
FROM     PointsDetail
GROUP BY CompanyId;

 

 

举例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;

 

作者:Chuck Lu    GitHub