查找

[好帅的一条鱼]

SELECT Date,Time,MachineId,COUNT(EmployeeName)
FROM AttendanceCollect
WHERE Date >='2010-04-12'
GROUP BY Date,Time,MachineId
ORDER BY COUNT(EmployeeName) DESC,Date,Time

我如何在不改变结果的前提下，将姓名也显示出来呢

[htl258_Tony]

SELECT Date,Time,MachineId,Name,COUNT(EmployeeName)
FROM AttendanceCollect
WHERE Date >='2010-04-12'
GROUP BY Date,Time,MachineId,Name
ORDER BY COUNT(EmployeeName) DESC,Date,Time

类似这样的

[htl258_Tony]

SELECT Date,Time,MachineId,EmployeeName,COUNT(EmployeeName)
FROM AttendanceCollect
WHERE Date >='2010-04-12'
GROUP BY Date,Time,MachineId,EmployeeName
ORDER BY COUNT(EmployeeName) DESC,Date,Time

[好帅的一条鱼]

那个姓名就是统计的那个字段EmployeeName

[老黎]

[Quote=引用楼主 good212164 的回复:]
SELECT Date,Time,MachineId,COUNT(EmployeeName)
FROM AttendanceCollect
WHERE Date >='2010-04-12'
GROUP BY Date,Time,MachineId
ORDER BY COUNT(EmployeeName) DESC,Date,Time

我如何在不改变结果的前提下，将姓名也显示出来呢
[/Quote]
在一个group中两个name不一样的时候，name怎么拿？任取一个name？

[好帅的一条鱼]

如果group by里面加入姓名，最后的结果就变了，变为按姓名分类了，结果只要按日期时间机器来查相同的

[htl258_Tony]

[Quote=引用 5 楼 good212164 的回复:]
如果group by里面加入姓名，最后的结果就变了，变为按姓名分类了，结果只要按日期时间机器来查相同的
[/Quote]那你准备让SQL显示成怎么样？

[老黎]

[Quote=引用 5 楼 good212164 的回复:]
如果group by里面加入姓名，最后的结果就变了，变为按姓名分类了，结果只要按日期时间机器来查相同的
[/Quote]
机器？

[好帅的一条鱼]

查找 Date,Time,MachineId这三个字段相同的记录

[htl258_Tony]

SELECT Date,Time,MachineId,COUNT(EmployeeName)[COUNT],
	EmployeeName=STUFF((select ','+EmployeeName from AttendanceCollect where MachineId=t.MachineId group by EmployeeName for XML path('')),1,1,'')
FROM AttendanceCollect t
WHERE Date >='2010-04-12'
GROUP BY Date,Time,MachineId
ORDER BY COUNT(EmployeeName) DESC,Date,Time

SQL2005的试试这样。看是不是你要的。

[老黎]

SELECT 
    *
FROM 
    AttendanceCollect a
WHERE 
    Date >='2010-04-12'
and
    (select 1 from AttendanceCollect where Date = a.Date and Time = a.Time and MachineId = a.MachineId)>1
GROUP BY Date,Time,MachineId

[好帅的一条鱼]

，还不行

[dreamloster]

楼主，根据AttendanceCollect ，用个内连接连接下原来的表AttendanceCollect

SELECT AttendanceCollect.EmployeeName,TempTable.* FROM
(SELECT Date,Time,MachineId,COUNT(EmployeeName)
 FROM AttendanceCollect
 WHERE Date >='2010-04-12'
 GROUP BY Date,Time,MachineId
 ORDER BY COUNT(EmployeeName) DESC,Date,Time) TempTable INNER JOIN AttendanceCollect
ON  TempTable.MachineId=AttendanceCollect.MachineId