access转为sql的代码怎么改。

[agoni_xu]

SELECT DISTINCTROW Format$([gztongji].[createDate],'mmmm yyyy') AS createDatebymonth, Sum(gztongji.jiben) AS Sumjiben, Sum(gztongji.jiangjin) AS Sumjiangjin, Sum(gztongji.koushui) AS Sumkoushui, Sum(gztongji.sanjin) AS Sumsanjin, Sum(gztongji.gongjijin) AS Sumgongjijin, Sum(gztongji.kouxin) AS Sumkouxin, Sum(gztongji.qita) AS Sumqita, Sum(gztongji.totalCount) AS SumtotalCount
FROM gztongji
GROUP BY Format$([gztongji].[createDate],'mmmm yyyy'), Year([gztongji].[createDate])*12+DatePart('m',[gztongji].[createDate])-1;
以上是access的代码，求改成sql使用的代码。
自己有改了些，可以使用，但是应用的时候又会出错。

[agoni_xu]

没人回答啊？？

[shoppo0505]

错误和测试数据贴上来看看

[Tiger_Zhao]

SELECT (CASE m WHEN 1 THEN '一月'
               WHEN 2 THEN '二月'
               ...
               WHEN 12 THEN '十二月'
        END) + ' ' +
       CONVERT(varchar(4),y) createdatebymonth,
       sumjiben,
       sumjiangjin,
       ...
       sumtotalcount
  FROM (
          SELECT YEAR([gztongji].[createdate]) y,
                 MONTH([gztongji].[createdate]) m,
                 SUM(gztongji.jiben) AS sumjiben,
                 SUM(gztongji.jiangjin) AS sumjiangjin,
                 SUM(gztongji.koushui) AS sumkoushui,
                 SUM(gztongji.sanjin) AS sumsanjin,
                 SUM(gztongji.gongjijin) AS sumgongjijin,
                 SUM(gztongji.kouxin) AS sumkouxin,
                 SUM(gztongji.qita) AS sumqita,
                 SUM(gztongji.totalcount) AS sumtotalcount
            FROM gztongji
        GROUP BY YEAR([gztongji].[createdate]),MONTH([gztongji].[createdate])
       ) t

[agoni_xu]

谢谢。。但是我要怎么插入到一张新表里？？

[Tiger_Zhao]

SELECT ...
  INTO 新表
  FROM ...

[agoni_xu]

楼上的你好厉害。。谢谢。 我们在做毕设。。 大学都白学了。诶诶~~~ 你扣扣多少啊？怎么联系，要小小的感谢你。

[agoni_xu]

SELECT DISTINCTROW gztongji.EmpID, Format$(gztongji.createDate,'yyyy') AS createDatebyyesr, Sum(gztongji.jiben) AS jibenSum, Sum(gztongji.jiangjin) AS jiangjinSum, Sum(gztongji.koushui) AS koushuiSum, Sum(gztongji.sanjin) AS sanjinSum, Sum(gztongji.gongjijin) AS gongjijinSum, Sum(gztongji.kouxin) AS kouxinSum, Sum(gztongji.qita) AS qitaSum, Sum(gztongji.totalCount) AS totalCountSum, EmpGeneralInfo.EmpName
FROM EmpGeneralInfo RIGHT JOIN gztongji ON EmpGeneralInfo.ID = gztongji.EmpID
GROUP BY gztongji.EmpID, Format$(gztongji.createDate,'yyyy'), EmpGeneralInfo.EmpName, Year(gztongji.createDate);

上面是access的，也想改成sql的

[Tiger_Zhao]

Format$(gztongji.createDate,'yyyy')
改为
CONVERT(varchar(4),gztongji.createDate,120)

你自己至少得把SQL Server帮助中得函数都看一遍，知道有那些功能。

[agoni_xu]

我 看了， 我本来是用asp+access做的， 然后要求得改成asp+sql的，就是要用sql才行。然后其他表都导成sql的， 然后这些查询表不能导，只能重新弄。都不知道、、 SELECT DISTINCTROW Holiday.EmpID, Format$([Holiday].[HDFrom],'mmmm yyyy') AS HDFromMonth, Sum(Holiday.HDNoPayCount) AS SumofHDNoPayCount FROM Holiday GROUP BY Holiday.EmpID, Format$([Holiday].[HDFrom],'mmmm yyyy'), Year([Holiday].[HDFrom])*12+DatePart('m',[Holiday].[HDFrom])-1 HAVING (((Sum(Holiday.HDNoPayCount))>0)); 这个能不能再帮我改成sql 的。 然后你看看给你的私信哈。。

[Tiger_Zhao]

#3已经改好了啊。

[agoni_xu]

有所区别， 我改了。但是出来结果少了empID. 你帮我看看哪里改错了。

SELECT (CASE m WHEN 1 THEN 'January'
               WHEN 2 THEN 'February'
               WHEN 3 THEN 'March'
               WHEN 4 THEN 'April'
               WHEN 5 THEN 'May'
               WHEN 6 THEN 'June'
               WHEN 7 THEN 'July'
               WHEN 8 THEN 'August'
               WHEN 9 THEN 'September'
               WHEN 10 THEN 'October'
               WHEN 11 THEN 'November'
               WHEN 12 THEN 'December'
        END) + ' ' +
       CONVERT(varchar(4),y) HDFromMonth,
       SumofHDNoPayCount
  FROM (
          SELECT Holiday.EmpID, YEAR([Holiday].[HDFrom]) y,
                 MONTH([Holiday].[HDFrom]) m,
                 Sum(Holiday.HDNoPayCount) AS SumofHDNoPayCount
      FROM Holiday
        GROUP BY Holiday.EmpID, YEAR([Holiday].[HDFrom]),MONTH([Holiday].[HDFrom])
        HAVING (((Sum(Holiday.HDNoPayCount))>0))
       )t

[agoni_xu]

是可以，但是我发现应用在asp上面会提示 datepart 指定的参数 1 无效。这个是数据库改了的问题还是？

[agoni_xu]

<%
Dim rs
Dim rs_numRows
sql = "SELECT * FROM gztongjimonth where "

if trim(request.QueryString("year")) <> "" then
stryear = request.QueryString("year")
sql = sql & "datepart('yyyy',createdatebymonth) = '" &stryear&"'"
else
stryear = datepart("yyyy",now())
sql = sql & "datepart('yyyy',createdatebymonth) = '" &stryear&"'"
end if

Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = MM_EmpMain_STRING
rs.Source = sql
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 1
rs.Open()

rs_numRows = 0
%>
asp引用到的。。datepart是不是也得改？