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