如何按当前月的天数分组,一个月可能是28天、29天、30天、31天...

Question

select top 366 identity(int,0,1)as row,cast('20080101' as datetime) as  dt into #
from master..sysobjectsupdate # set dt=dt+rowSELECT a.dt as TheDate ,COUNT(*) as TheNum
FROM  # a LEFT JOIN TableOne b
     ON a.dt=b.TheDate
--WHERE a.dt Between '20080701' and '20080731'     

Answer 1

算法可以这样:先取得最小的日期的年,月 ,假设为A,然后再取得最大的年,月 假设为B,然后用B 加上一个月再与A计算相差天数,然后循环填充得每一天的日期
然后自动赋上0,这是1个临时结果集然后再根据现在的表数据按年,月分组统计以后,与上面的结果集UNION 一下,就OK了,方法就自己写了

Answer 2

declare @year int, @month int
select @year=2008, @month=7select a.date,isnull(count(1),0) as cnt
from (
select dateadd(day, colid-1, ltrim(@year*100+@month)+'01') as date
    from syscolumns
        where id=object_id('syscolumns') and colid<=day(dateadd(month,1,ltrim(@year*100+@month)+'01')-1)
      ) a 
left join tb b on b.dt = a.date
            

Answer 3

IF OBJECT_ID('tableOne','u') IS NOT NULL
DROP TABLE tableOne
GOCREATE TABLE TableOne  (TheID int , TheTime datetime) 
INSERT tableOne SELECT 1       ,               '2008-07-01 18:28:28.000' 
UNION ALL  SELECT 2      ,                '2008-07-01 19:28:28.000' 
UNION ALL  SELECT 3     ,                 '2008-07-01 20:28:28.000' 
UNION ALL  SELECT 4    ,                  '2008-07-02 10:28:28.000' 
UNION ALL  SELECT 5   ,                   '2008-07-02 20:28:28.000' 
UNION ALL  SELECT 6  ,                    '2008-07-11 20:28:28.000' 
UNION ALL  SELECT 7 ,                     '2008-07-11 22:28:28.000' 
UNION ALL  SELECT 8,                      '2008-07-21 20:28:28.000' 
UNION ALL  SELECT 9,                      '2008-06-21 20:28:28.000' 
UNION ALL  SELECT 10,                     '2008-02-21 20:28:28.000' 
GOSELECT TOP 31 IDENTITY(INT) ID INTO # FROM sysobjectsSELECT d,COUNT(TheTime)
FROM
(SELECT DISTINCT CONVERT(VARCHAR(8),TheTime,120) + RTRIM(ID) d FROM TableOne
CROSS JOIN #
WHERE ISDATE(CONVERT(VARCHAR(8),TheTime,120) + RTRIM(ID))=1
) a
LEFT JOIN tableONE b
ON DATEDIFF(dd,TheTime,d)=0
GROUP BY d
ORDER BY CAST(d AS DATETIME)
/*
2008-02-1 0
2008-02-2 0
2008-02-3 0
2008-02-4 0
2008-02-5 0
2008-02-6 0
2008-02-7 0
2008-02-8 0
2008-02-9 0
2008-02-10 0
2008-02-11 0
2008-02-12 0
2008-02-13 0
2008-02-14 0
2008-02-15 0
2008-02-16 0
2008-02-17 0
2008-02-18 0
2008-02-19 0
2008-02-20 0
2008-02-21 1
2008-02-22 0
2008-02-23 0
2008-02-24 0
2008-02-25 0
2008-02-26 0
2008-02-27 0
2008-02-28 0
2008-02-29 0
2008-06-1 0
2008-06-2 0
2008-06-3 0
2008-06-4 0
2008-06-5 0
2008-06-6 0
2008-06-7 0
2008-06-8 0
2008-06-9 0
2008-06-10 0
2008-06-11 0
2008-06-12 0
2008-06-13 0
2008-06-14 0
2008-06-15 0
2008-06-16 0
2008-06-17 0
2008-06-18 0
2008-06-19 0
2008-06-20 0
2008-06-21 1
2008-06-22 0
2008-06-23 0
2008-06-24 0
2008-06-25 0
2008-06-26 0
2008-06-27 0
2008-06-28 0
2008-06-29 0
2008-06-30 0
2008-07-1 3
2008-07-2 2
2008-07-3 0
2008-07-4 0
2008-07-5 0
2008-07-6 0
2008-07-7 0
2008-07-8 0
2008-07-9 0
2008-07-10 0
2008-07-11 2
2008-07-12 0
2008-07-13 0
2008-07-14 0
2008-07-15 0
2008-07-16 0
2008-07-17 0
2008-07-18 0
2008-07-19 0
2008-07-20 0
2008-07-21 1
2008-07-22 0
2008-07-23 0
2008-07-24 0
2008-07-25 0
2008-07-26 0
2008-07-27 0
2008-07-28 0
2008-07-29 0
2008-07-30 0
2008-07-31 0*/
GO
DROP TABLE #
GO

Answer 4

参考
http://topic.csdn.net/u/20080509/20/4c8772d1-3761-46d5-a8f5-126f9abc5273.html

Answer 5

有点问题啊...我改成SELECT TOP 31 IDENTITY(INT) ID INTO # FROM sysobjectsSELECT d,COUNT(TheTime)
    FROM
-------------------------------------------------
    (SELECT DISTINCT CONVERT(VARCHAR(8),TheTime,120) + RTRIM(TableOne.ID) d FROM TableOne
    CROSS JOIN #
-------------------------------------------------
        WHERE ISDATE(CONVERT(VARCHAR(8),TheTime,120) + RTRIM(TableOne.ID))=1
-------------------------------------------------  这里给ID加了前缀...但结果怎么查的是6 月份的？
    ) a
LEFT JOIN tableONE b
    ON DATEDIFF(dd,TheTime,d)=0
    GROUP BY d
    ORDER BY CAST(d AS DATETIME)

Answer 6

如果要查某年6月份的，在 GROUP BY D前面加上 WHERE DATEDIFF(mm,d,'2008-6-1')=0

Answer 7

大哥，me的意思是，怎么查出来的不是 7 月的，而是6月的数据？
WHERE ISDATE(CONVERT(VARCHAR(8),TheTime,120) + RTRIM(TableOne.ID))=1
这句话有问题吗？=1是表示当前月吗？

Answer 8

 RTRIM(ID) 中的ID 是 #中的ID吗？我TableOne表的主键也是ID...

Answer 9

改成这样试试：SELECT d,COUNT(TheTime)
    FROM
    (SELECT DISTINCT CONVERT(VARCHAR(8),TheTime,120) + RTRIM(ID) d FROM TableOne T
    CROSS JOIN #
        WHERE ISDATE(CONVERT(VARCHAR(8),TheTime,120) + RTRIM(T.ID))=1
    ) a
LEFT JOIN tableONE b
    ON DATEDIFF(dd,TheTime,d)=0
    GROUP BY d
    ORDER BY CAST(d AS DATETIME)

Answer 10

如果你的tableOne里也有ID，那么指定别名就可以了。我写的语句里的id确实是指代的#里的id

Answer 11

(SELECT DISTINCT CONVERT(VARCHAR(8),TheTime,120) + RTRIM(ID) d FROM TableOne T
这句中的 RTRIM(ID) 的ID 是 # 中的ID 吗？
TableOne表的 主键 被同事 键成了ID...

Answer 12

我不能写成 RTRIM(#.ID) 这样啊。#怎么起别名呢？不然ID就不明确了。

Answer 13

形如:select a.*,b.* from tb a
inner join # b
   on a.id=b.id

Answer 14

对，把 # 的ID 改下就可以了改成 theID了...但我在 GROUP BY d 前添加了个条件where advID=22 怎么就只出现 Count(TheTime) 不为 0 的项呢？

Answer 15

在GROUP BY d 前添加WHERE DATEDIFF(mm,d,getdate())=0 
---------------确实是查出当月的数据了...但我要是在加个 and advId=22 怎么就不出现为 0 的项呢？

Answer 16

晕，如果在加个 and advId=22  的话...那些 count(TheTime) 项的 advId 都是 null值，所以都不出现....晕，那咋搞？TheID                  TheTime                        advId
1                      2008-07-01 18:28:28.000         22
2                      2008-07-01 19:28:28.000         22
3                      2008-07-01 20:28:28.000         28
4                      2008-07-02 10:28:28.000         22
5                      2008-07-02 20:28:28.000         28
6                      2008-07-11 20:28:28.000         22
7                      2008-07-11 22:28:28.000         22
8                      2008-07-21 20:28:28.000         22
---------------------------------------------------------TheNum                  TheDate           advId2                      2008-07-01          22
1                      2008-07-02          22
0                      2008-07-03          22
0                      2008-07-04          22
0                      2008-07-05          22...... 
0                      2008-07-10          22
2                      2008-07-11          22
0                      2008-07-12          22......
弄成这个样子怎么搞？不然不能查了...