Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите с запросом  [new]
rsolanov
Member

Откуда:
Сообщений: 930
Здравствуйте!
Не могу понять как написать запрос.
Есть четыре таблицы:
1. Компании (UT_Companies);
2. Физические лица (UT_Contacts);
3. Аккаунты (UT_Accounts);
4. "Подписанты" (UT_AuthorisedContacts)-это таблица-связка, указывающая по какому аккаунту компании в отчете кто будет его подписывать (определяет информацию в области подписей отчета).

Структура таблиц представлена во вложении. Смысл полей можно догадаться по наименованию.
Соответственно у одной компании может быть несколько аккаунтов, как и физических лиц, у аккаунта на одну дату может быть несколько "подписантов".

Необходимо написать запрос, который вернет все компании (id_Company), у всех аккаунтов которых есть подписанты, общие для всех аккаунтов. Например, если у компании "К1" есть три аккаунта "А1", "А2" и "А3", у аккаунта "А1" на дату "Д1" подписанты "П1", "П2", у "А2": "П1" и "П2", а у "А3" подписанты "П1" и "П3". Такая компания не должна быть в результатах запроса, поскольку хоть у нее и есть один общий на все аккаунты подписант "П1", а есть и разрозненные: это "П2" и "П3".

К сообщению приложен файл. Размер - 0Kb
6 окт 09, 13:44    [7747886]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
автор
Структура таблиц представлена во вложении.


А переводить ее в DDL и тестовые данные самим придумывать?! Вроде не первый день на форуме...
6 окт 09, 13:48    [7747919]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
rsolanov
Member

Откуда:
Сообщений: 930
pkarklin
автор
Структура таблиц представлена во вложении.


А переводить ее в DDL и тестовые данные самим придумывать?! Вроде не первый день на форуме...

Прошу прощения, вот:
1. UT_Companies
id_CompanyFullName
1К1
2К2
3К3
4К4

2. UT_Accounts
id_Accountid_Company Account
11A1
21A2
31A3
42A4
52A5
62A6
73А7
83А8
93А9
103А10
114А11

3. UT_Contacts
id_Contactid_Company F
11П1
21П2
31П3
42П4
52П5
63П6
73П7
84П8

4. UT_AuthorisedContacts
id_Accountid_Contact
11
12
21
22
31
33
44
45
54
55
64
65
76
87
97
106
118

В набор должны попасть компании К2 и К4
6 окт 09, 14:20    [7748152]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
rsolanov
Member

Откуда:
Сообщений: 930
Можно создать представления следующего содержания:
1. UT_Companies
SELECT 1 AS id_Company, 'K1' AS FullName
UNION
SELECT 2, 'К2'
UNION
SELECT 3, 'К3'
UNION
SELECT 4, 'К4'
2. UT_Accounts
SELECT 1 AS id_Account, 1 AS id_Company, 'A1' AS Account
UNION
SELECT 2, 1, 'A1'
UNION
SELECT 3, 1, 'A3'
UNION
SELECT 4, 2, 'A4'
UNION
SELECT 5, 2, 'A5'
UNION
SELECT 6, 2, 'A6'
UNION
SELECT 7, 3, 'A7'
UNION
SELECT 8, 3, 'A8'
UNION
SELECT 9, 3, 'A9'
UNION
SELECT 10, 3, 'A10'
UNION
SELECT 11, 4, 'A11'
3. UT_Contacts
SELECT 1 AS id_Contact, 1 AS id_Company, 'П1' AS F
UNION
SELECT 2, 1, 'П2'
UNION
SELECT 3, 1, 'П3'
UNION
SELECT 4, 2, 'П4'
UNION
SELECT 5, 2, 'П5'
UNION
SELECT 6, 3, 'П6'
UNION
SELECT 7, 3, 'П7'
UNION
SELECT 8, 4, 'П8'
4. UT_AuthorisedContacts
SELECT 1 AS id_Account, 1 AS id_Contact
UNION
SELECT 1, 2
UNION
SELECT 2, 1
UNION
SELECT 2, 2
UNION
SELECT 3, 1
UNION
SELECT 3, 3
UNION
SELECT 4, 4
UNION
SELECT 4, 5
UNION
SELECT 5, 4
UNION
SELECT 5, 5
UNION
SELECT 6, 4
UNION
SELECT 6, 5
UNION
SELECT 7, 6
UNION
SELECT 8, 7
UNION
SELECT 9, 7
UNION
SELECT 10, 6
UNION
SELECT 11, 8

Вроде ничего не перепутал
6 окт 09, 14:39    [7748316]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Добрый Э - Эх
Guest
Реляционное деление? Классическая задача ресторанного меню: найти все блюда, для изготовления которых используются одинаковые компоненты.
Я правильно понял задачу?
7 окт 09, 05:25    [7751044]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
rsolanov
Member

Откуда:
Сообщений: 930
Добрый Э - Эх
Реляционное деление? Классическая задача ресторанного меню: найти все блюда, для изготовления которых используются одинаковые компоненты.
Я правильно понял задачу?

Да, похоже на правду. Я просто еще не сталкивался с подобной задачей, а решение в голову так и не приходит. Вы уж пожалуйста помогите, если знаете...
7 окт 09, 09:14    [7751324]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
В эту тему я не вникал (может, попозже).
Но в теме Как реализовать операцию реляционной алгебры "деление" на SQL?
мы, помню, как-то обсуждали реляционное деление.
7 окт 09, 09:30    [7751379]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
rsolanov
Member

Откуда:
Сообщений: 930
Что-то я не до понимаю. В моем случае что на что делить? Пока читаю статьи по реляционному делению.
7 окт 09, 09:58    [7751522]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
rsolanov
Member

Откуда:
Сообщений: 930
Друзья, все пока не пойму что на что надо делить, если кто с подобными задачами сталкивался, пожалуйста, наведите на мысль
7 окт 09, 14:32    [7753688]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
vino
Member

Откуда:
Сообщений: 1191
rsolanov, думаю, достаточно так
SELECT * FROM [test].[dbo].[UT_Companies] f WHERE  not exists
	(SELECT * FROM
		(SELECT count(distinct t.[id_Account]) as Cnt
			FROM [test].[dbo].[UT_Accounts] s INNER JOIN
				[test].[dbo].[UT_AuthorisedContacts] t ON t.[id_Account] = s.[id_Account] and s.[id_Company] = f.[id_Company]
			GROUP BY s.[id_Company], t.[id_Contact]
		) maxc
		WHERE maxc.cnt
			<> (SELECT count(distinct d.[id_Account]) as cnt
				FROM [test].[dbo].[UT_AuthorisedContacts] d INNER JOIN
				[test].[dbo].[UT_Accounts] a ON a.[id_Account] = d.[id_Account] and a.[id_Company] = f.[id_Company]
			)
	)
исключаем компании, в которых встретится количество участия какого-нибудь контакта < чем количество авторизованных аккаунтов (которое, правда, не проверили на 0) Ну, дальше справишся
7 окт 09, 14:32    [7753690]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
vino
Member

Откуда:
Сообщений: 1191
кстати, лучше без лишних группировок
SELECT * FROM [test].[dbo].[UT_Companies] f WHERE  not exists
	(SELECT * FROM
		(SELECT count(distinct t.[id_Account]) as Cnt
			FROM [test].[dbo].[UT_Accounts] s INNER JOIN
				[test].[dbo].[UT_AuthorisedContacts] t ON t.[id_Account] = s.[id_Account] and s.[id_Company] = f.[id_Company]
			GROUP BY t.[id_Contact]
		) maxc
		WHERE maxc.cnt
			<> (SELECT count(distinct d.[id_Account]) as cnt
				FROM [test].[dbo].[UT_AuthorisedContacts] d INNER JOIN
				[test].[dbo].[UT_Accounts] a ON a.[id_Account] = d.[id_Account] and a.[id_Company] = f.[id_Company]
			)
	)
7 окт 09, 14:34    [7753707]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
rsolanov
Member

Откуда:
Сообщений: 930
vino, протестировал я этот запрос и надо сказать что он работает!
Разобрался я с этим запросом, понял логику его выполнения и как говорится, все гениальное-это простое. Но почему-то такой запрос ко мне в голову не пришел :-)
Как я понял, здесь реализована операция деления, описанная тут и этот запрос и запрос статьи повторяют одну структуру:
SELECT DISTINCT X.A
FROM X
WHERE NOT EXISTS(SELECT YY.B FROM Y YY EXCEPT SELECT XX.B FROM X XX WHERE XX.A=X.A)

Огромное Вам спасибо!
7 окт 09, 17:23    [7754834]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
vino
Member

Откуда:
Сообщений: 1191
rsolanov, нет, деление тут не применялось - это обычное количественное сравнение
7 окт 09, 18:41    [7755299]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить