一个按年季度查询数据的SQL语句
热29已有 1527 次阅读 2009-06-03 15:52
今天在上回答的别人一个问题，就是关于按年季度求和查询数据
我给出了2种数据库的解决方案：
MSSQL的：
Code:
Select Season = Case when month(日期) in (1,2,3) then '一季度'
when month(日期) in (4,5,6) then '二季度'
when month(日期) in (7,8,9) then '三季度'
when month(日期) in (10,11,12) then '四季度' end ,
count(*)
from tablename
group by Case when month(日期) in (1,2,3) then '一季度'
when month(日期) in (4,5,6) then '二季度'
when month(日期) in (7,8,9) then '三季度'
when month(日期) in (10,11,12) then '四季度' end
access的
Code:
SELECT IIf(month(addtime) in (1,2,3),'一季度',IIf(month(addtime) in (4,5,6),'二季度',IIf(month(addtime) in (7,8,9),'三季度','四季度'))) AS Season, sum(num), year(addtime)
FROM riq
GROUP BY year(addtime), IIf(month(addtime) in (1,2,3),'一季度',IIf(month(addtime) in (4,5,6),'二季度',IIf(month(addtime) in (7,8,9),'三季度','四季度')));
并给了我测试access用的一例子：
Code:
Dim Conn
Dim Db
Dim ConnStr
Db = "db1.mdb"
ConnStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(Db)
On Error Resume Next
Set fGetCnnStr = Server.CreateObject("ADODB.Connection")
fGetCnnStr.open ConnStr
If Err Then
err.Clear
Set fGetCnnStr = Nothing
Response.Write " Error......"
Response.End()
End If
strSQL ="SELECT IIf(month(addtime) in (1,2,3),'一季度',IIf(month(addtime) in (4,5,6),'二季度',IIf(month(addtime) in (7,8,9),'三季度','四季度'))) AS Season, sum(num) as znum, year(addtime) as pyear FROM riq GROUP BY year(addtime), IIf(month(addtime) in (1,2,3),'一季度',IIf(month(addtime) in (4,5,6),'二季度',IIf(month(addtime) in (7,8,9),'三季度','四季度'))) ORDER BY year(addtime) DESC , IIf(month(addtime) in (1,2,3),'一季度',IIf(month(addtime) in (4,5,6),'二季度',IIf(month(addtime) in (7,8,9),'三季度','四季度')))"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL,fGetCnnStr,1,1
if not rs.bof then
olddate=0
do while not rs.eof
if rs("pyear")<>olddate then response.Write rs("pyear")"年的统计结果""<br>"
response.Write rs("Season")":"&rs("znum")":""<br>"
olddate=rs("pyear")
rs.movenext
loop
end if
该数据库中就只有二个字段：即addtime、znum
