SQL 请帮忙看下

Write a query to select the top 3 team leads with the least amount of operators.

SELECT * FROM operator LIMIT 5 RIGHT JOIN team_lead LIMIT 5 ON operator LIMIT 5. tl_id= team_lead LIMIT 5. tl_id

COUNT(*) OVER (PARTITION BY tl_id )

ORDER BY DESC
请大家帮忙看看错哪了
----------------------------
SELECT * FROM operator LIMIT 5;





/*



+------------+------------+------------+------------+



| ops_id | first_name | last_name  | tl_id |



+------------+------------+------------+------------+



|          1 | Tanisha    | Glyndwr      |          2 |



|          2 | Jess       | Suresh       |       NULL |



|          3 | Tracy      | Skuld        |          3 |



|          4 | Alvin      | Uzziah      |          1 |



|          5 | Felix      | Wu           |          2 |



+------------+------------+------------+------------+



5 rows in set (0.00 sec)



*/





SELECT * FROM team_lead LIMIT 5;



/*



+------------+------------+-----------+



| tl_id | first_name | last_name |



+------------+------------+-----------+



|          1 | James      | Stjepan   |



|          2 | Amy        | Lindita     |



|          3 | Lamar      | Muhsin |



|          4 | Anita      | Dareios     |



|          5 | Jackie     | Black      |



+------------+------------+-----------+



5 rows in set (0.00 sec)



*/

Not tested.

1. select tl_id, first_name, last_name
   
2. from (
   
3.     select
   
4.         count(ops_id) as ops_count,
   
5.         tl_id,
   
6.         tl.first_name,
   
7.         tl.last_name
   
8.     from team_lead as tl
   
9.     left join operator as ops using(tl_id)
   
10.     group by tl_id
    
11.     order by ops_count desc
    
12.     limit 3
    
13. ) as tbl

复制代码

CH3COOH 发表于 2021-2-2 10:49
Not tested.
[mw_shl_code=sql,true]select tl_id, first_name, last_name
from (

谢谢回复

Not sure which SQL language you are using, script below with basic logic:

1. SELECT *
   
2. FROM (
   
3.         SELECT tl_id, ROW_NUMBER() OVER(PARTITION BY tl_id ORDER BY count_op) as rank_op
   
4.         FROM (
   
5.                 SELECT t.tl_id, COUNT(1) as count_op
   
6.                 FROM operator o
   
7.                 RIGHT JOIN team_lead t
   
8.                         ON o.tl_id = t.tl_id
   
9.                 GROUP BY t.tl_id
   
10.                 ) a
    
11.         ) b
    
12. WHERE rank_op < 4;

复制代码

You could pick one of rank function (RANK, DENSE_RANK, ROW_NUMBER) based on request.
Hope it helps

Below is using SQL Server:

1. SELECT tmp1.tl.id
   
2. FROM (
   
3.         SELECT ROW_NUMBER() OVER (
   
4.                         ORDER BY num_operators ASC
   
5.                         ) AS row_id
   
6.                 ,tmp.tl.id
   
7.         FROM (
   
8.                 SELECT tl.tl_id
   
9.                         ,count(p.operator_id) AS num_operators
   
10.                 FROM team_lead tl
    
11.                 LEFT JOIN operators p ON tl.tl_id = p.tl_id
    
12.                 GROUP BY tl.tl_id
    
13.                 ) AS tmp
    
14.         ) AS tmp1
    
15. WHERE tmp1.row_id <= 3

复制代码

Two more things you might need to consider:
1) How to break ties. For example, if there are more than 3 team leads who do not have operators, how to find the top 3?
2) Do not partition by tl_id inside ROW_NUMBER function, or else you will find all row number ids are 1.

Hope this helps.

去加州买保时捷 发表于 2021-2-2 22:39
Not sure which SQL language you are using, script below with basic logic:
[mw_shl_code=sql,true]SEL ...

谢谢回复。

jeremyhu87 发表于 2021-2-2 23:57
Below is using SQL Server:

[mw_shl_code=sql,true]SELECT tmp1.tl.id

非常感谢

inner join , group by, order by desc limit