288
Приложение C
WHEN 'Head Teller' THEN 'Operations'
WHEN 'Teller' THEN 'Operations'
ELSE 'Unknown'
END
FROM employee;
SELECT emp_id,
CASE
WHEN title LIKE '%President' OR title = 'Loan Manager'
OR title = 'Treasurer'
THEN 'Management'
WHEN title LIKE '%Teller' OR title = 'Operations Manager'
THEN 'Operations'
ELSE 'Unknown'
END
FROM employee;
11.2
Перепишите
следующий запрос так, чтобы результирующий набор со
держал всего одну строку и четыре столбца (по
одному для каждого от
деления). Назовите столбцы
branch_1
,
branch_2
и т. д.
mysql>
_SUM(CASE_WHEN_open_branch_id_=_1_THEN_1_ELSE_0_END)_branch_1,_>_SUM(CASE_WHEN_open_branch_id_=_2_THEN_1_ELSE_0_END)_branch_2'>SELECT_open_branch_id,_COUNT(*)__>_FROM_account__>_GROUP_BY_open_branch_id;'>SELECT open_branch_id, COUNT(*)
> FROM account
> GROUP BY open_branch_id;
+++
| open_branch_id | COUNT(*) |
+++
| 1 | 8 |
| 2 | 7 |
| 3 | 3 |
| 4 | 6 |
+++
4 rows in set (0.00 sec)
mysql>
SELECT
>
SUM(CASE WHEN open_branch_id = 1 THEN 1 ELSE 0 END) branch_1,
>
SUM(CASE WHEN open_branch_id = 2 THEN 1 ELSE 0 END) branch_2,
>
SUM(CASE WHEN open_branch_id = 3 THEN 1 ELSE 0 END) branch_3,
>
SUM(CASE WHEN open_branch_id = 4 THEN 1 ELSE 0 END) branch_4
>
FROM account;
+++++
| branch_1 | branch_2 | branch_3 | branch_4 |
+++++
| 8 | 7 | 3 | 6 |
+++++
1 row in set (0.02 sec)
