在特定日期之间从sql server重新收集数据

本文介绍了在特定日期之间从sql server重新收集数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想显示用户在两个日期之间登录我的网站的数量，例如01/02/2015到2015年3月22日，我的查询是：

选择LoggedIn，count（LoggedInFrom）来自User_log_Info的AS设备，其中LoggedIn在'03 / 20/2015 00:00:00'和'03/22/2015 23:59:59'和LoggedInFrom = 0 GROUP BY LoggedIn命令中由LoggedIn asc

结果是：

I want to show that how much user loggedin my site between two date like 01/02/2015 to 22/03/2015 and my query is:
"select LoggedIn,count(LoggedInFrom) AS Device from User_log_Info where LoggedIn between '03/20/2015 00:00:00' And '03/22/2015 23:59:59' And LoggedInFrom = 0 GROUP BY LoggedIn order by LoggedIn asc "

the result is :

id          LoggedIn        Device

1   2015-03-20 23:14:00     1
2   2015-03-21 07:13:00     1
3   2015-03-21 11:22:00     1
4   2015-03-21 11:24:00     1
5   2015-03-21 11:35:00     1
6   2015-03-22 14:07:00     1

但是我希望这样显示：

but i want to show like this:

id  	LoggedIn  Device

1   2015-03-20 	     1
2	2015-03-21 	     4
3	2015-03-22 	     1

我该怎么办？

how can i do this?

推荐答案

SELECT CAST(LoggedIn AS DATE)  LoggedIn
       ,COUNT(LoggedInFrom) AS Device
FROM User_log_Info
WHERE LoggedIn BETWEEN '03/20/2015 00:00:00' AND '03/22/2015 23:59:59'
      AND LoggedInFrom = 1
GROUP BY CAST(LoggedIn AS DATE)
ORDER BY LoggedIn ASC

请注意，我更改了一个条件LoggedInFrom = 1 根据提供的样本获取数据。

Note that I changed one condition LoggedInFrom = 1 to get data as per the sample provided.

select LoggedIn,count(LoggedInFrom) AS Device from User_log_Info where LoggedIn between '03/20/2015 00:00:00' And '03/22/2015 23:59:59' And LoggedInFrom = 0 GROUP BY CAST(LoggedIn) AS DATE) order by LoggedIn asc

这篇关于在特定日期之间从sql server重新收集数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！