Question

下表显示了哪个部门雇员,哪个部门是其主要部门。如果雇员只属于某个部门,则其主要国旗将显示N(尽管这是其唯一部门),如果他们有多个部门,则该国旗将标明Y或N。

雇员:

employee_id	department_id	primary_flag
1	1	N
2	1	Y
2	2	N
3	3	N
4	2	N
4	3	Y
4	4	N

预期产出——雇员福利的主要部门——部门——补贴

employee_id	department_id
1	1
2	1
3	3
4	3

我的解决办法丝毫没有结果。

它为什么不发挥作用?

不存在任何无效的价值观,当我自己管理分局时,它就显示雇员——即部门——不应参与最后产出。

select employee_id, department_id 
from Employee
WHERE (employee_id, department_id) not in(
    select employee_id, department_id 
    from (
        SELECT employee_id, department_id, primary_flag,
            count(employee_id) over (partition by employee_id) as cnt
        from employee
    ) a 
    WHERE cnt > 1 and primary_flag =  n 
);

从下文的Joel Coehoorn评论中,他能够得出同样的结果;然而,在LeetCode,他并不为我工作。

Answer 1

www.un.org/Depts/DGACM/index_spanish.htm 你的问询过于复杂。这根本不需要任何分局或窗口的职能。

你们需要的是某种有条件的汇总。

SELECT
  e.employee_id,
  CASE WHEN COUNT(*) = 1
       THEN MIN(e.department_id)
       ELSE MIN(CASE WHEN e.primary_flag =  Y  THEN e.department_id END)
  END AS department_id
FROM Employee e
GROUP BY
  e.employee_id;

db<>fiddle

Your existing query has multiple issues:

Because the values might have a null, MySQL evaluates the NOT IN clause differently. You can see this in action in this fiddle.
Generally you should avoid NOT IN, it can always be rewritten as a NOT EXISTS.
Row comparators don t work in all DBMSs.

In any case you could have simplified out and removed the self-join.

select employee_id, department_id 
from (
    SELECT employee_id, department_id, primary_flag,
        count(employee_id) over (partition by employee_id) as cnt
    from Employee
) a 
WHERE cnt = 1 or primary_flag =  y ;

Obviously it s not necessary to use window functions anyway, as I mentioned earlier.

Answer 2

我认为,最好使用<代码>row_ number()而不是(雇员在初级=Y,然后是零时按个案顺序排列)作为“空白”/代码,然后按照@JoelCoehoorn的建议,只保留其结果为1的行(,其中空白=1)。

如果你想用一个子座标,请见

select employee_id, department_id 
from Employee
WHERE primary_flag= Y  
  or employee_id not in(select employee_id from Employee where primary_flag= Y )

产出

employee_id	department_id
1	1
2	1
3	3
4	3

Answer 3

There are two cases regarding the primary_flag for a given employee_id:
1, when count(department_id) =1 , then that department_id is the primary department regardless the primary_flag always being N . To conclude, the focus is the count(department_id) =1
2, when count(department_id) >1 , then the department_id whose primary_flag is Y is the primary department . In short, the vital part is primary_flag is Y .

由于这两个案件是相互排斥的,因此有可能使用<条码>UNION。此外, s子更直截了当:

select * from
    (select employee_id, max(department_id)
    from employee
    group by employee_id
    having count(*) =1
    UNION
    select employee_id, department_id
    from employee
    where primary_flag= Y 
    ) t
order by employee_id
;
-- result:
+-------------+--------------------+
| employee_id | max(department_id) |
+-------------+--------------------+
|           1 | 1                  |
|           2 | 1                  |
|           3 | 3                  |
|           4 | 3                  |
+-------------+--------------------+

注:将<代码>UNION报表列入一个衍生表的原因是UNION 报表不执行。页: 1

Answer 4

I joint Employee and Department jointly

CREATE TABLE [Employee] 
(
    [employee_id]   INT,
    [department_id] VARCHAR(512),
    [primary_flag]  VARCHAR(512)
);

INSERT INTO [Employee] ([employee_id], [department_id], [primary_flag]) VALUES
    ( 1 ,  1 ,  N ),
    ( 2 ,  1 ,  Y ),
    ( 2 ,  2 ,  N ),
    ( 3 ,  3 ,  N ),
    ( 4 ,  2 ,  N ),
    ( 4 ,  3 ,  Y ),
    ( 4 ,  4 ,  N );

Select * from Employee

select employee_id, department_id 
from Employee
WHERE cast(employee_id as varchar(10)) +  |  + cast(department_id as varchar(10)) not in(
    select cast(employee_id as varchar(10)) +  |  + cast(department_id as varchar(10))  
    from (
        SELECT employee_id, department_id, primary_flag,
            count(employee_id) over (partition by employee_id) as cnt
        from employee
    ) a 
    WHERE cnt > 1 and primary_flag =  n 
);

https://dbfiddle.uk/JU3euYv-"rel=“nofollow noreferer”>Fiddle

employee_id	department_id
1	1
2	1
3	3
4	3

友情链接