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

Откуда: SPb
Сообщений: 65
Добрый вечер коллеги.

Есть некая табличка в которой хранятся дефектные записи.
Table (defectCode nchar(10), prettyName nchar(50),treatmentPrice numeric(18,2))

Необходимо сделать группировку записей по кодам дефектов, что-то вроде этого.

SELECT 
	    vlr.defectCode
	   ,count(*) AS defectCount
	   ,vlr.prettyName
	   ,sum(vlr.treatmentPrice) AS price
FROM 
	   vDefectListReport vlr 
GROUP BY 
	    vlr.defectCode
	   ,vlr.prettyName
В результате чего получается следующее:

defectCode defectCount prettyName price
4.5 7 Дневной стационар 22788.21
4.5 34 Круглосуточный стационар 156255.70
4.2 354 Поликлиника 28500.79
4.5 1677 Поликлиника 131042.63

Кроме того есть табличка с видами помощи: table(id smallint, prettyName nchar(50))
Там 3 вида помощи - поликлиника, круглосуточный стационар и дневной стационар.

Вопрос: Как видно из результатов для кода дефекта 4.5 есть дефектные записи относящиеся к 3 видам помощи. По коду 4.2 только Поликлиника. Как сделать запрос в общем виде так чтобы для каждого кода дефекта (имеющегося) в группировке выводились записи с нулевыми значениями для количества (defectCount) и стоимости (price) отсутствующих видов помощи, в данном случае требуемый результат должен выглядит вот так:

defectCode defectCount prettyName price
4.5 7 Дневной стационар 22788.21
4.5 34 Круглосуточный стационар 156255.70
4.2 354 Поликлиника 28500.79
4.5 1677 Поликлиника 131042.63
4.2 0 Дневной стационар 0
4.2 0 Круглосуточный стационар 0


Собственно как бы вы решили эту задачу?
15 сен 09, 17:35    [7663247]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
SELECT 
	    t.defectCode
	   ,count(vlr.defectCode) AS defectCount
	   ,vlr.prettyName
	   ,isnull(sum(vlr.treatmentPrice), 0) AS price
FROM (select distinct defectCode from vDefectListReport) as t
left join vDefectListReport vlr on vlr.defectCode = t.defectCode
GROUP BY 
	    vlr.defectCode
	   ,vlr.prettyName
ЗЫ не проверял
15 сен 09, 17:40    [7663287]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
SELECT 
	    t.defectCode
	   ,count(vlr.defectCode) AS defectCount
	   ,vlr.prettyName
	   ,isnull(sum(vlr.treatmentPrice), 0) AS price
FROM (select distinct defectCode from vDefectListReport) as t
left join vDefectListReport vlr on vlr.defectCode = t.defectCode
GROUP BY 
	    t.defectCode
	   ,vlr.prettyName
забыл
15 сен 09, 17:42    [7663297]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
Le Peace
Member

Откуда: Москва
Сообщений: 8969
cross join +
case
15 сен 09, 17:43    [7663300]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
Le Peace
Member

Откуда: Москва
Сообщений: 8969
Паганель, вроде left join не сгодится - нужны ведь строки, которые никак не связаны между собой.
2 Автор, лучше бы выложили скрипты создания и заполнения таблиц.
15 сен 09, 17:44    [7663315]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Le Peace
нужны ведь строки, которые никак не связаны между собой.
точно
сразу как-то не увидел что cross нужен
15 сен 09, 17:46    [7663330]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
iljy
Member

Откуда:
Сообщений: 8711
Паганель,

?? А что собственно нам дает извлечение всех кодов из таблицы, а потом присоединение ее же?? Вы получите исходную таблицу с дополнительной колонкой кода по моему или я чего не понял?
15 сен 09, 17:47    [7663336]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
iljy
Member

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

SELECT 
	    vlr.defectCode
	   ,count(vlr.defectCode) AS defectCount
	   ,vlr.prettyName
	   ,isnull(sum(vlr.treatmentPrice), 0) AS price
FROM 
	   (select distinct defectcode from vDefectListReport)t1 cross join PrettyTable pr
		left join vDefectListReport vlr on vlr.defectCode = t1.defectCode and vlr.prettyName = pr.prettyName
GROUP BY t1.defectCode,pr.prettyName

и еще момент - плохая идея делать внешний ключ текстовым. Замените поле prettyName в vDefectListReport на поле id из второй таблички.
15 сен 09, 17:51    [7663362]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
iljy
Member

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

пардон, опечатка

SELECT 
	    t1.defectCode
	   ,count(vlr.defectCode) AS defectCount
	   ,pr.prettyName
              ,isnull(sum(vlr.treatmentPrice), 0) AS price
15 сен 09, 17:53    [7663377]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
Le Peace
Member

Откуда: Москва
Сообщений: 8969
iljy, ни к чему тут имхо 2 обращения к vDefectListReport.
15 сен 09, 17:55    [7663401]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
iljy
Member

Откуда:
Сообщений: 8711
Le Peace,

возможно, приведите другой вариант. Это просто первое что в голову пришло.
15 сен 09, 18:03    [7663463]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
DZeus
Member

Откуда: SPb
Сообщений: 65
iljy,

Спасибо вам, ваш запрос дал верный результат.
Про внешний ключ: исправлю разумеется, просто на скорую руку под конец дня ))
15 сен 09, 18:14    [7663542]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
iljy
Member

Откуда:
Сообщений: 8711
select defectCode, pr.prettyName, count(case when pr.prettyName = vlr.prettyName then 1 end),
		isnull(sum(case when pr.prettyName = vlr.prettyName then price end), 0)
FROM 
	   PrettyTable pr cross join vDefectListReport vlr 
GROUP BY 
	    defectCode,pr.prettyName

вариант с одним обращением к vDefectListReport
15 сен 09, 18:30    [7663637]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
DZeus
Member

Откуда: SPb
Сообщений: 65
iljy,

Не могли бы вы пояснить как работает этот кусок кода

count(case when pr.prettyName = vlr.prettyName then 1 end)

Не совсем понятно что будет происходить если если равенство вернёт TRUE и FALSE соответственно.
Почему count(1) возвращает нужное число, а почему COUNT(NULL) возвращает 0 если он вообще должен исключение выдать. Тоже самое мне и с суммой не понятно
sum(case when pr.prettyName = vlr.prettyName then price end)
или я что-то не досмотрел? )))
16 сен 09, 15:35    [7668256]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
iljy
Member

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

недосмотрели:) если почитать БОЛ - там сказано, что count(выражение) считает количествно значений выражения, отличных от NULL. Для sum - null так же игнорируется. Про исключение - откуда вы это взяли? Почитайте статью в БОЛ про агрегатные функции.
16 сен 09, 15:39    [7668288]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
DZeus
Member

Откуда: SPb
Сообщений: 65
iljy

Про исключение - откуда вы это взяли?


Ну при осмыслении запроса решил проверить что будет если...
SELECT COUNT(NULL)
SELECT SUM(NULL)

Пожалуй освежу бол.
16 сен 09, 15:53    [7668431]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
iljy
Member

Откуда:
Сообщений: 8711
DZeus
iljy

Про исключение - откуда вы это взяли?


Ну при осмыслении запроса решил проверить что будет если...
SELECT COUNT(NULL)
SELECT SUM(NULL)

Пожалуй освежу бол.


а текст ошибки читали?
Operand data type void type is invalid for sum operator.


попробуйте так
SELECT COUNT(cast(NULL as int))
SELECT SUM(cast(NULL as int))
16 сен 09, 15:58    [7668482]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iljy
DZeus
iljy

Про исключение - откуда вы это взяли?


Ну при осмыслении запроса решил проверить что будет если...
SELECT COUNT(NULL)
SELECT SUM(NULL)

Пожалуй освежу бол.


а текст ошибки читали?
Operand data type void type is invalid for sum operator.


попробуйте так
SELECT COUNT(cast(NULL as int))
SELECT SUM(cast(NULL as int))
Достаточно так:
SELECT COUNT(0*NULL)
SELECT SUM(0*NULL)
16 сен 09, 16:02    [7668521]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
iljy
Member

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

достаточно, но CAST нагляднее
16 сен 09, 16:03    [7668529]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
DZeus
Member

Откуда: SPb
Сообщений: 65
iljy,

Не понимаю тогда чем
NULL
отличается от
cast(NULL as int)
тем что один NULL имеет тип void а другой тип int ?
16 сен 09, 16:27    [7668705]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
iljy
Member

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

именно:)
16 сен 09, 16:27    [7668714]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
DZeus
Member

Откуда: SPb
Сообщений: 65
iljy,

Неопределённости разных типов это блин круто.
16 сен 09, 17:05    [7668982]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
iljy
Member

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

ээээ... а что собственно вас смущает?? любая переменная может иметь значение NULL, при этом типы у них различны. Ну и вообще T-SQL - язык строго типизированный.
16 сен 09, 17:23    [7669149]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как сделать запрос с "фиктивной строкой".  [new]
DZeus
Member

Откуда: SPb
Сообщений: 65
iljy,

Ну я не назвал бы это смущением, просто слегка удивлён. До сего дня я почему-то думал что NULL не имеет типов. NULL просто означает что в переменная не содержится никакого value. Причём тип переменной не играет роли.

Впрочем спасибо за то что открыли глаза. )))
17 сен 09, 09:26    [7671053]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить