SQL 中 Count 和Distinct的使用

先来复习一下左连接：```
select * from tb_driverReportDetails
left join tb_driverReportInput on tb_driverReportInput.id=tb_driverReportDetails.reportid
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)就可以了  。

完整的语句如下：


 ` select 
   (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_driverReportDetails.comprehensiveOil) as decimal(18,2) ) as comprehensiveOil,
    CAST(sum(actualOil)-sum(tb_driverReportDetails.comprehensiveOil) 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=tb_driverReportDetails.reportid
	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_driverReportDetails.comprehensiveOil)as SumComprehensiveOil  
from  tb_driverReportDetails left join tb_driverReportInput on tb_driverReportInput.id=tb_driverReportDetails.reportid
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]
    )