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]
    )  


展开阅读全文

没有更多推荐了,返回首页