sqlserver过滤求和

一张表里，根据日期和车牌号过滤，如果同一天相同车牌号只收取一次安检费
如列 发车日期 车牌号码 安检次数
2011-02-02 cs2323 1
2011-02-02 cs2323 1
2011-02-02 cs1212 1
2011-02-03 cs1212 1
sql求出实际收费安检次数3次

SQL code

create table tb(发车日期 datetime,车牌号码 varchar(10),安检次数 int)
insert into tb select '2011-02-02','cs2323',1
insert into tb select '2011-02-02','cs2323',1
insert into tb select '2011-02-02','cs1212',1
insert into tb select '2011-02-03','cs1212',1
go
select count(*) from(select distinct * from tb)t
go
drop table tb
/*
-----------
3

(1 行受影响)

*/

SQL code

Create table tb(发车日期 datetime,车牌号码 varchar(10),安检次数 int)
insert into tb select '2011-02-02','cs2323',1
insert into tb select '2011-02-02','cs2323',1
insert into tb select '2011-02-02','cs1212',1
insert into tb select '2011-02-03','cs1212',1


select 安检次数=COUNT(1) 
from 
(select 发车日期,车牌号码,安检次数=sum(安检次数)
from tb
group by 发车日期,车牌号码) t
where 安检次数>0

/*
安检次数
-----------
3

(1 行受影响)

*/

SQL code

declare @t table 
(发车日期 datetime,车牌号码 varchar(6),安检次数 int)
insert into @t
select '2011-02-02','cs2323',1 union all
select '2011-02-02','cs2323',1 union all
select '2011-02-02','cs1212',1 union all
select '2011-02-03','cs1212',1

select count(*) as [count] from (select count(*) as c from @t
group by 发车日期,车牌号码) aa
/*
count
-----------
3
*/

SQL code

create table tb(发车日期 datetime,车牌号码 varchar(10),安检次数 int)
insert into tb select '2011-02-02','cs2323',1
insert into tb select '2011-02-02','cs2323',1
insert into tb select '2011-02-02','cs1212',1
insert into tb select '2011-02-03','cs1212',1
go
select count(*) 
from (select distinct convert(varchar(10),发车日期,120) as dt,车牌号码 from tb)t
go
drop table tb

/*********

-----------
3

declare @t table 
(发车日期 datetime,车牌号码 varchar(6),安检次数 int)
insert into @t
select '2011-02-02','cs2323',1 union all
select '2011-02-02','cs2323',1 union all
select '2011-02-02','cs1212',1 union all
select '2011-02-03','cs1212',1

select count(*) as [count] from (select count(*) as c from @t
group by 发车日期,车牌号码) aa

Mark！