我想从具有不同条件的同一张表中获取数据。我在android studio中使用sqlite。
**ID Name Role**
1 Emma Manager
2 Olivia Manager
3 Ava Manager
4 Isabella Sales officer
5 Sophia Sales Officer
6 Charlotte Sales Officer
7 Mia Clerk
8 Amelia Clerk
假设此表中，我具有不同类型的角色，并且一个角色中有不同数量的人员。我想从每个角色中选择2个人。
Select * from employeeTable where role = 'Manager' LIMIT 2
Select * from employeeTable where role = 'Sales officer' LIMIT 2
Select * from employeeTable where role = 'Clerk' LIMIT 2
我只是想加入这三个查询的结果。抱歉，如果是幼稚的问题。并预先感谢
具有row_number()视窗功能：
select t.id, t.name, t.role
from (
select *, row_number() over (partition by role) rn
from employeeTable
) t
where t.rn <= 2
您还可以定义：
partition by role order by name
要么：
partition by role order by id
不仅仅是：
partition by role
以获取结果中的特定行。
参见演示。
结果：
| ID | Name | Role |
| --- | -------- | ------------- |
| 7 | Mia | Clerk |
| 8 | Amelia | Clerk |
| 1 | Emma | Manager |
| 2 | Olivia | Manager |
| 4 | Isabella | Sales Officer |
| 5 | Sophia | Sales Officer |
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句