按正确使用分组
TL;DR 如何返回列而不将它们添加到 MSSQL 中的 GROUP BY 子句？刑事陈述如下。
大家好， 所以我最近问了这个问题并得到了一个很好的解决方案能够解剖和重新组装以解决我的问题。这是最终结果
WITH UC AS
(
SELECT OSJ.opportunityid, OS.status,
ROW_NUMBER() OVER (PARTITION BY OSJ.opportunityid ORDER BY creationdate DESC) RN
FROM Opportunitystatusjoin OSJ
JOIN OpportunityStatus OS ON OSJ.opportunitystatusid = OS.opportunitystatusid
)
SELECT O.opportunityid , O.salesperson ,
MAX( CASE
WHEN RN = 1
THEN UC.status
END ) AS MostRecent ,
MAX( CASE
WHEN RN = 2
THEN UC.status
END ) AS SecondMostRecent
FROM Opportunity O
JOIN UC ON UC.opportunityid = O.opportunityid
WHERE UC.RN <= 2
GROUP BY O.opportunityid , O.salesperson
现在，如果我想从 Opportunity 表中返回更多列，即 O.shoesize、O.favorite_color，我必须将它们添加到我的 GROUP BY 子句中。但我只是有一种肮脏的感觉，因为我并不想将其他专栏分组，我只是希望他们以各自的机会出现。
我在互联网上找到的一个博客说要将 GROUP BY 子句尽可能地填充到嵌套选择中。然而，由于我使用的是第一个语句（“with UC...”），这似乎使事情变得不那么直观。包含表中的其他列而不将它们添加到 GROUP BY 子句的聪明方法是什么？
TL;DR How do I return columns without adding them to the GROUP BY clause in MSSQL ? The criminal statement is below.
Hello Everyone,
So I recently asked this question and got a great solution which I was able to dissect and reassemble to suite my problem. Here is the final result
WITH UC AS
(
SELECT OSJ.opportunityid, OS.status,
ROW_NUMBER() OVER (PARTITION BY OSJ.opportunityid ORDER BY creationdate DESC) RN
FROM Opportunitystatusjoin OSJ
JOIN OpportunityStatus OS ON OSJ.opportunitystatusid = OS.opportunitystatusid
)
SELECT O.opportunityid , O.salesperson ,
MAX( CASE
WHEN RN = 1
THEN UC.status
END ) AS MostRecent ,
MAX( CASE
WHEN RN = 2
THEN UC.status
END ) AS SecondMostRecent
FROM Opportunity O
JOIN UC ON UC.opportunityid = O.opportunityid
WHERE UC.RN <= 2
GROUP BY O.opportunityid , O.salesperson
Now if I want to return more columns from the Opportunity table i.e O.shoesize, O.favorite_color, I have to add them to my GROUP BY clause. But I just get a dirty feeling because I am not trying to group the other columns, I just want them to show up with their respective opportunity.
A blog I found on the internet said to stuff your GROUP BY clauses as far in to your nested selects as possible. However, since I am using the first statement ( "with UC...") that seems to make things less intuitive. What is a smart way to include other columns from my table without adding them to the GROUP BY clause.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您分组的所有内容的列值都相同，那么您可以执行以下操作：
If the columns are going to be the same value for everything you are grouping by, then you could do something like this: