只需要3条具有相同jobid的记录，然后跳到下一个作业

6jygbczu 于 2021-06-19 发布在 Mysql

关注(0)|答案(1)|浏览(297)

我需要从电影剧组中挑选8个人。我只想有3个生产者，然后让查询继续与其他工作。
这是我现在的问题：

SELECT EmployeeID, j.NameEN, e.Firstname, e.Lastname, j.ID AS JobId 
  FROM crew AS c
  LEFT JOIN job AS j ON c.JobID = j.ID
  LEFT JOIN employee AS e ON c.EmployeeID = e.Id
  WHERE c.MovieID = 237038
  ORDER BY j.SortOrder ASC, c.JobID ASC, e.Score DESC
  LIMIT 8

如何实现只选择3个工作id相同的员工？
样本输出

EmployeeID NameEN       Firstname     Lastname   JobId 
802        Director     Chris         Columbus   2
5707       Director     David         Yates      2
1705       Director     Mike          Newell     2
175        Director     Alfonso       Cuarón     2
5115       Writer       Steve         Kloves     3
2041       Writer       Michael       Goldenberg 3
11096      Writer       J.K.          Rowling    3

你可以看到我有4个导演。期望的输出是最多有3个控制器。

sql mysql

来源：https://stackoverflow.com/questions/52550743/only-want-3-records-with-the-same-jobid-then-skip-to-the-next-jobs

1条答案

按热度按时间

bnl4lu3b1#

我认为解决你这个问题最简单的方法是 union all :

(SELECT EmployeeID, j.NameEN, e.Firstname, e.Lastname, j.ID AS JobId 
 FROM crew c LFT JOIN
      job j
      ON c.JobID = j.ID LEFT JOIN
      employee e
      ON c.EmployeeID = e.Id
 WHERE c.MovieID = 237038 AND
       j.NameEN = 'Producer'
 ORDER BY j.SortOrder ASC, c.JobID ASC, e.Score DESC
 LIMIT 3
) UNION ALL
(SELECT EmployeeID, j.NameEN, e.Firstname, e.Lastname, j.ID AS JobId 
 FROM crew c LFT JOIN
      job j
      ON c.JobID = j.ID LEFT JOIN
      employee e
      ON c.EmployeeID = e.Id
 WHERE c.MovieID = 237038 AND
       j.NameEN <> 'Producer'
 ORDER BY j.SortOrder ASC, c.JobID ASC, e.Score DESC
 LIMIT 5
);

赞(0）回复(0）举报 2021-06-20

我来回答

只需要3条具有相同jobid的记录，然后跳到下一个作业

1条答案

相关问题

热门标签

最新问答