返回多列不相同的所有行/列

I have a query where I want to find rows where both ActivityDate and TaskId have multiple entries at the same time:

我有一个查询,我想找到其中ActivityDate和TaskId同时有多个条目的行:

SELECT
    ActivityDate, taskId
FROM
    [DailyTaskHours]
GROUP BY
    ActivityDate, taskId
HAVING 
    COUNT(*) > 1

The above query appears to work. However I want all of the columns to return now just the two (ActivityDate, taskId). This doesn't work:

上述查询似乎有效。但是我希望所有列现在只返回两个(ActivityDate,taskId)。这不起作用:

SELECT *
FROM
    [DailyTaskHours]
GROUP BY
    ActivityDate, taskId
HAVING 
    COUNT(*) > 1

because many of the columns are not in the group by clause. I don't want any columns to be effected by the HAVING COUNT(*) > 1 other than ActivityDate, taskId.

因为许多列不在group by子句中。我不希望任何列受到除ActivityDate,taskId之外的HAVING COUNT(*)> 1的影响。

How do I achieve this?

我该如何实现这一目标?

4 个解决方案

#1

SELECT t1.*
FROM
    [DailyTaskHours] t1
INNER JOIN (
  SELECT
      ActivityDate, taskId
  FROM
      [DailyTaskHours]
  GROUP BY
      ActivityDate, taskId
  HAVING 
      COUNT(*) > 1
) t2 ON (
  t1.ActivityDate = t2.ActivityDate AND
  t1.taskId = t2.taskId
)

#2

WITH sel as(
SELECT
    ActivityDate, taskId
FROM
    [DailyTaskHours]
GROUP BY
    ActivityDate, taskId
HAVING 
    COUNT(*) > 1
)
SELECT * 
      FROM [DailyTaskHours] d
           INNER JOIN sel ON d.ActivityDate = sel.ActivityDate AND d.taskId = sel.taskId

#3

-- fully functional example.
DECLARE @table TABLE ( ActivityDate DATE, TaskID INT);

SET NOCOUNT ON;
INSERT @table VALUES ('01/01/2013',1);
INSERT @table VALUES ('01/02/2013',1);
INSERT @table VALUES ('01/02/2013',2);
INSERT @table VALUES ('01/03/2013',1);
INSERT @table VALUES ('01/03/2013',2);
INSERT @table VALUES ('01/03/2013',5); -- duplicate date,taskid
INSERT @table VALUES ('01/03/2013',5); -- duplicate date,taskid
SET NOCOUNT OFF;

SELECT A.*
FROM @table A
    INNER JOIN (
        SELECT [ActivityDate], TaskId
        FROM @table
        GROUP BY [ActivityDate], TaskId
        HAVING Count(*) > 1
    ) AS B ON B.[ActivityDate]=A.ActivityDate AND B.TaskId=A.TaskId;

#4

Here is the SQL Fiddle that shows the following query in action:

下面是SQL Fiddle,它显示了以下查询:

SELECT DISTINCT m.* 
FROM 
(
  SELECT s.ActivityDate, s.taskId
  FROM DailyTaskHours s
  GROUP BY s.ActivityDate, s.taskId
  HAVING COUNT(*) > 1
) sub
JOIN DailyTaskHours m 
ON m.taskId = sub.taskId
AND m.ActivityDate = sub.ActivityDate