Подскажите, как получить в одной текстовой колонке, пересечение всех вариантов отношения многие ко многим? Если полей может быть несколько тысяч, а форматов несколько десятков. Пересечений для каждого поля думаю не более 50. Оптимизация запроса желательна по времени. Использоваться будет в клиенте, для получения и отображения сообщения об ошибке. В хранимой процедуре, как я понимаю, без цикла не обойтись?
/*Нужно получить такой результат */
format format_name pole1 mon dd yyyy hh:miAM '001. 003'2 mm/dd/yyyy '002'3 yyyy.mm.dd'001. 002'4 dd/mm/yyyy'002. 003'5 yyyy.mm.dd '001'6 dd-mm-yyyy '002'7 dd mon yyyy '001'8 mon dd yyyy '002'9 hh:mm:ss '003 004'10 mon dd yyyy hh:mi:ss:mmmAM (or PM) '004'11 mm-dd-yyyy '004'12 yyyy/mm/dd '003. 004'13 yyyymmdd'001. 003. 004'
Небольшое количество исходных данных в T-SQL формате ниже.
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.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
/* ============================================================ */
/* DBMS name: Sybase AS Enterprise 12.5.1 */
/* Created on: 21.05.2014 15:54 */
/* ============================================================ */
create table pole
(
pole int not null,
pole_name varchar(100) null ,
constraint pk_pole primary key (pole)
)
go
create table format
(
format int not null,
format_name varchar(100) null ,
constraint pk_format primary key (format)
)
go
create table link
(
link int not null,
pole int null ,
format int null ,
constraint pk_link primary key (link)
)
go
create index link_format on link (format)
go
create index link_pole on link (pole)
go
alter table link
add constraint fk_link_link_form_format foreign key (format)
references format (format)
go
alter table link
add constraint fk_link_link_pole_pole foreign key (pole)
references pole (pole)
go
commit
go
/* Данные */
if not exists (select 1 from pole where pole = 1) INSERT INTO pole (pole, pole_name) VALUES (1,'Поле_1') ELSE UPDATE pole SET pole_name = 'Поле_1' WHERE pole = 1
if not exists (select 1 from pole where pole = 2) INSERT INTO pole (pole, pole_name) VALUES (2,'Поле_2') ELSE UPDATE pole SET pole_name = 'Поле_2' WHERE pole = 2
if not exists (select 1 from pole where pole = 3) INSERT INTO pole (pole, pole_name) VALUES (3,'Поле_3') ELSE UPDATE pole SET pole_name = 'Поле_3' WHERE pole = 3
if not exists (select 1 from pole where pole = 4) INSERT INTO pole (pole, pole_name) VALUES (4,'Поле_4') ELSE UPDATE pole SET pole_name = 'Поле_4' WHERE pole = 4
if not exists (select 1 from pole where pole = 5) INSERT INTO pole (pole, pole_name) VALUES (5,'Поле_5') ELSE UPDATE pole SET pole_name = 'Поле_5' WHERE pole = 5
if not exists (select 1 from pole where pole = 6) INSERT INTO pole (pole, pole_name) VALUES (6,'Поле_6') ELSE UPDATE pole SET pole_name = 'Поле_6' WHERE pole = 6
if not exists (select 1 from format where format = 1) INSERT INTO format (format, format_name) VALUES (1,'mon dd yyyy hh:miAM') ELSE UPDATE format SET format_name = 'mon dd yyyy hh:miAM' WHERE format = 1
if not exists (select 1 from format where format = 2) INSERT INTO format (format, format_name) VALUES (2,'mm/dd/yyyy') ELSE UPDATE format SET format_name = 'mm/dd/yyyy' WHERE format = 2
if not exists (select 1 from format where format = 3) INSERT INTO format (format, format_name) VALUES (3,'yyyy.mm.dd') ELSE UPDATE format SET format_name = 'yyyy.mm.dd' WHERE format = 3
if not exists (select 1 from format where format = 4) INSERT INTO format (format, format_name) VALUES (4,'dd/mm/yyyy') ELSE UPDATE format SET format_name = 'dd/mm/yyyy' WHERE format = 4
if not exists (select 1 from format where format = 5) INSERT INTO format (format, format_name) VALUES (5,'yyyy.mm.dd') ELSE UPDATE format SET format_name = 'yyyy.mm.dd' WHERE format = 5
if not exists (select 1 from format where format = 6) INSERT INTO format (format, format_name) VALUES (6,'dd-mm-yyyy') ELSE UPDATE format SET format_name = 'dd-mm-yyyy' WHERE format = 6
if not exists (select 1 from format where format = 7) INSERT INTO format (format, format_name) VALUES (7,'dd mon yyyy') ELSE UPDATE format SET format_name = 'dd mon yyyy' WHERE format = 7
if not exists (select 1 from format where format = 8) INSERT INTO format (format, format_name) VALUES (8,'mon dd, yyyy') ELSE UPDATE format SET format_name = 'mon dd, yyyy' WHERE format = 8
if not exists (select 1 from format where format = 9) INSERT INTO format (format, format_name) VALUES (9,'hh:mm:ss') ELSE UPDATE format SET format_name = 'hh:mm:ss' WHERE format = 9
if not exists (select 1 from format where format = 10) INSERT INTO format (format, format_name) VALUES (10,'mon dd yyyy hh:mi:ss:mmmAM (or PM)') ELSE UPDATE format SET format_name = 'mon dd yyyy hh:mi:ss:mmmAM (or PM)' WHERE format = 10
if not exists (select 1 from format where format = 11) INSERT INTO format (format, format_name) VALUES (11,'mm-dd-yyyy') ELSE UPDATE format SET format_name = 'mm-dd-yyyy' WHERE format = 11
if not exists (select 1 from format where format = 12) INSERT INTO format (format, format_name) VALUES (12,'yyyy/mm/dd') ELSE UPDATE format SET format_name = 'yyyy/mm/dd' WHERE format = 12
if not exists (select 1 from format where format = 13) INSERT INTO format (format, format_name) VALUES (13,'yyyymmdd') ELSE UPDATE format SET format_name = 'yyyymmdd' WHERE format = 13
if not exists (select 1 from link where link = 1) INSERT INTO link (link, format, pole) VALUES (1,1,1) ELSE UPDATE link SET format = 1, pole = 1 WHERE link = 1
if not exists (select 1 from link where link = 2) INSERT INTO link (link, format, pole) VALUES (2,3,1) ELSE UPDATE link SET format = 3, pole = 1 WHERE link = 2
if not exists (select 1 from link where link = 3) INSERT INTO link (link, format, pole) VALUES (3,5,1) ELSE UPDATE link SET format = 5, pole = 1 WHERE link = 3
if not exists (select 1 from link where link = 4) INSERT INTO link (link, format, pole) VALUES (4,7,1) ELSE UPDATE link SET format = 7, pole = 1 WHERE link = 4
if not exists (select 1 from link where link = 5) INSERT INTO link (link, format, pole) VALUES (5,13,1) ELSE UPDATE link SET format = 13, pole = 1 WHERE link = 5
if not exists (select 1 from link where link = 6) INSERT INTO link (link, format, pole) VALUES (6,2,2) ELSE UPDATE link SET format = 2, pole = 2 WHERE link = 6
if not exists (select 1 from link where link = 7) INSERT INTO link (link, format, pole) VALUES (7,3,2) ELSE UPDATE link SET format = 3, pole = 2 WHERE link = 7
if not exists (select 1 from link where link = 8) INSERT INTO link (link, format, pole) VALUES (8,4,2) ELSE UPDATE link SET format = 4, pole = 2 WHERE link = 8
if not exists (select 1 from link where link = 9) INSERT INTO link (link, format, pole) VALUES (9,6,2) ELSE UPDATE link SET format = 6, pole = 2 WHERE link = 9
if not exists (select 1 from link where link = 10) INSERT INTO link (link, format, pole) VALUES (10,8,2) ELSE UPDATE link SET format = 8, pole = 2 WHERE link = 10
if not exists (select 1 from link where link = 11) INSERT INTO link (link, format, pole) VALUES (11,1,3) ELSE UPDATE link SET format = 1, pole = 3 WHERE link = 11
if not exists (select 1 from link where link = 12) INSERT INTO link (link, format, pole) VALUES (12,4,3) ELSE UPDATE link SET format = 4, pole = 3 WHERE link = 12
if not exists (select 1 from link where link = 13) INSERT INTO link (link, format, pole) VALUES (13,9,3) ELSE UPDATE link SET format = 9, pole = 3 WHERE link = 13
if not exists (select 1 from link where link = 14) INSERT INTO link (link, format, pole) VALUES (14,12,3) ELSE UPDATE link SET format = 12, pole = 3 WHERE link = 14
if not exists (select 1 from link where link = 15) INSERT INTO link (link, format, pole) VALUES (15,13,3) ELSE UPDATE link SET format = 13, pole = 3 WHERE link = 15
if not exists (select 1 from link where link = 16) INSERT INTO link (link, format, pole) VALUES (16,9,4) ELSE UPDATE link SET format = 9, pole = 4 WHERE link = 16
if not exists (select 1 from link where link = 17) INSERT INTO link (link, format, pole) VALUES (17,10,4) ELSE UPDATE link SET format = 10, pole = 4 WHERE link = 17
if not exists (select 1 from link where link = 18) INSERT INTO link (link, format, pole) VALUES (18,11,4) ELSE UPDATE link SET format = 11, pole = 4 WHERE link = 18
if not exists (select 1 from link where link = 19) INSERT INTO link (link, format, pole) VALUES (19,12,4) ELSE UPDATE link SET format = 12, pole = 4 WHERE link = 19
if not exists (select 1 from link where link = 20) INSERT INTO link (link, format, pole) VALUES (20,13,4) ELSE UPDATE link SET format = 13, pole = 4 WHERE link = 20
commit
go
Приз: - Хранимая процедура, которая получает данные из любой таблички в T-SQL формате, для синхронизации данных в разных базах, (может это вам и не нужно, но делюсь тем что имею. Скрипт выше создан ей) |
