mysql - 当 Hive 中列出的任何一个主题失败或缺席时，查询以获取最终状态为失败或缺席

Question

 select * from college;
    btech   cse     dbms    fail
    btech   cse     java    absent
    btech   cse     unix    pass
    btech   ece     edc     pass
    btech   ece     power   pass
    btech   ece     embeded pass
    mba     marketing       mlaw    pass
    mba     marketing       mtheory fail
    mba     hr      hrtheory1        pass
    mba     hr      hrtheory2        absent

输出应该是这样的:-

btech cse fail
btech ece pass
mba  marketing fail
mba hr hrtheory absent

Answer 1

我假设您的最后结果是 mba hr 缺席，并且我为您的专栏添加了一些名称。尝试以下操作，可能对您有帮助；)

SQL Fiddle

MySQL 5.6 架构:

CREATE TABLE college
    (`name` varchar(5), `major` varchar(9), `class` varchar(9), `result` varchar(6))
;

INSERT INTO college
    (`name`, `major`, `class`, `result`)
VALUES
    ('btech', 'cse', 'dbms', 'fail'),
    ('btech', 'cse', 'java', 'absent'),
    ('btech', 'cse', 'unix', 'pass'),
    ('btech', 'ece', 'edc', 'pass'),
    ('btech', 'ece', 'power', 'pass'),
    ('btech', 'ece', 'embeded', 'pass'),
    ('mba', 'marketing', 'mlaw', 'pass'),
    ('mba', 'marketing', 'mtheory', 'fail'),
    ('mba', 'hr', 'hrtheory1', 'pass'),
    ('mba', 'hr', 'hrtheory2', 'absent')
;

查询 1:

select
 name, major,
 case 
 when find_in_set('fail', group_concat(result)) > 0 then 'fail'
 when find_in_set('fail', group_concat(result)) = 0
      and find_in_set('absent', group_concat(result)) = 0
      and find_in_set('pass', group_concat(result)) > 0 then 'pass'
 when find_in_set('fail', group_concat(result)) = 0
      and find_in_set('absent', group_concat(result)) > 0
      and find_in_set('pass', group_concat(result)) > 0 then 'absent' end as result
from college
group by name, major

<强> Results :

|  name |     major | result |
|-------|-----------|--------|
| btech |       cse |   fail |
| btech |       ece |   pass |
|   mba |        hr | absent |
|   mba | marketing |   fail |

在 Hive 中，可能会喜欢这个；)

select
 name, major,
 case 
 when ARRAY_CONTAINS('fail', collect_set(result)) then 'fail'
 when ARRAY_CONTAINS('fail', collect_set(result))
      and ARRAY_CONTAINS('absent', collect_set(result))
      and ARRAY_CONTAINS('pass', collect_set(result)) then 'pass'
 when ARRAY_CONTAINS('fail', collect_set(result))
      and ARRAY_CONTAINS('absent', collect_set(result))
      and ARRAY_CONTAINS('pass', collect_set(result)) then 'absent' end as result
from college
group by name, major