注册 登录
编程论坛 ASP技术论坛

请高手帮助注释一段asp代码,有点难呀

hp3325 发布于 2009-09-17 11:33, 1018 次点击
Function getCumulativeSalesByCatXML(intYear,forDataURL)
    Dim oRsCat, oRs, strSQL
    Dim strXML   
    Dim strCat, catXMLDone
    catXMLDone = false
    Dim strDataXML
    strDataXML = ""
    strCat = "<categories>"
    strSQL = "Select categoryId,categoryName from FC_Categories GROUP BY categoryId,categoryName"   
    Set oRsCat = Server.CreateObject("ADODB.Recordset")
    oRsCat.Open strSQL, oConn
    Dim strLink
    While not oRsCat.EOF
        strDataXML = strDataXML & "<dataset seriesName='" & escapeXML(oRsCat("categoryName"),forDataURL) & "'>"
        strSQL = "SELECT  Month(o.orderdate) as MonthNum, g.CategoryID, g.CategoryName, round(sum(d.quantity),0) as quantity, SUM(d.quantity*p.Unitprice) As Total FROM FC_categories as g,  FC_products as p, FC_orders as o, FC_OrderDetails as d  WHERE year(o.OrderDate)=" & intYear &" and g.categoryId=" & oRsCat("CategoryId") & " and d.productid= p.productid and g.categoryid= p.categoryid and o.orderid= d.orderid GROUP BY g.CategoryID,g.categoryname,Month(o.orderdate)"
        Set oRs = Server.CreateObject("ADODB.Recordset")
        oRs.Open strSQL, oConn
        While not oRs.EOF
            if catXMLDone=false then                        
                strCat = strCat & "<category label='" & MonthName(oRs("MonthNum"),true) & "' />"               
            end if   
            strLink = Server.URLEncode("javaScript:updateProductChart(" & intYear & "," & oRs("MonthNum") & "," & ors("CategoryId") & ");")
            strDataXML = strDataXML & "<set value='" & oRs("Total") & "' link='" & strLink & "'/>"
            oRs.MoveNext
        Wend   
        catXMLDone = true
        Set oRs = nothing
        strDataXML = strDataXML & "</dataset>"
        oRsCat.MoveNext()        
    Wend
    strCat = strCat & "</categories>"
    Set oRsCat = nothing
    strXML = strCat & strDataXML
    getCumulativeSalesByCatXML = strXML
End Function


特别是这个:
strSQL = "SELECT  Month(o.orderdate) as MonthNum, g.CategoryID, g.CategoryName, round(sum(d.quantity),0) as quantity, SUM(d.quantity*p.Unitprice) As Total FROM FC_categories as g,  FC_products as p, FC_orders as o, FC_OrderDetails as d  WHERE year(o.OrderDate)=" & intYear &" and g.categoryId=" & oRsCat("CategoryId") & " and d.productid= p.productid and g.categoryid= p.categoryid and o.orderid= d.orderid GROUP BY g.CategoryID,g.categoryname,Month(o.orderdate)"
4 回复
#2
hp33252009-09-17 20:50
特别是这段:
        strSQL = "SELECT  Month(o.orderdate) as MonthNum, g.CategoryID, g.CategoryName, round(sum(d.quantity),0) as quantity, SUM(d.quantity*p.Unitprice) As Total FROM FC_categories as g,  FC_products as p, FC_orders as o, FC_OrderDetails as d  WHERE year(o.OrderDate)=" & intYear &" and g.categoryId=" & oRsCat("CategoryId") & " and d.productid= p.productid and g.categoryid= p.categoryid and o.orderid= d.orderid GROUP BY g.CategoryID,g.categoryname,Month(o.orderdate)"
#3
无根泉2009-09-17 21:12
FC_categories as g,  FC_products as p, FC_orders as o, FC_OrderDetails as d
四个表用别名表示,
Month(o.orderdate) as MonthNum 取  FC_orders表中orderdate字段的月份
round(sum(d.quantity),0)       求和表FC_OrderDetails中quantity字段并取整数
SUM(d.quantity*p.Unitprice) As Total 将表FC_OrderDetails的字段和FC_products表中的Unitprice字段相乘并求和
#4
无根泉2009-09-17 21:14
year(o.OrderDate)=" & intYear &" and g.categoryId=" & oRsCat("CategoryId") 设定查询条件
 
d.productid= p.productid and g.categoryid= p.categoryid and o.orderid= d.orderid四个表的依赖关系
#5
hp33252009-09-19 09:57
解决了,谢谢
1