SQL Server CE:选择组合MAX和Distinct？
SQL Server CE:选择组合MAX和Distinct？
提问于 2015-06-15 18:42:07
我对SQL非常陌生。我有一个表，基本上是一堆项目的结果表。不同的用户在不同的项目中工作，有不同的完成日期和水平。
以下是表的布局：
tblProjectLog:
[plogID] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
[projID] int NOT NULL,
[userID] int NOT NULL,
[presID] int NOT NULL,
[plogDateTime] datetime NOT NULL,
[plogNote] nvarchar(100)下面是我的初始SQL：
select
plog.plogID, plog.userID, plog.projID, plog.presID,
u.userFirstName + ' ' + u.userLastName AS [User],
proj.projName, pres.presResult, plog.plogDateTime, plog.plogNote
from
tblProjectLog plog
inner join
tblUser u on plog.userID = u.userID
inner join
tblProject proj on plog.projID = proj.projID
inner join
tblProjectResult pres on plog.presID = pres.presID
order by
plog.plogID asc它返回所有东西(因为没有MAX或DISTINCT语句，所以应该返回)。所以我得到的是：
plogID userID projID presID User projName presResult plogDateTime plogNote
2 1 1 2 John Smith Birdhouse Completed 2015-06-13 10:54:45.873 Well done. Sanding needs work.
3 3 1 2 Katie Williams Birdhouse Completed 2015-06-13 13:55:54.813 Excellent staining with solid work.
4 2 7 1 John Thomas Step Ladder In Progress 2015-06-13 15:57:20.593 going along well
5 6 2 3 Diane Stevens Chessboard Withdrawn 2015-06-13 16:57:57.393 leaving town for 2 weeks. will start new when she returns.
6 7 4 1 Lia McKenzie Lamp In Progress 2015-06-13 17:36:39.183 Working on electical routing
7 8 5 1 Stephanie Drake Toolbox 1 In Progress 2015-06-14 11:41:27.907 solid starting work
8 7 4 1 Lia McKenzie Lamp In Progress 2015-06-14 11:52:25.923 finished sanding. first day of stain.
9 9 3 1 Cheryl Lor Step Stool In Progress 2015-06-14 11:59:30.113 Learning to use a table saw. Nice start.
10 2 7 2 John Thomas Step Ladder Completed 2015-06-15 11:20:51.737 good finished project.
11 7 4 2 Lia McKenzie Lamp Completed 2015-06-15 11:21:31.723 Excellent work.
12 2 1 1 John Thomas Birdhouse In Progress 2015-06-15 13:08:53.407 starting complicated birdhouse design清除它以便于阅读(移除ID字段( plogID除外，它是tblProjectLog的主键)和无关字段)提供如下内容：
2 | John Smith | Birdhouse | Completed
3 | Katie Williams | Birdhouse | Completed
4 | John Thomas | Step Ladder | In Progress
5 | Diane Stevens | Chessboard | Withdrawn
6 | Lia McKenzie | Lamp | In Progress
7 | Stephanie Drake | Toolbox | In Progress
8 | Lia McKenzie | Lamp | In Progress
9 | Cheryl Lor | Step Stool | In Progress
10 | John Thomas | Step Ladder | Completed
11 | Lia McKenzie | Lamp | Completed
12 | John Thomas | Birdhouse | In Progress我想得到的是：
2 | John Smith | Birdhouse | Completed
3 | Katie Williams | Birdhouse | Completed
5 | Diane Stevens | Chessboard | Withdrawn
7 | Stephanie Drake | Toolbox | In Progress
9 | Cheryl Lor | Step Stool | In Progress
11 | Lia McKenzie | Lamp | Completed
12 | John Thomas | Birdhouse | In Progress但是，当我尝试添加一个MAX(plog.plogID)时，我会得到以下错误：
ErrorCode：-2147467259 Server压缩ADO.NET数据提供程序HResult：-2147217900，NativeError: 25518 ErrorMessage:在聚合表达式和分组表达式中，ORDER子句只能包含聚合函数和分组表达式。
当我在plog.userID字段中添加一个不同的标记时，我会得到一个令牌错误。
有谁能帮我试着只得到每个userID的最新条目，每个userID只有一个条目吗？是需要MAX和DISTINCT，还是sql有其他方法来完成这一任务？
哦，我忘了提到它是Server。谢谢你能提供的任何帮助。
回答 1
Stack Overflow用户
发布于 2015-06-15 21:22:57
另一个网站上的一个用户为我发布了一个完美的答案。我想赞扬用户(和网站)，但我不确定有关张贴其他网站和/或他们的用户的规则。
这是他发布的sql语句，它提供了我正在寻找的内容。希望这将有助于其他人，如果他们正在寻找类似的东西，我正在寻找。
select plog.plogID, plog.userID, plog.projID, plog.presID,
u.userFirstName + ' ' + u.userLastName AS [User],
proj.projName, pres.presResult, plog.plogDateTime, plog.plogNote
from tblProjectLog plog
inner join tblUser u on plog.userID = u.userID
inner join tblProject proj on plog.projID = proj.projID
inner join tblProjectResult pres on plog.presID = pres.presID
inner join
(SELECT MAX(plog.plogID) as m_plogID
FROM tblProjectLog plog
GROUP BY plog.userID
) M
ON m_plogID = plog.plogID
order by plog.plogID asc希望这能有所帮助。:)
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/30852686
复制相关文章
相似问题
腾讯云开发者
