SQL 中 Count 和Distinct的使用

最新推荐文章于 2025-06-16 17:41:40 发布

原创最新推荐文章于 2025-06-16 17:41:40 发布 · 6k 阅读

1 ·

CC 4.0 BY-SA版权

sql 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了如何使用SQL进行复杂的统计查询，特别是针对特定时间段内乘务员报单数量的统计，并展示了如何通过分页查询获取指定范围内的数据记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

先来复习一下左连接：```
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]
    )