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;