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

Откуда:
Сообщений: 151
Найти самый популярный город по общему объему пассажиропотока.
Вывести количество рейсов в/из города, количество прилетевших и улетевших пассажиров

Написал вот такой запрос:

SELECT  city as 'город', 
		(select COUNT(id_vzlet) from marshrut where id_punkta = id_vzlet)  as 'исходящие рейсы',
		(select COUNT(id_posadka) from marshrut where id_punkta = id_posadka) as 'входящие рейсы',
		pri as 'прилетевшие', 
		ul as 'улетевшие'
from (punkt left join ulet_ on punkt.id_punkta = ulet_.id_vzlet)
		left join prilet on punkt.id_punkta = prilet.id_posadka
group by city


Create view prilet as 
SELECT id_posadka, sum(reg_pass) as 'pri'
FROM marshrut m, reis r
WHERE m.id_marsh = r.id_marsh
group by id_posadka

Create view ulet_ as 
SELECT id_vzlet, sum(reg_pass) as 'ul'
FROM marshrut m, reis r
WHERE m.id_marsh = r.id_marsh
group by id_vzlet


хочу сгруппировать group by city, но выдает ошибку
Столбец "punkt.id_punkta" недопустим в списке выбора, поскольку он не содержится ни в агрегатной функции, ни в предложении GROUP BY.

К сообщению приложен файл. Размер - 46Kb
29 май 12, 15:52    [12632567]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с группировкой в запросе  [new]
RubinDm
Member

Откуда:
Сообщений: 461
kim-kong,

create view Takeoff as
-- Это взлеты
select P.city, R.id_reisa, R.reg_pass
from punkt P
join marshrut M on M.id_vzlet = P.id_punkta
join reis R on R.id_marsh = M.id_marsh
go


create view TakeoffStats as
-- Это ТОЛЬКО ВЗЛЕТНАЯ статистика по городам
select T.city,
  -- кол-во взлетов из city
  TakeoffCount = count(T.id_reisa),
  -- кол-во пассажиров взлетевших из city
  TakeoffPassengerCount = sum(T.reg_pass)
from Takeoff T
group by T.city
go


create view Landing as
-- Это посадки
select P.city, R.id_reisa, R.reg_pass
from punkt P
join marshrut M on M.id_posadka = P.id_punkta
join reis R on R.id_marsh = M.id_marsh
go


create view LandingStats as
-- Это ТОЛЬКО посадочная статистика по городам
select L.city,
  -- кол-во посадок в city
  LandingCount = count(L.id_reisa),
 
  -- кол-во пассажиров севших в city
  LandingPassengerCount = sum(L.reg_pass)
  
from Landing L
group by L.city
go


create view AllStats as
-- Это ВСЯ статистика по городам
select city = P.city
, TakeoffCount          = isnull(TS.TakeoffCount, 0)
, TakeoffPassengerCount = isnull(TS.TakeoffPassengerCount, 0)
, LandingCount          = isnull(LS.LandingCount, 0)
, LandingPassengerCount = isnull(LS.LandingPassengerCount, 0)
, PassengerCount = isnull(LS.LandingPassengerCount, 0) + isnull(TS.TakeoffPassengerCount, 0)
, PassengerDelta = isnull(LS.LandingPassengerCount, 0) - isnull(TS.TakeoffPassengerCount, 0)
from punkt P
left join TakeoffStats TS on TS.city = P.city
left join LandingStats LS on LS.city = P.city
go
29 май 12, 18:41    [12633736]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с группировкой в запросе  [new]
AHDP
Member

Откуда:
Сообщений: 1226
Select Top 1 p.City, Sum(rs.rin), Sum(rs.rout), Sum(rs.pin), Sum(rs.pout)
From

(
Select r.Id_vzlet id_punkta, 1 ROut, r.reg_pass POut, 0 RIn, 0 PIn
From Reis r
Join Marshrut m On m.id_marsh = r.id_marsh

Union all

Select Id_Posadka punkt, 0 ROut, 0 POut, 1 RIn, r.reg_pass PIn
From Reis r
Join Marshrut m On m.id_marsh = r.id_marsh
) rs
Join Punkt p on p.id_punkta = rs.id_punkta
Group by p.City
Order by rs.pin + rs.pout
29 май 12, 18:47    [12633764]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с группировкой в запросе  [new]
kim-kong
Member

Откуда:
Сообщений: 151
RubinDm,
большое спасибо, только в Allstats distinct надо написать, а то повторяющиеся города выведет.
29 май 12, 19:04    [12633840]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с группировкой в запросе  [new]
RubinDm
Member

Откуда:
Сообщений: 461
kim-kong
RubinDm,
большое спасибо, только в Allstats distinct надо написать, а то повторяющиеся города выведет.


Согласен. Но distinct - это "синтаксический сахар", который делает группировку по результатам вычисления всех выражений из select'а. Т.е. если вы пишите там distinct, то сервер будет интерпретировать это так:
select city = P.city
, TakeoffCount          = isnull(TS.TakeoffCount, 0)
, TakeoffPassengerCount = isnull(TS.TakeoffPassengerCount, 0)
, LandingCount          = isnull(LS.LandingCount, 0)
, LandingPassengerCount = isnull(LS.LandingPassengerCount, 0)
, PassengerCount = isnull(LS.LandingPassengerCount, 0) + isnull(TS.TakeoffPassengerCount, 0)
, PassengerDelta = isnull(LS.LandingPassengerCount, 0) - isnull(TS.TakeoffPassengerCount, 0)
from punkt P
left join TakeoffStats TS on TS.city = P.city
left join LandingStats LS on LS.city = P.city
group by
  P.city
, isnull(TS.TakeoffCount, 0)
, isnull(TS.TakeoffPassengerCount, 0)
, isnull(LS.LandingCount, 0)
, isnull(LS.LandingPassengerCount, 0)
, isnull(LS.LandingPassengerCount, 0) + isnull(TS.TakeoffPassengerCount, 0)
, isnull(LS.LandingPassengerCount, 0) - isnull(TS.TakeoffPassengerCount, 0)
На практике такой distinct может очень дорого стоить.

Поэтому лучше сделать так:
select city = P.city
, TakeoffCount          = isnull(TS.TakeoffCount, 0)
, TakeoffPassengerCount = isnull(TS.TakeoffPassengerCount, 0)
, LandingCount          = isnull(LS.LandingCount, 0)
, LandingPassengerCount = isnull(LS.LandingPassengerCount, 0)
, PassengerCount = isnull(LS.LandingPassengerCount, 0) + isnull(TS.TakeoffPassengerCount, 0)
, PassengerDelta = isnull(LS.LandingPassengerCount, 0) - isnull(TS.TakeoffPassengerCount, 0)
from
( select distinct city from punkt ) P -- дубликаты будут убраны
left join TakeoffStats TS on TS.city = P.city -- для каждого city тут гарантировано не больше одной записи
left join LandingStats LS on LS.city = P.city -- для каждого city тут гарантировано не больше одной записи

В данном случае distinct будет применен всего к одному полю, а не к пачке выражений.
29 май 12, 19:35    [12633975]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить