mysql - 如何查询哪个员工是全职员工

Question

我有以下 MySQL 表:

+----+---------+----------------+------------+
| id | user_id | employment_type| date       |
+----+---------+----------------+------------+
|  1 |       9 | full-time      | 2013-01-01 |
|  2 |       9 | half-time      | 2013-05-10 |
|  3 |       9 | full-time      | 2013-12-01 |
|  4 |     248 | intern         | 2015-01-01 |
|  5 |     248 | full-time      | 2018-10-10 |
|  6 |      58 | half-time      | 2020-10-10 |
|  7 |     248 | NULL           | 2021-01-01 |
+----+---------+----------------+------------+

例如，我想查询哪些员工在 2014-01-01 是全职员工。
我需要传递哪个 SQL 查询才能获得正确的结果？
在这种情况下，结果将是 user_id=9 的员工；
这张表的结构是否合理才能得到这样的结果？

Answer 1

如果您的 MySql 版本是 8.0+，您可以使用 FIRST_VALUE() 窗口函数来实现:

SELECT DISTINCT user_id
FROM (
  SELECT user_id,
         FIRST_VALUE(employment_type) OVER (PARTITION BY user_id ORDER BY date DESC) last_type
  FROM tablename
  WHERE date <= '2014-01-01'
) t
WHERE last_type = 'full-time'

对于以前版本的 MySql，您可以使用 NOT EXISTS 来实现:

SELECT t1.user_id
FROM tablename t1
WHERE t1.date <= '2014-01-01' AND t1.employment_type = 'full-time'
  AND NOT EXISTS (
    SELECT 1 
    FROM tablename t2 
    WHERE t2.user_id = t1.user_id AND t2.date BETWEEN t1.date AND '2014-01-01' 
      AND COALESCE(t2.employment_type, '') <> t1.employment_type
  )

见 demo .
结果:

| user_id |
| ------- |
|       9 |