22,210
社区成员
发帖
与我相关
我的任务
分享
--按天group by索引会失效。
SELECT dt=CONVERT(CHAR(10),dt,120),cnt=COUNT(*) FROM tb
WHERE dt >= '2013-07-01' AND dt < '2013-08-01'
GROUP BY CONVERT(CHAR(10),dt,120) --可以先过滤再GROUP BY;如果你对所有数据都GROUP BY,也就没必须用索引了
[/quote]
如果我要统计7月份里超过10天都有记录的ID列表呢?--按天group by索引会失效。
SELECT dt=CONVERT(CHAR(10),dt,120),cnt=COUNT(*) FROM tb
WHERE dt >= '2013-07-01' AND dt < '2013-08-01'
GROUP BY CONVERT(CHAR(10),dt,120) --可以先过滤再GROUP BY;如果你对所有数据都GROUP BY,也就没必须用索引了
select * from 表A
where [字段] between '2013-07-01 00:00:00' and '2013-07-31 23:59:59.997' --最好加上.997
select * from 表A
where [字段] between '2013-07-01 00:00:00' and '2013-07-31 23:59:59'
SELECT * FROM tb
WHERE dt >= '2013-07-01' AND dt < '2013-08-01' --dt不要放在函数里,右边的日期可以动态地算出来
--如果你的10天不要求连续的话,参考如下:
SELECT id, total=SUM(total)
FROM
(
SELECT
id,
[date]=CONVERT(CHAR(10),dt, 120),
total=SUM([money]),
cnt = COUNT(*)
FROM #temp
WHERE dt >= '20130701' AND dt < '20130801'
GROUP BY [id], CONVERT(CHAR(10),dt, 120)
) t
GROUP BY id
HAVING COUNT(*) > 10