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

Откуда:
Сообщений: 531
Есть исходная таблица о человеке, вида
ID | FatherID | MotherID | Name
где FatherID или MotherID могут быть NULL

данные для таблицы (пример)
1 - NULL - NULL - '1'
2 - 1 - NULL - '2'
3 - NULL - NULL - '3'
4 - 1 - 3 - '4'
5 - 2 - 3 - '5'
6 - 1 - 3 - '6'
7 - 5 - 6 - '7'
8 - 7 - 3 - '8'
Ну то есть мы заполнили всех родственников с их связями )

Вопрос: найти человека с наибольшим количеством партнеров.
Ну то есть по примеру, это получится ID = 3, ибо у нее партнеры 1,2,7

У меня есть еще вопросы, с другими вопросами по сей таблице, но спрошу если уж с ними не разберусь
С этим проломал голову полдня, никакого просвета.
Спасибо.
7 июн 11, 00:57    [10773983]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос хелп  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3755
tual
Есть исходная таблица о человеке, вида
ID | FatherID | MotherID | Name
где FatherID или MotherID могут быть NULL

данные для таблицы (пример)
1 - NULL - NULL - '1'
2 - 1 - NULL - '2'
3 - NULL - NULL - '3'
4 - 1 - 3 - '4'
5 - 2 - 3 - '5'
6 - 1 - 3 - '6'
7 - 5 - 6 - '7'
8 - 7 - 3 - '8'
Ну то есть мы заполнили всех родственников с их связями )

Вопрос: найти человека с наибольшим количеством партнеров.
Ну то есть по примеру, это получится ID = 3, ибо у нее партнеры 1,2,7

У меня есть еще вопросы, с другими вопросами по сей таблице, но спрошу если уж с ними не разберусь
С этим проломал голову полдня, никакого просвета.
Спасибо.

select top 1 MID, cnt from
(
select FatherID as MID, count(MotherID) as cnt from table
union
MotherID, count (FatherID) from table
) grp
order by cnt desc
7 июн 11, 01:09    [10774011]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос хелп  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3755
ну group by дописать забыл....
7 июн 11, 01:14    [10774034]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос хелп  [new]
tual
Member

Откуда:
Сообщений: 531
Большое спасибо, я его приукрасил для полноты результата :)

select top 1 MID as ID, cnt as RelCount, zz.Name from
	(
		select ID_FATHER as MID, count(ID_MOTHER) as cnt from Person 
		where ID_FATHER is not null and ID_MOTHER is not null
		group by ID_FATHER
		union
		select ID_MOTHER, count(ID_FATHER) from Person 
		where ID_FATHER is not null and ID_MOTHER is not null
		group by ID_MOTHER
	) grp
	join Person zz on zz.ID = MID
	order by cnt desc 

И вроде тоже думал делать через union, пробовал, но я далеко не силен в TSQL

Вообщем, у меня еще есть вопросы, по другим пунктам, но спрошу если сам не осилю
7 июн 11, 01:19    [10774052]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос хелп  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3755
автор
where ID_FATHER is not null and ID_MOTHER is not null

это не нужно совсем.... каунт нулы не считает
7 июн 11, 09:55    [10774642]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос хелп  [new]
korneyr
Member

Откуда:
Сообщений: 6
tual,

Мне кажется, вы забыли про маленькую деталь DISTINCT в COUNT-ах,
без них у "3" - 4 RelCount (насколько я понял вы считаете уникальных партнеров,
а у вас на выходе может просто получиться многодетная пара)
declare @Person Table(ID int,ID_FATHER int, ID_MOTHER int,Name varchar(20))
insert @Person values (1,NULL,NULL,'1'),
(2,1,NULL,'2'),
(3,NULL,NULL,'3'),
(4,1,3,'4'),
(5,2,3,'5'),
(6,1,3,'6'),
(7,5,6,'7'),
(8,7,3,'8')

select top 1 MID as ID, cnt as RelCount, zz.Name from
	(
		select ID_FATHER as MID, count(DISTINCT ID_MOTHER) as cnt from @Person 
		where ID_FATHER is not null and ID_MOTHER is not null
		group by ID_FATHER
		union
		select ID_MOTHER, count(DISTINCT ID_FATHER) from @Person 
		where ID_FATHER is not null and ID_MOTHER is not null
		group by ID_MOTHER
	) grp
	join @Person zz on zz.ID = MID
	order by cnt desc 

7 июн 11, 10:23    [10774788]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос хелп  [new]
tual
Member

Откуда:
Сообщений: 531
Я не то чтобы забыл :)
Я про него помнил, потом забыл, потом перед сном вспомнил, потом опять забыл, и вот после напоминания сейчас впишу :)
7 июн 11, 10:39    [10774891]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос хелп  [new]
korneyr
Member

Откуда:
Сообщений: 6
Ivan Durak,

Ага, вы конечно правы
Добавил немного данных чтобы было понятна проблема с DISTINCT (точнее без него)
declare @Person Table(ID int,ID_FATHER int, ID_MOTHER int,Name varchar(20))
insert @Person values (1,NULL,NULL,'1'),
(2,1,NULL,'2'),
(3,NULL,NULL,'3'),
(4,1,3,'4'),
(5,2,3,'5'),
(6,1,3,'6'),
(7,5,6,'7'),
(8,5,3,'8'),
(9,5,6,'9'),
(10,5,6,'10'),
(11,5,6,'11'),
(12,5,6,'12')

select top 1 MID as ID, cnt as RelCount, zz.Name from
	(
		select ID_FATHER as MID, count(DISTINCT ID_MOTHER) as cnt from @Person 
		group by ID_FATHER
		union
		select ID_MOTHER, count(DISTINCT ID_FATHER) from @Person 
		group by ID_MOTHER
	) grp
	join @Person zz on zz.ID = MID
	order by cnt desc 

7 июн 11, 10:41    [10774908]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос хелп  [new]
korneyr
Member

Откуда:
Сообщений: 6
tual,

Это вы молодец, перед сном лучше про все забывать, что бы спалось лучше:)
7 июн 11, 10:45    [10774936]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос хелп  [new]
tual
Member

Откуда:
Сообщений: 531
Ну что ж, продолжу, ибо нифига в голову не лезет
В исходной таблице добавляется еще колонка BirthDate - дата рождения
А задание состоит в том, чтобы найти двух (братьев/сестер/сводных), то есть у них или общий отец, или общая мать.
У этих двоих, должна быть максимальная разница в возрасте.

Максимум до чего додумался, это выбрать детей с одинаковым родителем (и чтоб детей было не меньше 2)

select ID, BirthDate, id_mother as ParentID from Person p
				where id_mother is not null and id_mother in
				(
					select ID_MOTHER from Person p2
					group by ID_MOTHER
					having count(*) > 1
				);

Аналогичный скрипт для id_father
7 июн 11, 14:41    [10776987]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос хелп  [new]
tual
Member

Откуда:
Сообщений: 531
Может это можно хранимкой написать ?
Хотя бы так, если одним мега-запросом чересчур умно выйдет
7 июн 11, 15:53    [10777479]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос хелп  [new]
tual
Member

Откуда:
Сообщений: 531
Как насчет такой процедуры ?
На моем примере вроде ищет возраст (я походу обдурил и вас, и себя). Надо найти не самих родственников, а максимальную разницу возраста между братьями/сестрами

CREATE PROCEDURE dbo.MaxAgeGap
AS
BEGIN		
	with T
	as
	(
		select ID, ParentID, BirthDate from
		(
			select ID, BirthDate, id_mother as ParentID from Person p
				where id_mother is not null and id_mother in
				(
					select ID_MOTHER from Person p2
					group by ID_MOTHER
					having count(*) > 1
				)
			UNION
			select ID, BirthDate, id_father as ParentID from Person p
				where id_father is not null and id_father in
				(
					select id_father from Person p2
					group by id_father
					having count(*) > 1
				)
		) z
	)

	select top 1 ParentID, max(datediff(yyyy, minDate, maxDate)) as YearsDiff from
	(
		select ParentID, min(BirthDate) as minDate, max(BirthDate) as maxDate from T
		group by ParentID
	) z2
	group by ParentID
	order by YearsDiff desc
END
GO
7 июн 11, 17:12    [10778103]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос хелп  [new]
tual
Member

Откуда:
Сообщений: 531
правда пришлось еще добавить еще одну запись 9-2-6-'9' для наглядности проверки
7 июн 11, 17:14    [10778130]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос хелп  [new]
tual
Member

Откуда:
Сообщений: 531
Подскажите что не так...
Исходные данные я приводил, мне надо найти всех потомков указанного человека
Процедура:

with DescendantsReport(ID)
	as
	(
		SELECT ID
		FROM Person
		WHERE ID_Mother = @ID
UNION
		SELECT ID
		FROM Person
		WHERE ID_Father = @ID
UNION ALL
		SELECT Person.ID
		FROM DescendantsReport, Person
		WHERE Person.ID_Mother = DescendantsReport.ID
UNION ALL
		SELECT Person.ID
		FROM DescendantsReport, Person
		WHERE Person.ID_Father = DescendantsReport.ID
	)

	Select distinct dr.ID, p.Name, coalesce(p.ID_Father,-1) as FatherID, coalesce(p.ID_Mother,-1) as MotherID
	From DescendantsReport dr
	join Person p on dr.ID = p.ID

Ерунда в том, что мне приходится ставить distinct ибо появляются повторяющиеся строки
ID = 7/8/9

а почему ? может исходные данные кривые ? не соблюдается иерархия ?
7 июн 11, 19:45    [10778901]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос хелп  [new]
tual
Member

Откуда:
Сообщений: 531
Отбой... и правда исходные данные представляли собой неправильную структуру
Написал данные по-новой - все ок
7 июн 11, 19:58    [10778924]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить