如何计算数据库记录集中当前这个月有几天,比如说有一数据库(DemoTable),有一字段(Date),记录如下: Date
2011-8-1
2011-8-1
2011-8-2
2011-8-4
2011-8-4
2011-8-4
2011-8-6
2011-8-8
2011-8-8
2011-8-11想得到结果为:6 (8.1、8.2、8.4、8.6、8.8、8.11共6天);应该如何计算,谢谢!
2011-8-1
2011-8-1
2011-8-2
2011-8-4
2011-8-4
2011-8-4
2011-8-6
2011-8-8
2011-8-8
2011-8-11想得到结果为:6 (8.1、8.2、8.4、8.6、8.8、8.11共6天);应该如何计算,谢谢!
insert into demotable select '2011-8-1'
insert into demotable select '2011-8-1'
insert into demotable select '2011-8-2'
insert into demotable select '2011-8-4'
insert into demotable select '2011-8-4'
insert into demotable select '2011-8-4'
insert into demotable select '2011-8-6'
insert into demotable select '2011-8-8'
insert into demotable select '2011-8-8'
insert into demotable select '2011-8-11'
go
select count(distinct date) from DemoTable where convert(varchar(7),date,120)='2011-08'
/*-----------
6(1 行受影响)*/
go
drop table demotable
create table demotable(Date datetime)
insert into demotable select '2011-8-1'
insert into demotable select '2011-8-1'
insert into demotable select '2011-8-2'
insert into demotable select '2011-8-4'
insert into demotable select '2011-8-4'
insert into demotable select '2011-8-4'
insert into demotable select '2011-8-6'
insert into demotable select '2011-8-8'
insert into demotable select '2011-8-8'
insert into demotable select '2011-8-11'
go
select count(distinct date) from DemoTable where year(date)=2011 and month(date)=8
/*-----------
6(1 行受影响)*/
go
drop table demotable
--不要重复发帖,字符型的date话
select left(date,6) as 月份,count(*) as 数目
from DemoTable group by left(date,6)
count(distinct convert(varchar(10),date,120)) as cnt
from tb
group by convert(varchar(7),date,112)
where convert(varchar(7),date,120)=convert(varchar(7),getDate(),120)
convert(varchar(7),date,112) as date,
count(distinct convert(varchar(10),date,120)) as cnt
from
tb
group by
convert(varchar(7),date,112)
insert into @demotable values('2011-8-1'),
('2011-8-1'),
('2011-8-2'),
('2011-8-4'),
('2011-8-4'),
('2011-8-4'),
('2011-8-6'),
('2011-8-8'),
('2011-8-8'),
('2011-8-11')
select cast(LEN(col1)/11 as varchar) + '('+left(col1,len(col1)-1)+')'
from (select distinct CONVERT(varchar,col1,111)+',' from @demotable where DATEDIFF(MONTH,col1,'2011-8-1')=0 for xml path('')) tb (col1)
group by date
declare @tb table([date] date)
insert into @tb
select '2011-8-1' union all
select '2011-8-1' union all
select '2011-8-2' union all
select '2011-8-4' union all
select '2011-8-4' union all
select '2011-8-4' union all
select '2011-8-6' union all
select '2011-8-8' union all
select '2011-8-8' union all
select '2011-8-11'
select COUNT(distinct [date]) from @tb
-------------------------
6