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

Откуда:
Сообщений: 3
Доброго дня.
Никак не могу решить задачку по SQL.
Люди Добрые, пните в какую сторону смотреть. Где я неправ?
Скоро уже умом тронусь :(
Задача
Для каждого производителя подсчитать:
сколько имеется в наличии его продуктов (любого типа) с неуникальной для этого производителя ценой
и количество таких неуникальных цен.
Вывод: производитель, количество продуктов, количество цен.

Первый вариант.
with AllProducts as --Все продукты с ценами---------------------------------------------------
	(
	select p.*, t.code, t.price from dbo.Product p inner join dbo.PC t on p.model = t.model
	union all
	select p.*, t.code, t.price from dbo.Product p inner join dbo.Printer t on p.model = t.model
	union all
	select p.*, t.code, t.price from dbo.Product p inner join dbo.Laptop t on p.model = t.model
	)
	, CountNonUniqProducts as --Количество продуктов с не уникальной ценой-------------------------
	(
	select 
		case -- Когда присутствует вторая цена => Продукт с такой ценой не уникальный - считаем все что есть
			when row_number()over(partition by al_out.maker, al_out.price order by al_out.maker, al_out.price) = 2 
			then (select count(al_in.price) from AllProducts al_in where al_in.maker=al_out.maker and al_in.price=al_out.price)
		end 'CountNonUniq'
		, al_out.* 
	from AllProducts al_out
	)
select 
	cnt.maker
	,isnull(sum(cnt.CountNonUniq),0) 'CountModels'
	,count(cnt.CountNonUniq)'CountPrice'
from 
	CountNonUniqProducts cnt
group by
	cnt.maker	
;
--Ваш запрос вернул правильные данные на основной базе, но не прошел тест на проверочной базе.
--* Неверное число записей (меньше на 6)

Второй вариант
With CountNonUniqProducts as --Количество продуктов с не уникальной ценой
	(
	select		
		al.maker, al.price, count(*) 'NonUniq'
	from		
		(
		select p.*, t.code, t.price from dbo.Product p inner join dbo.PC t on p.model = t.model
		union all
		select p.*, t.code, t.price from dbo.Product p inner join dbo.Printer t on p.model = t.model
		union all
		select p.*, t.code, t.price from dbo.Product p inner join dbo.Laptop t on p.model = t.model
		) al
	group by
		al.maker, al.price
	having	
		count(*)>1
	)
select distinct 
	p.maker
	,( 
	select isnull(sum(cnt.NonUniq),0) from CountNonUniqProducts cnt where cnt.maker=p.maker
	) 'CountModels'
	,(
	select count(cnt.NonUniq) from CountNonUniqProducts cnt where cnt.maker=p.maker
	) 'CountPrice'
from dbo.Product p

--Ваш запрос вернул правильные данные на основной базе, но не прошел тест на проверочной базе.
--* Несовпадение данных (4)


Структура БД
Картинка с другого сайта.
Схема БД состоит из четырех таблиц:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, price, screen)
Printer(code, model, color, type, price)
Таблица Product представляет производителя (maker), номер модели (model) и тип ('PC' - ПК, 'Laptop' - ПК-блокнот или 'Printer' - принтер). Предполагается, что номера моделей в таблице Product уникальны для всех производителей и типов продуктов. В таблице PC для каждого ПК, однозначно определяемого уникальным кодом – code, указаны модель – model (внешний ключ к таблице Product), скорость - speed (процессора в мегагерцах), объем памяти - ram (в мегабайтах), размер диска - hd (в гигабайтах), скорость считывающего устройства - cd (например, '4x') и цена - price. Таблица Laptop аналогична таблице РС за исключением того, что вместо скорости CD содержит размер экрана -screen (в дюймах). В таблице Printer для каждой модели принтера указывается, является ли он цветным - color ('y', если цветной), тип принтера - type (лазерный – 'Laser', струйный – 'Jet' или матричный – 'Matrix') и цена - price.

8 дек 16, 14:17    [19980475]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с неверным результатом или моей логикой  [new]
buven
Member

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

На эту задачу наверно есть подсказки? в FAQ вообще следов нет?
8 дек 16, 14:33    [19980558]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с неверным результатом или моей логикой  [new]
buven
Member

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

Кстати говоря, на сколько я помню, на SQL-ex можно написать в поддержку. Ребята быстро отвечают и хорошо направляют мысль, не давая конечного ответа.
8 дек 16, 14:45    [19980636]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с неверным результатом или моей логикой  [new]
waszkiewicz
Member

Откуда:
Сообщений: 1089
Gulash,
правила sql-ex.ru уже прочитаны?
8 дек 16, 14:45    [19980638]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с неверным результатом или моей логикой  [new]
Владислав Колосов
Member

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

читайте справку по GROUP BY и HAVING.
8 дек 16, 14:45    [19980639]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с неверным результатом или моей логикой  [new]
Gulash
Member

Откуда:
Сообщений: 3
waszkiewicz
Gulash,
правила sql-ex.ru уже прочитаны?

Мне НЕ нужен сертификат. Я решаю для себя и НЕ прошу решить за меня.

buven
Gulash,
...Ребята быстро отвечают и хорошо направляют мысль, не давая конечного ответа.

Владислав Колосов
Gulash,
читайте справку по GROUP BY и HAVING.

Направление понял. Пошел читать. :) Спасибо.

PS
Если все-таки решу, то решение выкладывать не буду, чтобы не нарываться на праведный гнев Модераторов.
Посему тему прошу закрыть
8 дек 16, 16:49    [19981330]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с неверным результатом или моей логикой  [new]
Gulash
Member

Откуда:
Сообщений: 3
Владислав Колосов,
Спасибо Вам.

Я задачку решил. Заплакал над своей тупостью. Ушел покупать книгу "SQL для чайников".
8 дек 16, 18:31    [19981735]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с неверным результатом или моей логикой  [new]
Chris_Gabler
Member

Откуда:
Сообщений: 2
Не могу найти принципиальное отличие своего запроса от Варианта№2, но несовпадение данных уже не в 4-х, а в 2-х записях...
В чем подвох?

with view1 as
(select maker, price, product.model from product inner join PC on product.model = pc.model
union all
select maker, price, product.model from product inner join laptop on laptop.model = product.model
union all
select maker, price, product.model from product inner join printer on printer.model = product.model), 

view2 as
(select maker, price, count(model) as mo from view1 group by maker, price having count(model)>1), 

view3 as
(select maker, count(price) as pr from view2 group by maker),

view4 as
(select maker, sum(mo) as m from view2 group by maker), 

view5 as
(select distinct maker from product)

select view5.maker, coalesce(m,0), coalesce(pr,0) from view5 left join view4 on view4.maker = view5.maker left join view3 on view3.maker = view5.maker
30 янв 17, 23:56    [20164092]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с неверным результатом или моей логикой  [new]
Chris_Gabler
Member

Откуда:
Сообщений: 2
Ок, я поняла..
31 янв 17, 00:52    [20164218]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить