|
Помогите, пожалуйста, с созданием представления (view)
#38394179
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
|
про100,
...
ещё осторожнее (test-case)Из представленных Вами тестовых данных нельзя получить результат "правильного" скриншота. Для примера, служащие с ид = 16, 17, 18, - несоответствие должностей и управлений.
А в общем не понято, почему имея рабочей представление, не получается создать новое по его образу и подобию...
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
mysql> create view v_info as select emp.ordinal id,
-> emp.fio fio,
-> p.position position,
-> g.governance governance,
-> d.department department
-> from employees emp
-> left join positions p on p.ordinal = emp.positionid
-> left join governances g on g.ordinal = emp.governanceid
-> left join departments d on d.ordinal = emp.departmentid;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from v_info;
+-----+---------------------------------+--------------------------------+--------------+--------------+
| id | fio | position | governance | department |
+-----+---------------------------------+--------------------------------+--------------+--------------+
{skipped}
| 16 | Алексахина Ирина Вячеславовна | Операционист | Управление1 | NULL |
| 17 | Алексеев Сергей Дмитриевич | Менеджер по работе с клиентами | Управление1 | NULL |
| 18 | Алехина Елена Александровна | Страховой агент | Управление1 | NULL |
{skipped}
100 rows in set (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_test22 |
+------------------+
| committees |
| departments |
| employees |
| governances |
| governments |
| positions |
| v_info |
+------------------+
7 rows in set (0.00 sec)
mysql> show create table v_info\G
*************************** 1. row ***************************
View: v_info
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_info` AS select `emp`.`ordinal` AS `id`,`emp`.`fio` AS `fio`,`p
`.`position` AS `position`,`g`.`governance` AS `governance`,`d`.`department` AS `department` from (((`employees` `emp` left join `positions` `p` on((`p`.`ordinal` = `em
p`.`positionid`))) left join `governances` `g` on((`g`.`ordinal` = `emp`.`governanceid`))) left join `departments` `d` on((`d`.`ordinal` = `emp`.`departmentid`)))
character_set_client: cp1251
collation_connection: cp1251_general_ci
1 row in set (0.00 sec)
mysql> desc v_info;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id | int(5) | NO | | 0 | |
| fio | char(100) | NO | | NULL | |
| position | varchar(50) | YES | | NULL | |
| governance | varchar(255) | YES | | NULL | |
| department | char(255) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.05 sec)
mysql> show global variables like 'version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| version | 5.5.28 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86 |
| version_compile_os | Win32 |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
create view v_info2 as select
e.ordinal,
e.fio,
p.position,
g.governance,
d1.department department_emp,
d2.department department_gov,
c.committee,
gm1.government government_gov,
gm2.government government_dep_emp,
gm3.government government_dep_gov
from employees e
left join positions p on p.ordinal = e.positionid
left join governances g on g.ordinal = e.governanceid
left join departments d1 on d1.ordinal = e.departmentid
left join departments d2 on d2.ordinal = g.departmentid
left join committees c on c.ordinal = g.committeeid
left join governments gm1 on gm1.ordinal = g.governmentid
left join governments gm2 on gm2.ordinal = d1.governmentid
left join governments gm3 on gm3.ordinal = d2.governmentid;
|
|
|
