英文:
SQL Server Group By and First in priority
问题
我有一个数据表
SQL Fiddle
http://sqlfiddle.com/#!18/b33c86
模式
创建表lenderdata(ID int 身份主键,LinkID varchar(250) 空,Lender varchar(250) 空,Item varchar(250) 空,Priority int 空,Quantity int 空,Status varchar(250) 空);向lenderdata插入（LinkID，Lender，Item，Priority，Quantity，Status）的值（'001'，'A'，'Apple'，1，100，'PENDING'）;向lenderdata插入（LinkID，Lender，Item，Priority，Quantity，Status）的值（'001'，'B'，'Orange'，2，100，'PENDING'）;向lenderdata插入（LinkID，Lender，Item，Priority，Quantity，Status）的值（'002'，'C'，'Strawberry'，1，1000，'PENDING'）;向lenderdata插入（LinkID，Lender，Item，Priority，Quantity，Status）的值（'002'，'D'，'grapes'，2，100，'PENDING'）;向lenderdata插入（LinkID，Lender，Item，Priority，Quantity，Status）的值（'003'，'E'，'coffee'，1，1000，'PROCESSING'）;向lenderdata插入（LinkID，Lender，Item，Priority，Quantity，Status）的值（'003'，'F'，'mango'，2，1000，'PENDING'）;
我想按Link ID分组，并仅选择优先级最低的状态为PENDING的数据。
如果分组数据有其他状态，例如PROCESSING，那么它应该简单地忽略该组。
只有当所有状态都是PENDING，才排除具有PROCESSING状态的组。
如果我运行查询，它将仅返回Link ID 001和002。
英文:
I have a data table
SQL Fiddle
http://sqlfiddle.com/#!18/b33c86
Schema
create table lenderdata(ID int identityprimary key,LinkID varchar(250) null,Lender varchar(250) null,Item varchar(250) null,Priority int null,Quantity int null,Status varchar(250) null);INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('001', 'A', 'Apple', 1, 100, 'PENDING');INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('001', 'B', 'Orange', 2, 100, 'PENDING');INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('002', 'C', 'Strawberry', 1, 1000, 'PENDING');INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('002', 'D', 'grapes', 2, 100, 'PENDING');INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('003', 'E', 'coffee', 1, 1000, 'PROCESSING');INSERT INTO lenderdata (LinkID, Lender, Item, Priority, Quantity, Status) VALUES ('003', 'F', 'mango', 2, 1000, 'PENDING');
I want to group by Link ID and pick up only the one with minimum of priority if the status is PENDING
If the group by data has other status e.g. PROCESSING then it should simply ignore that group
Only if all the status is either PENDING excluding the group if there is one with PROCESSING status
If I was to run the query it would only return Link ID 001 and 002
答案1
得分: 1
以下是翻译好的部分：
select LinkIdfrom lenderdatagroup by LinkIdhaving Min(status) = 'Pending' and Max(Status) = 'Pending';
英文:
Your description is hard to make sense of but given your expectation perhaps this is what you require?
select LinkIdfrom lenderdatagroup by LinkIdhaving Min(status) = 'Pending' and Max(Status) = 'Pending';
答案2
得分: 1
以下是已翻译的内容：
You could use the following where clause to filter out any LinkID group that has a status <> 'pending':
使用以下的 where 子句可以筛选掉任何具有状态 <> 'pending' 的 LinkID 组：
WHERE LinkID NOT IN
(
SELECT LinkID FROM lenderdata WHERE Status <> 'PENDING'
)
Now, according to your request: (I want to group by Link ID and pick up only the one with a minimum priority)
根据您的请求：（我想按Link ID分组，并仅选择具有最低优先级的行）
If you meant to select rows with a minimum priority for each LinkID group, then you may use the row_number function approach.
如果您想选择每个 LinkID 组的具有最低优先级的行，那么您可以使用 row_number 函数的方法。
WITH CTE AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY LinkID ORDER BY Priority) rn
FROM lenderdata
WHERE LinkID NOT IN
(
SELECT LinkID FROM lenderdata WHERE Status <> 'PENDING'
)
)
SELECT ID, LinkID, Lender, Item, Priority, Quantity, Status
FROM CTE WHERE rn = 1
If you meant to select rows with a minimum priority among all LinkID groups, then you may use the rank function approach.
如果您想选择在所有 LinkID 组中具有最低优先级的行，那么您可以使用 rank 函数的方法。
WITH CTE AS
(
SELECT *,
RANK() OVER (ORDER BY Priority) rnk
FROM lenderdata
WHERE LinkID NOT IN
(
SELECT LinkID FROM lenderdata WHERE Status <> 'PENDING'
)
)
SELECT ID, LinkID, Lender, Item, Priority, Quantity, Status
FROM CTE WHERE rnk = 1
See a demo for your sample data.
查看演示 以获取您的示例数据。
See a demo for modified sample data to see the difference between the two approaches (your sample data will get you the same results for both approaches).
查看演示 以查看修改后的示例数据，了解两种方法之间的区别（对于两种方法，您的示例数据将获得相同的结果）。
英文:
You could use the following where clause to filter out any LinkID group that has a status <> 'pending':
WHERE LinkID NOT IN(SELECT LinkID FROM lenderdata WHERE Status <> 'PENDING')
Now, according to your request: (I want to group by Link ID and pick up only the one with a minimum priority)
If you meant to select rows with a minimum priority for each LinkID group, then you may use the row_number function approach.
WITH CTE AS(SELECT *,ROW_NUMBER() OVER (PARTITION BY LinkID ORDER BY Priority) rnFROM lenderdataWHERE LinkID NOT IN(SELECT LinkID FROM lenderdata WHERE Status <> 'PENDING'))SELECT ID, LinkID, Lender, Item, Priority, Quantity, StatusFROM CTE WHERE rn = 1
If you meant to select rows with a minimum priority among all LinkID groups, then you may use the rank function approach.
WITH CTE AS(SELECT *,RANK() OVER (ORDER BY Priority) rnkFROM lenderdataWHERE LinkID NOT IN(SELECT LinkID FROM lenderdata WHERE Status <> 'PENDING'))SELECT ID, LinkID, Lender, Item, Priority, Quantity, StatusFROM CTE WHERE rnk = 1
See a demo for your sample data.
See a demo for modified sample data to see the difference between the two approaches (your sample data will get you the same results for both approaches).
答案3
得分: -1
SELECT ld.LinkID, ld.Lender, ld.Item, ld.Priority, ld.Quantity, ld.Status
FROM lenderdata ld
INNER JOIN (
SELECT LinkID, MIN(Priority) as MinPriority
FROM lenderdata
WHERE Status = 'PENDING'
GROUP BY LinkID
HAVING COUNT(DISTINCT Status) = 1
) groupedLd
ON ld.LinkID = groupedLd.LinkID AND ld.Priority = groupedLd.MinPriority
WHERE ld.Status = 'PENDING';
英文:
SELECT ld.LinkID, ld.Lender, ld.Item, ld.Priority, ld.Quantity, ld.StatusFROM lenderdata ldINNER JOIN (SELECT LinkID, MIN(Priority) as MinPriorityFROM lenderdataWHERE Status = 'PENDING'GROUP BY LinkIDHAVING COUNT(DISTINCT Status) = 1) groupedLdON ld.LinkID = groupedLd.LinkID AND ld.Priority = groupedLd.MinPriorityWHERE ld.Status = 'PENDING'
This is the right answer. Got it from ....
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论