统计每月一号总数。请高手出招啊

Question

select * from total_reg where day(total_date)=1 order by total_date
---------楼主的数据好象不对...

Answer 1

create table total_reg(  id  int ,  total_num  int,  total_date datetime) insert into total_reg values(  1,     30000,       '2006-1-1')
insert into total_reg values(  2,     35000,       '2006-1-2')
insert into total_reg values(  3 ,    38000,       '2006-1-3')
insert into total_reg values(  8 ,    58000,       '2006-2-1')
insert into total_reg values(  9 ,    68000,       '2006-3-1')
insert into total_reg values(  10 ,   68000,       '2006-3-2')
insert into total_reg values(  11 ,   78000,       '2006-4-1')select total_date ,sum(total_num)
from  total_reg
where datepart(dd,total_date)=1
group by total_date total_date                                                         
------------------------------------------------------ ----------- 
2006-01-01 00:00:00.000                                30000
2006-02-01 00:00:00.000                                58000
2006-03-01 00:00:00.000                                68000
2006-04-01 00:00:00.000                                78000（所影响的行数为 4 行）

Answer 2

select *
from  total_reg
where datepart(dd,total_date)=1
结果：
id          total_num   total_date                                             
----------- ----------- ------------------------------------------------------ 
1           30000       2006-01-01 00:00:00.000
8           58000       2006-02-01 00:00:00.000
9           68000       2006-03-01 00:00:00.000
11          78000       2006-04-01 00:00:00.000（所影响的行数为 4 行）

Answer 3

create table total_reg(  id  int ,  total_num  int,  total_date datetime) insert into total_reg values(  1,     30000,       '2006-1-1')
insert into total_reg values(  2,     35000,       '2006-1-31')
insert into total_reg values(  3 ,    38000,       '2006-1-30')
insert into total_reg values(  8 ,    58000,       '2006-2-28')
insert into total_reg values(  9 ,    68000,       '2006-3-1')
insert into total_reg values(  10 ,   68000,       '2006-3-2')
insert into total_reg values(  11 ,   78000,       '2006-4-30')
select *
from  total_reg
where datepart(dd,dateadd(dd,1,total_date))=1id          total_num   total_date                                             
----------- ----------- ------------------------------------------------------ 
2           35000       2006-01-31 00:00:00.000
8           58000       2006-02-28 00:00:00.000
11          78000       2006-04-30 00:00:00.000（所影响的行数为 3 行）

Answer 4

最后一天：
create table #daylist (
dl datetime)

select dateadd(day, -1, total_date) into #daylist 
from total_reg
where day(total_date) = 1
order by total_date ;

select a.* 
from total_reg a, #daylist b
where a.total_date = b.dl;

truncate table #daylist
drop table #daylist
         GO