mssql中DATEDIFF问题

[zixiang82]

access一周之内的语句如下
sql = "SELECT TOP 6 shop_shangpin.shopid, shop_shangpin.shoppic, shop_shangpin.cjname, shop_shangpin.shopname " &_
"FROM (SELECT shop_action.actiondate AS actiondate, shop_action.shopid AS shopid FROM shop_action WHERE shop_action.dingdan <> '' UNION ALL SELECT shop_action_FHY.actiondate AS actiondate, shop_action_FHY.shopid AS shopid FROM shop_action_FHY WHERE shop_action_FHY.dingdan <> '') AS tmp INNER JOIN shop_shangpin ON tmp.shopid=shop_shangpin.shopid " &_
"WHERE DateDiff('d', tmp.actiondate, '"&Date()&"') <= 7 " &_
"GROUP BY shop_shangpin.shopid, shop_shangpin.shopname, shop_shangpin.shoppic, shop_shangpin.cjname, shop_shangpin.chengjiaocount " &_
"ORDER BY shop_shangpin.chengjiaocount DESC"

转成mssql语句 该如何写
我试过几次了 都不行

[AcHerat]

--WHERE DateDiff('d', tmp.actiondate, '"&Date()&"') <= 7 

WHERE DateDiff(dd,tmp.actiondate,@date) <= 7   --@date 所有需要的日期 

[zixiang82]

sql = "SELECT TOP 6 shop_shangpin.shopid, shop_shangpin.shoppic, shop_shangpin.cjname, shop_shangpin.shopname " &_
"FROM (SELECT shop_action.actiondate AS actiondate, shop_action.shopid AS shopid FROM shop_action WHERE shop_action.dingdan is not null UNION ALL SELECT shop_action_FHY.actiondate AS actiondate, shop_action_FHY.shopid AS shopid FROM shop_action_FHY WHERE shop_action_FHY.dingdan is not null) AS tmp INNER JOIN shop_shangpin ON tmp.shopid=shop_shangpin.shopid " &_
"WHERE DateDiff(d, tmp.actiondate, getdate()) >= 7 " &_
"GROUP BY shop_shangpin.shopid, shop_shangpin.shopname, shop_shangpin.shoppic, shop_shangpin.cjname, shop_shangpin.chengjiaocount " &_
"ORDER BY shop_shangpin.chengjiaocount DESC"

上面mssql语句我这样写可对
acces中的DateDiff('d', tmp.actiondate, '"&Date()&"') <= 7 一周之内 是这样写的吗

[-晴天]

SELECT TOP 6 shop_shangpin.shopid, shop_shangpin.shoppic, shop_shangpin.cjname, shop_shangpin.shopname --" &_
FROM (SELECT shop_action.actiondate AS actiondate, shop_action.shopid AS shopid FROM shop_action WHERE shop_action.dingdan <> '' UNION ALL SELECT shop_action_FHY.actiondate AS actiondate, shop_action_FHY.shopid AS shopid FROM shop_action_FHY WHERE shop_action_FHY.dingdan <> '') AS tmp INNER JOIN shop_shangpin ON tmp.shopid=shop_shangpin.shopid --" &_
WHERE DateDiff(d, tmp.actiondate, GETDATE()) <= 7 --" &_
GROUP BY shop_shangpin.shopid, shop_shangpin.shopname, shop_shangpin.shoppic, shop_shangpin.cjname, shop_shangpin.chengjiaocount --" &_
ORDER BY shop_shangpin.chengjiaocount DESC--"

[zixiang82]

sql = "SELECT TOP 6 shop_shangpin.shopid, shop_shangpin.shoppic, shop_shangpin.cjname, shop_shangpin.shopname " &_
"FROM (SELECT shop_action.actiondate AS actiondate, shop_action.shopid AS shopid FROM shop_action WHERE shop_action.dingdan is not null UNION ALL SELECT shop_action_FHY.actiondate AS actiondate, shop_action_FHY.shopid AS shopid FROM shop_action_FHY  WHERE shop_action_FHY.dingdan is not null)  AS tmp INNER JOIN shop_shangpin ON tmp.shopid=shop_shangpin.shopid " &_
"WHERE DateDiff(d, tmp.actiondate, GETDATE()) <= 7 " &_
"GROUP BY shop_shangpin.shopid, shop_shangpin.shopname, shop_shangpin.shoppic, shop_shangpin.cjname, shop_shangpin.chengjiaocount " &_
"ORDER BY shop_shangpin.chengjiaocount DESC"

是这样写的把

[zixiang82]

估计 是我缓存的问题 始终 看不到效果

[AcHerat]

sql = "SELECT TOP 6 shop_shangpin.shopid, shop_shangpin.shoppic, shop_shangpin.cjname, shop_shangpin.shopname "
"FROM (SELECT shop_action.actiondate AS actiondate, shop_action.shopid AS shopid FROM shop_action WHERE shop_action.dingdan is not null UNION ALL SELECT shop_action_FHY.actiondate AS actiondate, shop_action_FHY.shopid AS shopid FROM shop_action_FHY  WHERE shop_action_FHY.dingdan is not null)  AS tmp INNER JOIN shop_shangpin ON tmp.shopid=shop_shangpin.shopid "
"WHERE DateDiff(dd, tmp.actiondate, GETDATE()) <= 7 "
"GROUP BY shop_shangpin.shopid, shop_shangpin.shopname, shop_shangpin.shoppic, shop_shangpin.cjname, shop_shangpin.chengjiaocount "
"ORDER BY shop_shangpin.chengjiaocount DESC"


--你看你这段打印出来能在数据库里执行不?

[-晴天]

[Quote=引用 5 楼 zixiang82 的回复:]
估计 是我缓存的问题 始终 看不到效果
[/Quote]
你把我写的语句拿到SQL企业管理器里去运行,看是否能查询到数据.

[zixiang82]

SELECT TOP 6 shop_shangpin.shopid, shop_shangpin.shoppic, shop_shangpin.cjname, shop_shangpin.shopname 
FROM (SELECT shop_action.actiondate AS actiondate, shop_action.shopid AS shopid FROM shop_action WHERE shop_action.dingdan is not null UNION ALL SELECT shop_action_FHY.actiondate AS actiondate, shop_action_FHY.shopid AS shopid FROM shop_action_FHY  WHERE shop_action_FHY.dingdan is not null)  AS tmp INNER JOIN shop_shangpin ON tmp.shopid=shop_shangpin.shopid 
WHERE DateDiff(d, tmp.actiondate, GETDATE()) <= 7 
GROUP BY shop_shangpin.shopid, shop_shangpin.shopname, shop_shangpin.shoppic, shop_shangpin.cjname, shop_shangpin.chengjiaocount
ORDER BY shop_shangpin.chengjiaocount DESC

测试了 可以运行

[--小F--]

sql = "SELECT TOP 6 shop_shangpin.shopid, shop_shangpin.shoppic, shop_shangpin.cjname, shop_shangpin.shopname " &_
"FROM (SELECT shop_action.actiondate AS actiondate, shop_action.shopid AS shopid FROM shop_action WHERE shop_action.dingdan is not null UNION ALL SELECT shop_action_FHY.actiondate AS actiondate, shop_action_FHY.shopid AS shopid FROM shop_action_FHY  WHERE shop_action_FHY.dingdan is not null)  AS tmp INNER JOIN shop_shangpin ON tmp.shopid=shop_shangpin.shopid " &_
"WHERE DateDiff(d, tmp.actiondate, GETDATE()) <= 7 " &_
"GROUP BY shop_shangpin.shopid, shop_shangpin.shopname, shop_shangpin.shoppic, shop_shangpin.cjname, shop_shangpin.chengjiaocount " &_
"ORDER BY shop_shangpin.chengjiaocount DESC"