SQL查询/子查询需要帮助
我想从测试信息表中获取的SQL Server 2005上的最后10序列号的列表,我想是这样的:SQL查询/子查询需要帮助
SELECT DISTINCT TOP (10) Serial, DateTime
FROM [Test].[dbo].[TestInfo]
WHERE (TestedBy = 'JSMITH') ORDER BY DateTime DESC
返回重复序列号:
+---------+-------------------------+
| Serial | DateTime |
+-----------------------------------+
| 1114048 | 2011-03-16 11:03:14.000 |
| 1617683 | 2011-03-11 15:07:29.000 |
| 1617683 | 2011-03-11 15:07:27.000 |
| 1617683 | 2011-03-11 15:07:26.000 |
| 1617683 | 2011-03-10 13:16:04.000 |
| 1617683 | 2011-03-10 13:15:35.000 |
| 1617683 | 2011-03-10 13:15:30.000 |
| 1617683 | 2011-03-07 13:42:48.000 |
| 1617683 | 2011-03-07 13:40:32.000 |
| 1617683 | 2011-03-07 13:37:58.000 |
+---------+-------------------------+
有没有一种方法,或者使用查询或子查询来获取最后的10个序列号没有重复?
select top (10) Serial, Max(DateTime)
from [Test].[dbo].[TestInfo]
where (TestedBy = 'JSMITH')
group by Serial
order by Max(DateTime) desc
是的,这也可以工作! – 2011-03-24 16:18:21
我接受了你的答案,但我从SELECT部分删除了Max(DateTime),因为我真的只需要序列列表:SELECT TOP(10)Serial FROM [Test]。[dbo]。[TestInfo] WHERE (TestedBy ='JSMITH') GROUP BY Serial ORDER BY Max(日期时间)DESC – 2011-03-24 17:22:59
它返回重复行,因为每行的日期时间不同。 如果您只需要串行字段,则必须在查询中只写入“串行”字段。
SELECT TOP 10
*
FROM (SELECT
Serial,
MAX(DateTime) AS DateTime
FROM [Test].[dbo].[TestInfo]
WHERE (TestedBy = 'JSMITH')
GROUP BY Serial) q1
ORDER BY q1.DateTime DESC
是的,它的工作原理! – 2011-03-24 16:17:56
大概是这样的:
SELECT DISTINCT TOP (10) Serial, DateTime
FROM (
SELECT Serial, MAX(DateTime) AS DateTime
FROM [Test].[dbo].[TestInfo]
WHERE (TestedBy = 'JSMITH')
GROUP BY Serial
) AS sub
ORDER BY DateTime DESC
是的,这也适用! – 2011-03-24 16:19:29
感谢您的帮助,所有。 – 2011-03-24 17:24:49