今天在CSDN上回答的别人一个问题,就是关于按年季度求和查询数据
我给出了2种数据库的解决方案:
MSSQL的:
- 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的
- 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用的一例子:
- 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
一个例子,其中的显示思想不错的,可以琢磨给以引申