针对多个标准的不同记录的SQL计数

I have a table in SQL SERVER "usage" that consists of 4 columns

我在SQL SERVER“usage”中有一个包含4列的表

userID, appid, licenseid, dateUsed

Now I need to query the table so that I can find how many users used an app under a license within a time frame. Bearing in mind that even if user 1 used the app on say january 1st and 2nd they would only count as 1 in the result. i.e unique users in the time frame.

现在我需要查询这个表，以便在一段时间内找到有多少用户使用了许可证下的应用程序。记住，即使用户1在1月1日和2日使用了这个应用程序，结果也只能算作1。我。e在时间范围内唯一的用户。

so querying

所以查询

where dateUsed > @from  AND dateUsed < @dateTo

what I would like to get back is a table of rows like

我想要返回的是一个像这样的行表

, appid, licenseid

appid,licenseid

Has anyone any ideas how? I have been playing with count as subquery and then trying to sum the results but no success yet.

有人知道怎么做吗?我一直在玩count as子查询，然后试着总结结果，但还没有成功。

4 个解决方案

#1

Just tested this and provides your desired result:

刚刚测试了这个，并提供了您想要的结果:

SELECT appid, licenseid, COUNT(DISTINCT userID)
FROM usage
WHERE dateUsed BETWEEN @from AND @dateTo
GROUP BY appid, licenseid

#2

Something like this should work:

像这样的东西应该是有用的:

SELECT   appid, licenseid, COUNT(DISTINCT userid) as DistictUsers
FROM     yourTable
WHERE    dateUsed BETWEEN @from AND @dateTo
GROUP BY appid, licenseid

#3

Just copy, paste and run to see if it works as you want it to:

复制，粘贴，然后运行看看它是否按你的要求运行:

SELECT appId, licenseId, COUNT(userId)
FROM usage
WHERE dateUsed > @from AND dateUsed < @dateTo
GROUP BY appId, licenseId

#4

You haven't posted a query here but. I suggest you use PARTITION BY to produce a unique row count for each license id. You can create a sub query to the number of users using each separate license. The SELECT .... FROM this sub query and use a HAVING clause to filter on your preferred dates/users. Perhaps not the best explanation. Please post an example query.

您还没有在这里发布查询。我建议您使用PARTITION BY为每个许可证id生成惟一的行数。选择....从这个子查询中，使用一个have子句来筛选首选日期/用户。也许这不是最好的解释。请发布一个示例查询。

#1