access转为sql的代码怎么改。

agoni_xu 2015-10-28 02:44:56

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使用的代码。
自己有改了些，可以使用，但是应用的时候又会出错。

...全文

65 点赞收藏 13

写回复

13 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

agoni_xu 2015-10-29

<%
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是不是也得改？

回复赞

agoni_xu 2015-10-29

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

回复赞

agoni_xu 2015-10-29

有所区别，我改了。但是出来结果少了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

回复赞

Tiger_Zhao 2015-10-29

#3已经改好了啊。

回复赞

agoni_xu 2015-10-29

我看了，我本来是用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 2015-10-29

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

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

回复赞

agoni_xu 2015-10-29

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的

回复赞

agoni_xu 2015-10-29

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

回复赞

Tiger_Zhao 2015-10-29

SELECT ...

  INTO 新表

  FROM ...

回复赞

agoni_xu 2015-10-29

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

回复赞

Tiger_Zhao 2015-10-28

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