SQL中Count和Distinct的使⽤
先来复习⼀下左连接：```
lect * from tb_driverReportDetails
left join tb_driverReportInput on tb_driverReportInput.id=portid
left join tb_stationMileage on tb_stationMileage.id=mileageStationId
它的⾏数和lect * from tb_driverReportDetails 是⼀样的列数更多。
上述表中，tb_driverReportInput的⼀条对应tb_driverReportDetails的多条。
香菇青菜粥
客户要求统计出某个时间段内乘务员报单数，就是tb_driverReportInput.id的个数。
⾸先要gorup by driverGh.
如果直接Count (tb_driverReportInput.id) 查出来的⽐较多，这是不对的，改为count( distinct tb_driverReportInput.id)就可以了。
完整的语句如下：
` lect
(lect gh from tb_driver where gh=driverGh) as gh,
(lect drivername from tb_driver where gh=driverGh) as name,
(lect department from tb_driver where gh=driverGh) as department,
(lect post from tb_driver where gh=driverGh) as post,
'司机' as workpost,
CAST(sum(actualOil) as decimal(18,2) ) as actualOil,
CAST(sum(prehensiveOil) as decimal(18,2) ) as comprehensiveOil,
CAST(sum(actualOil)-sum(prehensiveOil) as decimal(18,2) ) as saveOil,
0.5* sum(dzCount+dxCount+dcCount)+sum(score) as driverValue,烤鸭架汤
count( distinct tb_driverReportInput.id)
金羚电器有限公司
from tb_driverReportDetails left join tb_driverReportInput on tb_driverReportInput.id=portid
left join tb_stationMileage on tb_stationMileage.id=mileageStationId
where department='⽥集' and fromDateTime>@startdate and fromDateTime<@enddate group by driverGh`
另外有个不重复的约束
⾸先，创建⼀张表table_a
CREATE TABLE [dbo].[table_a](
大豆是什么
[aID] [int] NULL,
[aNum] [int] NULL
) ON [PRIMARY];
这个是没有unique约束的，若我们想给aID字段增加unique约束，则可有下列语句：忻口会战
ALTER TABLE table_a ADD unique(aID);
-----------------修改完善，另⼀种写法-----------------
上⾯的太复杂，效率也不⾼，另外⼀种写法，是包⼀层，逻辑更清晰。
lect
(lect gh from tb_driver where gh=assistantDriverGh) as gh, (lect drivername from tb_driver where
gh=assistantDriverGh) as name,
(lect department from tb_driver where gh=assistantDriverGh) as department,
(lect post from tb_driver where gh=assistantDriverGh) as post,
‘副司机’ as workpost,
(lect sum(driverOil) from tb_driverReportInput nb where nb.assistantDriverGh=b.assistantDriverGh)as SumDriverOil,
(lect sum(driverOil)- SumComprehensiveOil from tb_driverReportInput nb where
nb.assistantDriverGh=b.assistantDriverGh)as saveOil,
* from
(许爱
lect assistantDriverGh, 0.5* sum(dzCount+dxCount+dcCount)+sum(score) as driverValue,悠闲拼音
count( distinct tb_driverReportInput.id) as driverReportCount,
沐浴露可以洗头吗sum(prehensiveOil)as SumComprehensiveOil
from tb_driverReportDetails left join tb_driverReportInput on tb_driverReportInput.id=portid
left join tb_stationMileage on tb_stationMileage.id=mileageStationId
where (lect department from tb_driver where tb_driver.gh=assistantDriverGh and post=‘副司机’)=‘谢桥’ group by assistantDriverGh
) b
分页查询语句：查询31-40条的数据
lect top 10 * from [Table] where Id not in (
lect top 30 id from [Table]
)
