用户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 an userid, 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屋！