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

Откуда: Москва
Сообщений: 5381
Добрый вечер. Что-то торможу и никак не могу сообразить как сортировку сделать требуемую.
Скрипт с тестовыми данными:
+
use tempdb
go

if object_id('Servitm', 'U') is not null
	drop table Servitm

if object_id('Doctor', 'U') is not null
	drop table Doctor

if object_id('Speciality', 'U') is not null
	drop table Speciality
go


-- таблица специальностей врачей
create table Speciality(
	idSpeciality int not null,
	SpecName varchar(40) not null,
	constraint PK_Speciality primary key(idSpeciality)
)

-- таблица врачей

create table Doctor(
	idDoctor int not null,
	idSpeciality int not null,
	SurName varchar(40) not null,
	constraint PK_Doctor primary key(idDoctor),
	constraint FK_Doctor_Speciality foreign key (idSpeciality) references Speciality(idSpeciality)
)

-- таблиц услуг
create table Servitm(
	idServitm int not null,
	idDoctor int not null,
	Price money not null,
	constraint PK_Servitm primary key(idServitm),
	constraint FK_Servitm_Doctor foreign key (idDoctor) references Doctor(idDoctor)
)
go

-- тестовые данные
insert into Speciality(idSpeciality, SpecName)
select 1, 'ТЕРАПЕВТ' union all
select 2, 'ПЕДИАТР' union all
select 3, 'ХИРУРГ'

insert into Doctor(idDoctor, idSpeciality, SurName)
select 1, 1, 'ИВАНОВ' union all
select 2, 2, 'ПЕТРОВ' union all
select 3, 3, 'СИДОРОВ' union all
select 4, 1, 'КУЗНЕЦОВ' union all
select 5, 2, 'ВЛАСОВ' union all
select 6, 1, 'ПОТАНИН' union all
select 7, 1, 'АБРАМОВИЧ' union all
select 8, 2, 'КАЛИНИН'

insert into Servitm(idServitm, idDoctor, Price)
select 1, 1, 50.0 union all
select 2, 1, 10.0 union all
select 3, 5, 100.0 union all
select 4, 2, 44.0 union all
select 5, 3, 33.0 union all
select 6, 4, 22.0 union all
select 7, 2, 25.0 union all
select 8, 6, 67.0 union all
select 9, 5, 50.0 union all
select 10, 5, 80.0 union all
select 11, 7, 90.0 union all
select 12, 4, 34.0 union all
select 13, 2, 77.0 union all
select 14, 3, 127.0
go

-- запрос
select
	Doctor.idSpeciality, 
	0 as idDoctor, 
	SpecName as Name, 
	sum(Price) as summ
from Servitm
	join Doctor on Servitm.idDoctor = Doctor.idDoctor
	join Speciality on Doctor.idSpeciality = Speciality.idSpeciality
group by Doctor.idSpeciality, SpecName
union all
select
	Doctor.idSpeciality, 
	Doctor.idDoctor, '    >' + SurName, 
	sum(Price)
from Servitm
	join Doctor on Servitm.idDoctor = Doctor.idDoctor
group by Doctor.idSpeciality, Doctor.idDoctor, SurName
order by 1, 2
Требуется сортировка сначала по названию специальности, потом по имени врача. Т.е. следующим образом
2	0	ПЕДИАТР	376,00
2	5	    >ВЛАСОВ	230,00
2	2	    >ПЕТРОВ	146,00
1	0	ТЕРАПЕВТ	273,00
1	7	    >АБРАМОВИЧ	90,00
1	1	    >ИВАНОВ	60,00
1	4	    >КУЗНЕЦОВ	56,00
1	6	    >ПОТАНИН	67,00
3	0	ХИРУРГ	160,00
3	3	    >СИДОРОВ	160,00
20 окт 09, 16:24    [7812879]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по сортировке  [new]
Anddros
Member

Откуда:
Сообщений: 1077
order by Doctor.idSpeciality, sign(idDoctor), SpecName
20 окт 09, 16:28    [7812916]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по сортировке  [new]
iljy
Member

Откуда:
Сообщений: 8711
Senya_L,
select * from
(
	select
		Doctor.idSpeciality, 
		0 as idDoctor, 
		SpecName as Name, 
		sum(Price) as summ
	from Servitm
		join Doctor on Servitm.idDoctor = Doctor.idDoctor
		join Speciality on Doctor.idSpeciality = Speciality.idSpeciality
	group by Doctor.idSpeciality, SpecName
	union all
	select
		Doctor.idSpeciality, 
		Doctor.idDoctor, '    >' + SurName, 
		sum(Price)
	from Servitm
		join Doctor on Servitm.idDoctor = Doctor.idDoctor
	group by Doctor.idSpeciality, Doctor.idDoctor, SurName
)t
order by 1, case idDoctor when 0 then '' else Name end

Anddros,
не сработает, idDoctor - вычисляемая в спсике выбоки колонка:) Надо сначала в подзапрос собрать.
20 окт 09, 16:29    [7812927]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по сортировке  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Ой, я туплю
Сам догадался *YAHOO*
-- запрос
select
	SpecName as SpecName, 
	'' as DoctorName,
	sum(Price) as summ
from Servitm
	join Doctor on Servitm.idDoctor = Doctor.idDoctor
	join Speciality on Doctor.idSpeciality = Speciality.idSpeciality
group by Doctor.idSpeciality, SpecName
union all
select
	Speciality.SpecName,
	'    >' + SurName, 
	sum(Price)
from Servitm
	join Doctor on Servitm.idDoctor = Doctor.idDoctor
	join Speciality on Doctor.idSpeciality = Speciality.idSpeciality
group by Doctor.idSpeciality, Doctor.idDoctor, Speciality.SpecName, Doctor.SurName
order by 1, 2
Всем спасибо.
20 окт 09, 16:32    [7812944]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по сортировке  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
Можно попробовать без юнионов:
select 
  f.idSpeciality
 ,f.idDoctor
 ,sname=case when n.i=1 then specname else dName end
 ,scost=case when n.i=1 then ssum else cost end
from(
  select
	  d.idSpeciality
         ,s.specname
         ,d.idDoctor
         ,dName='    >' + SurName
         ,cost=sum(Price)
         ,r=row_number()over(partition by d.idSpeciality order by SurName)
         ,ssum=sum(sum(Price))over(partition by d.idSpeciality)
  from servitm r
  join doctor d on r.idDoctor = d.idDoctor
  join speciality s on d.idSpeciality = s.idSpeciality
  group by d.idSpeciality, s.specname, d.idDoctor, SurName
) f
join (select i=1 union all select 2) n on n.i=1 and f.r=1 or n.i=2
order by specname,dName
+
idSpeciality
idDoctorsnamescost
25ПЕДИАТР376,00
25 >ВЛАСОВ230,00
22 >ПЕТРОВ146,00
17ТЕРАПЕВТ273,00
17 >АБРАМОВИЧ90,00
11 >ИВАНОВ60,00
14 >КУЗНЕЦОВ56,00
16 >ПОТАНИН67,00
33ХИРУРГ160,00
33 >СИДОРОВ160,00
20 окт 09, 16:55    [7813129]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по сортировке  [new]
iljy
Member

Откуда:
Сообщений: 8711
Ozzy-Osbourne,
тогда уж так:)
  select
	  d.idSpeciality
         ,dName=isnull('    >' + SurName, specname)
         ,cost=sum(Price)
  from servitm r
  join doctor d on r.idDoctor = d.idDoctor
  join speciality s on d.idSpeciality = s.idSpeciality
  group by d.idSpeciality, s.specname, d.idDoctor, SurName with rollup
having grouping(s.specname) = 0 and grouping(d.idDoctor) = grouping(SurName)
order by s.specname, SurName
20 окт 09, 17:04    [7813213]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по сортировке  [new]
Anddros
Member

Откуда:
Сообщений: 1077
iljy
Anddros,
не сработает, idDoctor - вычисляемая в спсике выбоки колонка:) Надо сначала в подзапрос собрать.

Вах, зачем сразу 'не сработает'?

select * from
(
...
)Doctor
order by Doctor.idSpeciality, sign(idDoctor), SpecName



Ну а по сути если: не вчитывался я в запрос особо. К тому же вопрошающие делятся на две категории: одним надо все разжевать и в рот положить, другим вполне достаточно подкинуть маааленькую идейку... :)

+ off. 2 iljy

Зря забросили решать. На третьем этапе полно вкусностей. Главное - не подавиться ими. :) Например, одну такую 'вкусность' я пять дней прожевать не мог. Чистых часов 30-40 убил на нее. Попадаются вкусности и на втором этапе, но редко и не такие вкусные. И до львиной доли из них Вы просто не добрались.

Если без аллегорий: даже самые простые задачи третьего этапа не сравнимы по сложности с самыми запутанными случаями, поднимаемыми здесь... Подумайте над этим. :)
20 окт 09, 17:07    [7813236]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по сортировке  [new]
iljy
Member

Откуда:
Сообщений: 8711
Anddros
iljy
Anddros,
не сработает, idDoctor - вычисляемая в спсике выбоки колонка:) Надо сначала в подзапрос собрать.

Вах, зачем сразу 'не сработает'?

select * from
(
...
)Doctor
order by Doctor.idSpeciality, sign(idDoctor), SpecName

Ну я ровно это и имел ввиду под "собрать в подзапрос"
+ off. 2 iljy

Anddros

Зря забросили решать. На третьем этапе полно вкусностей. Главное - не подавиться ими. :) Например, одну такую 'вкусность' я пять дней прожевать не мог. Чистых часов 30-40 убил на нее. Попадаются вкусности и на втором этапе, но редко и не такие вкусные. И до львиной доли из них Вы просто не добрались.

Если без аллегорий: даже самые простые задачи третьего этапа не сравнимы по сложности с самыми запутанными случаями, поднимаемыми здесь... Подумайте над этим. :)

Да я не забросил, со временем туго Хочется же рейтинг! :D а когда каждые 10 минут отвлекают - общее время решения получается грустное ;(
20 окт 09, 17:21    [7813328]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по сортировке  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
iljy
Ozzy-Osbourne,
тогда уж так:)
...having grouping(s.specname) = 0 and grouping(d.idDoctor) = grouping(SurName)...
вах... красиво! надо бы поизучать эту функцию, даже не подозревал о ней :-)
20 окт 09, 17:21    [7813330]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по сортировке  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Пока оставил свой с UNION'ом, потому как это я понимаю. :) Надо тоже поучить GROUPING и ROLLUP.

Спасибо за красивые варианты решения.
20 окт 09, 17:23    [7813354]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по сортировке  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
off. 2iljy
+
iljy
Хочется же рейтинг! :D а когда каждые 10 минут отвлекают - общее время решения получается грустное ;(
там в первых строчках можно оказаться, совсем не заботясь о времени: посмотрите на показатели некоторых ТОП-10 :-)
20 окт 09, 17:25    [7813376]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по сортировке  [new]
iljy
Member

Откуда:
Сообщений: 8711
off. 2iljy
+
Ozzy-Osbourne
там в первых строчках можно оказаться, совсем не заботясь о времени: посмотрите на показатели некоторых ТОП-10 :-)

убедили
20 окт 09, 18:02    [7813663]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить