Как выбрать минимальное значение из таблицы в запросе?

Question

[ellz]

Есть такая процедура:

CREATE PROCEDURE [dbo].[GetToursList](	
   @toCity nvarchar(30) 
   , @toCountry nvarchar(30) 
   ,  @departureDate date
   , @amountNights int)
AS
BEGIN
SELECT 
hotels.Name as HotelName
,cities.Name as CityName
,countrys.Name as CounrtyName
,(select min((select hotelsRooms.price from hotelsRooms where hotelsRooms.HotelID = hotels.Id))) as MinPrice// здесь мучаюсь
,hotels.Rating as Stars
,hotels.Line as Line
,hotels.DistanceToAirport as DistToAirport
,hotels.DistanceToBeach as DistTobeach
FROM dbo.Tours 
INNER JOIN dbo.Countrys as countrys
ON Tours.CountryID = countrys.Id
INNER JOIN dbo.Cities as cities
ON Tours.CityID = cities.Id
INNER JOIN dbo.Hotels as hotels 
ON Tours.HotelID = hotels.Id
INNER JOIN dbo.HotelsRooms as hotelsRooms
ON hotelsRooms.HotelID = hotels.Id
WHERE
(@toCity is null or(Tours.CityID =       (select cities.Id   from cities   where   cities.Name = @toCity)))
or 
(@toCountry is null or(Tours.CountryID = (select countrys.Id from countrys where countrys.Name = @toCountry)))
END

В hotelRooms к каждому отелю присвоены свои комнаты со своими данными.

Например

id hotelID roomTypeID Price Count
431 14 1 4500 10
432 14 2 5625 10
433 14 3 5400 10
434 14 4 6165 10
435 14 5 6525 10
436 15 1 5600 10
437 15 2 7000 10
438 15 3 6720 10
439 15 4 7672 10
440 15 5 8120 10
441 16 1 5700 10
442 16 2 7125 10
443 16 3 6840 10
444 16 4 7809 10

В поле MinPrice процедура должна вернуть минимальную цену для номера. Например для отеля с ID 14 - это будет 4500.
Процедура не обновляется -

(69,1): SQL72014: .Net SqlClient Data Provider: Msg 130, Level 15, State 1, Procedure GetToursList, Line 12 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
(58,0): SQL72045: Script execution error. The executed script:

line 12 - это то самое место где я мучаюсь.

Или в одном запросе такие вещи не пишутся? Может целесообразние разделить на разные процедуры?

Answer 1

[Константин Цветков]

(SELECT MIN( hotelsRooms.price) FROM hotelsRooms WHERE hotelsRooms.HotelID = hotels.Id ) as MinPrice

Структура

В вопросе

[Tours]
    [HotelID]         INT            NOT NULL
    [CityID]          INT            NOT NULL,
    [CountryID]       INT            NOT NULL,

Гостиница может быть без города, город — без страны? Нет. Нужно удалить поля город и страна, а использовать связанные таблицы.

Запрос

Предлагаю вариант запроса с учётом исправления структуры:

SELECT Hotels.[Name] AS HotelName, 
       Cities.[Name] AS CityName, 
       Countrys.[Name] AS CounrtyName,
       (SELECT MIN(Price)
          FROM HotelsRooms
          WHERE (HotelID = Hotels.ID)) AS MinPrice, 
        Hotels.Rating AS Stars, 
        Hotels.Line, 
        Hotels.DistanceToBeach, 
        Hotels.DistanceToAirport
  FROM Hotels 
    INNER JOIN Cities ON Hotels.City = Cities.ID 
    INNER JOIN Countrys ON Cities.CountryID = Countrys.ID
  WHERE ((@toCity IS NULL) OR (Cities.[Name] = @toCity)) 
    AND ((@toCountry IS NULL) OR (Countrys.[Name] = @toCountry))

Дополнительно

Установите SSMS и используйте конструктор запросов. Например, сделать подзапрос

SELECT MIN(Price) FROM HotelsRooms WHERE (HotelID = 14)

Потом перенести его в

SELECT        (SELECT        MIN(Price) AS Expr1
                          FROM            HotelsRooms
                          WHERE        (HotelID = 14)) AS Expr1
FROM            Hotels
WHERE        (Id = 14)

Связать таблицы

SELECT        (SELECT        MIN(Price) AS Expr1
                          FROM            HotelsRooms
                          WHERE        (HotelID = Hotels.Id)) AS MinPrice
FROM            Hotels
WHERE        (Id = 14)

Comments

[ellz]

Убрал из Tours CityID и CountryID. В вашем варианте, к сожалению, выборка не работает. Подумал, что проблема где-то в передачи данных, но нет.

CREATE PROCEDURE [dbo].[GetToursList](	
   @toCity nvarchar(30) 
   , @toCountry nvarchar(30) 
   ,  @departureDate date
   , @amountNights int)
AS
BEGIN
SET @toCity = N'Сиде'
SET @toCountry = N'Турция'
SELECT Hotels.[Name] AS HotelName, 
       Cities.[Name] AS CityName, 
       Countrys.[Name] AS CounrtyName,
       (SELECT MIN(Price)
          FROM HotelsRooms
          WHERE (HotelID = Hotels.ID)) AS MinPrice, 
        Hotels.Rating AS Stars, 
        Hotels.Line, 
        Hotels.DistanceToBeach, 
        Hotels.DistanceToAirport
  FROM Hotels 
    INNER JOIN Cities ON Hotels.City = Cities.ID 
    INNER JOIN Countrys ON Cities.CountryID = Countrys.ID
  WHERE (NOT (@toCity IS NULL) OR Cities.[Name] = @toCity) 
    AND (NOT (@toCountry IS NULL) OR Countrys.[Name] = @toCountry)
END

выборка вообще не работает. Выдает по всем странам и по всем городам.

[Константин Цветков]

ellz, Исправил.

[Константин Цветков]

ellz, Поэтому нужны тестовые данные: скрипт с INSERT или CVS.

[ellz]

Константин Цветков, вы об этом CVS?

[ellz]

Константин Цветков, Спасибо.

[Константин Цветков]

ellz, Ой. CSV для bulk copy program.

[ellz]

Константин Цветков , мне кажется, что лучше не создавать новую тему для следующего вопроса. Спрошу здесь.
ответы от базы данных я сереализую через JsonConvert(от Newtonsoft) и отправлю клиенту ответ таким образом:

var response = Querys.GetTours(value.toCity, value.toCountry, value.departureDate, value.amountNights);
return JsonConvert.SerializeObject(response);

Quuerys.GetTours возвращает объект типа DataTable.
Мне нужен запрос который будет выводить данные об отеле, в том числе о комнатах в нем.
В json это должно выглядеть так:

{
	"hotelName": "someName",
	"someInfo": "someInfo",
	"someInfo2": "someInfo",
	"someInfo3": "someInfo",
        // как вот это сделать не понимаю:
	"rooms": [{ 
			"type": 1,
			"price": 2312
		},
		{
			"type": 2,
			"price": 5652
		},
		{
			"type": 3,
			"price": 5312
		}
	]
}

Я не представляю в dateTable вместить данные об комнате, чтобы сами данные отеля не повторялись с каждой запись о комнате. Нет идей даже как гуглить такое. С dataTable такое проделать наверное невозможно?

[Константин Цветков]

ellz, Я не знаю тонкость работы с JSON, но при получении данных можно отдельно получать информационную часть, отдельно — табличную, то есть два запроса. Либо самостоятельно разделить табличные данные на постоянную и переменную часть.
А так же у хранимой процедуры есть параметры OUT, то есть выходные. Тут надо смотреть: может ли ваш промежуточный слой их обрабатывать.

[ellz]

Константин Цветков,

отдельно — табличную, то есть два запроса

- вы имеете ввиду http запросы или запросы внутри процедуры?

есть параметры OUT, то есть выходные

, вы намекаете на то, что часть данных из процедуры можно вернуть в переменную C# и потом в вставить в объект перед сеарилизацией?

может ли ваш промежуточный слой их обрабатывать.

промежуточный слой - это API?

[Константин Цветков]

ellz, Процедура:

CREATE PROCEDURE [dbo].[SayOops] @Say VARCHAR(100) OUT 
AS
BEGIN
  SET @Say = 'Ой! ' + @Say
  EXECUTE sp_databases
  RETURN @@TIMETICKS
END

И что от неё можно получить:

DECLARE	@return_value int, @Say varchar(100)
SELECT	@Say = N'Проверка.'

EXECUTE	@return_value = [dbo].[SayOops]	@Say = @Say OUTPUT

SELECT	@Say as N'@Say'
SELECT	'Return Value' = @return_value

Answer 2

[d-stream]

1.
MinPrice = (select min((select hotelsRooms.price from hotelsRooms where hotelsRooms.HotelI ….

гораздо читабельнее чем длинющий бла-бла-бла as MinPrice

2. вложенные селекты чаще всего - зло, хотя конечно умный планировщик с ними справляется...

3. аналогично с сабселектами в условиях

4. стоит открыть для себя:
а) временные таблицы in-memory
б) cte

4.а:

declare @minmaxprices table (hotel_id int, min_price decimal, max_price decimal)
insert into @minmaxprices 
select hotel_id, min(price), max(price) from hotelrooms group by hotel_id 


select 
…
[самая низкая цена] = minmaxprices.minprice
…
from hotels 
…
left join @minmaxprices as minmaxprices on minmaxprices.hotel_id=hotels.hotel_id

4.б:

;with cte_min_max_prices(hotel_id, minprice, maxprice)
as ( select hotel_id, min(price), max(price) from hotelrooms group by hotel_id )
select 
…
[самая низкая цена в отеле] = cte_min_max_prices.minprice
...
from hotels
….
left join cte_min_max_prices on cte_min_max_prices.hotel_id=hotels.hotel_id