SQL 在sql中选择每个组的第一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16529701/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select First Row of Every Group in sql
提问by Vijay
I have two tables .
我有两张桌子。
1-> SM_Employee
1-> SM_Employee
(1) employeeid (2) roleid (3) storeid
(1) employeeid (2) roleid (3) storeid
2-> SM_SalesRepWorkflow
2-> SM_SalesRepWorkflow
(1) workflowid (2) Salesrepid foreign key to employeeid (3) QuantityAssigned (4) QuantityLeft (5) month (6) year
(1) workflowid (2) Salesrepid foreign key to employeeid (3) QuantityAssigned (4) QuantityLeft (5) month (6) year
By these tables I need to select first row of every SalesRep Details from SM_SalesRepWorkflow order by SalesRepId for CurrentMonth and CurrentYear.
通过这些表，我需要通过 SalesRepId 为 CurrentMonth 和 CurrentYear 从 SM_SalesRepWorkflow 订单中选择每个 SalesRep 详细信息的第一行。
Example
例子
Workflowid SalesRepId QuantityAssigned QuantityLeft Month Year
Workflowid SalesRepId QuantityAssigned QuantityLeft Month Year
WF_101 : EMP_101 : 100 : 90 : May : 2013
WF_101 : EMP_102 : 100 : 100 : May : 2013
WF_101 : EMP_103 : 100 : 80 : May : 2013
WF_102 : EMP_101 : 100 : 70 : May : 2013
WF_101 : EMP_101 : 100 : 90 : May : 2013
WF_101 : EMP_102 : 100 : 100 : May : 2013
WF_101 : EMP_103 : 100 :
0 : 1 0 May : 1 0 5月1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 5月1 0 5月1日
So result i want is
所以我想要的结果是
WF_101 : EMP_101 : 100 : 90 : May : 2013
WF_101 : EMP_102 : 100 : 100 : May : 2013
WF_101 : EMP_103 : 100 : 80 : May : 2013
WF_101：EMP_101：100：90：5 月：2013
WF_101：EMP_102：100：100：5 月：2013
WF_101：EMP_103：100：5 月：80
So There can be many Workflow for a SalesRep. But i want the first one for every SalesRep for current month and year.
所以销售代表可以有很多工作流程。但我想要第一个为当前月份和年份的每个 SalesRep。
回答by Rachcha
You can use the ROW_NUMBER()function like this:
您可以ROW_NUMBER()像这样使用该函数：
SELECT *
FROM(SELECT workflowid, salesRepId, quantityAssigned,
quantityLeft, month, year
, ROW_NUMBER()
OVER (PARTITION BY salesRepId
ORDER BY workflowid) AS rownumber
FROM sm_salesRepWorkflow)
WHERE rownumber = 1;
回答by BWStearns
I'm having a little trouble understanding the question so if I'm off I'll edit it after, but it looks like you want to do something like SELECT * FROM SM_SalesRepWorkflow WHERE Workflowid = latestwhere latest is the id of the workflow. Perhaps you can determine the latest outside of the query and bring that in instead of doing it from the query?
我在理解这个问题时遇到了一些麻烦，所以如果我离开了，我会在之后对其进行编辑，但看起来您想要做一些事情，例如SELECT * FROM SM_SalesRepWorkflow WHERE Workflowid = latest最新的是工作流的 ID。也许您可以确定查询之外的最新内容并将其引入而不是从查询中进行？
