SQL中Count和Distinct的使⽤
先来复习⼀下左连接：```
select * from tb_driverReportDetails
left join tb_driverReportInput on tb_driverReportInput.id=portid
left join tb_stationMileage on tb_stationMileage.id=mileageStationId
它的⾏数和select * from  tb_driverReportDetails  是⼀样的列数更多。
上述表中，tb_driverReportInput的⼀条对应tb_driverReportDetails的多条。
客户要求统计出某个时间段内乘务员报单数，就是tb_driverReportInput.id的个数。
⾸先要gorup by driverGh.
如果直接Count (tb_driverReportInput.id) 查出来的⽐较多，这是不对的，改为count( distinct tb_driverReportInput.id)就可以了。
完整的语句如下：
` selectdistinct查询
(select gh from tb_driver where gh=driverGh) as  gh,
(select drivername from tb_driver where gh=driverGh) as  name,
(select department from tb_driver where gh=driverGh) as  department,
(select post from tb_driver where gh=driverGh) as  post,
'司机' as  workpost,
CAST(sum(actualOil) as decimal(18,2) ) as actualOil,
CAST(sum(tb_driverReportDetailsprehensiveOil) as decimal(18,2) ) as comprehensiveOil,
CAST(sum(actualOil)-sum(tb_driverReportDetailsprehensiveOil) 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);
-----------------修改完善，另⼀种写法-----------------
上⾯的太复杂，效率也不⾼，另外⼀种写法，是包⼀层，逻辑更清晰。
select
(select gh from tb_driver where gh=assistantDriverGh) as gh, (select drivername from tb_driver where
gh=assistantDriverGh) as name,
(select department from tb_driver where gh=assistantDriverGh) as department,
(select post from tb_driver where gh=assistantDriverGh) as post,
‘副司机’ as workpost,
(select sum(driverOil) from tb_driverReportInput nb where nb.assistantDriverGh=b.assistantDriverGh)as SumDriverOil,
(select sum(driverOil)- SumComprehensiveOil from tb_driverReportInput nb where
nb.assistantDriverGh=b.assistantDriverGh)as saveOil,
* from
(
select assistantDriverGh, 0.5* sum(dzCount+dxCount+dcCount)+sum(score) as driverValue,
count( distinct tb_driverReportInput.id) as  driverReportCount,
sum(tb_driverReportDetailsprehensiveOil)as SumComprehensiveOil
from  tb_driverReportDetails left join tb_driverReportInput on tb_driverReportInput.id=portid
left join tb_stationMileage on tb_stationMileage.id=mileageStationId
where (select department from tb_driver where tb_driver.gh=assistantDriverGh and post=‘副司机’)=‘谢桥’ group by assistantDriverGh
) b
分页查询语句：查询31-40条的数据
select  top 10  *  from [Table] where Id not in (
select  top 30  id from [Table]
)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。