uVbftJVe

1SELECT sm1.id, sm1.name, sm1.inn, sm1.sro_id, sm2.id, sm2.name, sm2.inn, sm2.sro_id
2FROM sro_members sm1
3	JOIN sro_members sm2
4		ON sm1.sro_id = sm2.sro_id AND sm1.inn = sm2.inn AND sm1.id <> sm2.id
5ORDER BY sm1.id, sm1.inn, sm1.sro_id, sm1.name;
6
7SELECT sm1.id, count(sm2.id)
8FROM sro_members sm1
9	JOIN sro_members sm2
10		ON sm1.sro_id = sm2.sro_id AND sm1.inn = sm2.inn AND sm1.id <> sm2.id
11	GROUP BY sm2.id
12ORDER BY sm1.id, sm1.name;
13
14
15SELECT name, inn, sro_id, count(inn) as c1, count(sro_id) as c2
16FROM sro_members
17	GROUP BY inn, sro_id, name HAVING count(inn) > 1 AND count(sro_id) > 1
18ORDER BY c1 asc, c2 asc
19;
20
21
22
23
24
25SELECT sm1.name, sm1.inn, sm1.sro_id
26FROM sro_members as sm1
27WHERE inn IN (
28	SELECT inn
29  FROM sro_members as sm2
30  GROUP BY inn HAVING count(inn) > 1
31)/* AND sro_id IN (
32	SELECT sro_id
33	FROM sro_members as sm2
34	GROUP BY sro_id HAVING count(sro_id) > 1
35)*/
36ORDER BY sm1.inn, sm1.sro_id
37;
38
39SELECT sm1.name, sm1.inn, sm1.sro_id
40FROM sro_members as sm1
41WHERE ;
42
43
44
45
46--
47-- Изменения в таблице `sro`.`auction_winner`
48--
49ALTER TABLE auction_winner
50  ADD COLUMN IF NOT EXISTS manager_id INTEGER NULL;
51
52CREATE UNIQUE INDEX IF NOT EXISTS unique_inn_sro_id_sro_members
53	on sro_members (sro_id, inn)
54;
55
56-- Надо немного денормализовать базу - добавить auction_winner.manager_id, и заполнять его с помощью триггеров во время добавления контракта, удаления компании и добавления/изменения/удаления менеджера из компании.
57
58--
59-- Функция и триггер, обновляющие manager_id из таблицы `sro_member_manager`
60--
61CREATE OR REPLACE FUNCTION add_manager_id_in_auction_winner()
62  RETURNS TRIGGER AS $add_manager_id_in_auction_winner$
63
64	DECLARE
65	  manager_id          integer;
66
67	BEGIN
68
69	  SELECT smm.user_id INTO manager_id
70		FROM sro_member_manager as smm
71		  JOIN sro_members as sm
72			ON sm.id = smm.sro_member_id
73		  JOIN auction_winner as aw
74			ON aw.inn = sm.inn AND aw.sro_id = sm.sro_id
75		WHERE sm.sro_id = NEW.sro_id AND sm.inn = NEW.inn
76		;
77
78	  IF (manager_id IS NOT NULL) THEN
79			-- обновляем `auction_winner`.`manager_id`.
80			NEW.manager_id := manager_id;
81	  END IF;
82
83		RETURN NEW;
84
85	END;
86
87$add_manager_id_in_auction_winner$ LANGUAGE plpgsql;
88
89CREATE TRIGGER insert_into_contract
90	AFTER INSERT ON auction_winner
91  FOR EACH ROW EXECUTE PROCEDURE add_manager_id_in_auction_winner();
92
93
94
95
96
97
98--
99-- Удаляем сделанные изменения
100--
101ALTER TABLE auction_winner
102	DROP COLUMN IF EXISTS manager_id;
103
104DROP INDEX IF EXISTS unique_inn_sro_id_sro_members;
105
106DROP FUNCTION IF EXISTS add_manager_id_in_auction_winner();
107
108DROP TRIGGER IF EXISTS insert_into_contract ON auction_winner;
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
126INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
127INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
128INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
129SELECT * FROM sales_summary_bytime;
130DELETE FROM sales_fact WHERE product_key = 1;
131SELECT * FROM sales_summary_bytime;
132UPDATE sales_fact SET units_sold = units_sold * 2;
133SELECT * FROM sales_summary_bytime;