SQLServer2008根据年月时间归总数据

1. http://www.itnose.net/detail/6009311.html
   
2. select?years(orderdate),??
3. ??month(OrderDate),count(1)???
4. from???
5. ?tb??
6. group?by?years(orderdate),248); line-height:19px"> ??month(OrderDate) ?

1. --?Author??:fredrickhu(小F，向高手学习)??
2. --?Date????:2014-03-17?15:48:38??
3. --?Verstion:??
4. --??????Microsoft?SQL?Server?2008?(RTM)?-?10.0.1600.22?(Intel?X86)???
5. --??Jul??9?2008?14:43:34???
6. --??Copyright?(c)?1988-2008?Microsoft?Corporation??
7. --??Enterprise?Edition?on?Windows?NT?6.1?<X86>?(Build?7601:?Service?Pack?1)??
8. --??
9. ----------------------------------------------------------------??
10. -->?测试数据：[tb]??
11. if?object_id('[tb]')?is?not?null?drop?table?[tb]??
12. go???
13. create?table?[tb]([O_Id]?int,[OrderDate]?datetime,[OrderPrice]?6))??
14. insert?[tb]??
15. select?1,'2008/12/29',1000,'Bush'?union?all??
16. select?2,'2008/11/23',0)">1600,'Carter'?union?all??
17. 3,'2008/11/05',0)">700,0)">4,'2008/09/28',0)">300,'Bush'?union?all??
18. 5,'2008/08/06',0)">2000,'Adams'?union?all??
19. 6,'2008/08/21',0)">100,'Carter'??
20. --------------开始查询--------------------------??
21. declare?@startdate?datetime,@enddate?datetime??
22. set?@startdate='2008-01-01'??
23. set?@enddate='2009-12-31'??
24. SELECT?*?INTO?#tb??
25. FROM???
26. (??
27. select???
28. ????DISTINCT?YEAR(convert(varchar(10),dateadd(day,number,100)">@startdate),0)">120))?AS?[year],MONTH(convert(varchar(120))?AS?[month]??
29. from??
30. ????master..spt_values???
31. where???
32. ????datediff(day,?@enddate)>=0??
33. ????and?number>=0???
34. ????and?type='p'??
35. )t??
36. --SELECT?*?FROM?#tb?AS?a?LEFT?JOIN?tb?AS?b?ON?a.[year]=DATEPART(YEAR,b.OrderDate)?AND?a.[month]=DATEPART(month,b.OrderDate)??
37. ??
38. SELECT?a.*,ISNULL(COUNT(b.O_Id),0)">0)?AS?客户数量?FROM?#tb?AS?a?LEFT?JOIN?tb?AS?b?ON?a.[year]=DATEPART(YEAR,b.OrderDate)?GROUP?BY?a.year,a.month?ORDER?BY?a.year??
39. DROP?TABLE?#tb??
40. ----------------结果----------------------------??
41. /*?year????????month???????客户数量?
42. -----------?-----------?-----------?
43. 2008????????1???????????0?
44. 2008????????2???????????0?
45. 2008????????3???????????0?
46. 2008????????4???????????0?
47. 2008????????5???????????0?
48. 2008????????6???????????0?
49. 2008????????7???????????0?
50. 2008????????8???????????2?
51. 2008????????9???????????1?
52. 2008????????10??????????0?
53. 2008????????11??????????2?
54. 2008????????12??????????1?
55. 2009????????1???????????0?
56. 2009????????2???????????0?
57. 2009????????3???????????0?
58. 2009????????4???????????0?
59. 2009????????5???????????0?
60. 2009????????6???????????0?
61. 2009????????7???????????0?
62. 2009????????8???????????0?
63. 2009????????9???????????0?
64. 2009????????10??????????0?
65. 2009????????11??????????0?
66. 2009????????12??????????0?
67. 警告:?聚合或其他?SET?操作消除了?Null?值。?
68. ?
69. (24?行受影响)?
70. */??

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!