用户ID在Sql basec中的日期计数 [英] Date Count in Sql basec on user id
问题描述
date userid
2012-12-30 21
2012-12-30 21
2012-12-31 21
2012-12-30 22
我希望结果看起来像
I would like the result to look like
userid totaldays
21 2
22 1
有两个相同的日期,因为在同一天它们会有两个条目,即上午和下午
我这样做:
There are two same date because on same date they will have two entry ie AM and PM
I''m trying like this:
SELECT
CONVERT(date,TOF.compoffdate,101),
count(compoffdate) as TotalDays
FROM tblcompoff TOF
WHERE TOF.userid=21
group by tof.compoffdate
怎么样? o继续吗?
How to proceed?
推荐答案
首先,你需要确保你有一个数据集,你想要计算行数。
在你的情况下您只想计算userid
的唯一日期,因此实现此目的的第一个SQL查询将是
First you need to make sure you have a dataset on which you want to count the rows.
In your case you only want to count the unique days for anuserid
, so the first SQL query to achieve this would be
SELECT DISTINCT CONVERT( date, compoffdate, 101 ) AS date, userid
FROM tblcompoff
此查询会产生以下行:
This query results in the following rows:
date userid
2012-12-30 21
2012-12-31 21
2012-12-30 22
现在基于此数据集,我们可以使用以下查询计算每个用户ID的关闭天数:
Now based on this data set we can count the number off days for each user id by using the following query:
SELECT TOF.userid, COUNT( TOF.date ) AS totaldays
FROM (SELECT DISTINCT CONVERT( date, compoffdate, 101 ) AS date, userid
FROM tblcompoff)
GROUP BY TOF.userid
查询执行对我们限制/指定数据的第一个查询的计数。
结果是:
The query performs the count over the first query we made to limit/specify our data.
The result is:
userid totaldays
21 2
22 1
我希望这可以帮助您完成清除结果的过程。
I hope this make the process off how to achieve your result clear.
select count(compoffdate),userid from tblcompoff group by userid
简单一个我认为它有用
Simple one i think its useful
使用此
Use this
SELECT userid , COUNT(DISTINCT DATEADD(dd, 0, DATEDIFF(dd, 0, compoffdate))) AS totaldays FROM tblcompoff
GROUP BY userid
这篇关于用户ID在Sql basec中的日期计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!