Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / sql запрос для View заменить NULL значение / 10 сообщений из 10, страница 1 из 1
10.01.2020, 20:27
    #39912628
Igorz
Igorz
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql запрос для View заменить NULL значение
Подскажите пожалуйста sql запрос для View. Задача состоит в том что нужно заменить NULL значение в колонке Price, сумму нужно перенести на нижние строки которые имеют значение NULL.

исходные данные :

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
CREATE TABLE [dbo].[Product](
	[ProductID] [int] NOT NULL,
	[ProductItemID] [varchar](50) NOT NULL,
	[Price] [decimal](18, 2) NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (1, N'ABC100', CAST(1450.50 AS Decimal(18, 2)))
GO
INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (1, N'ABC120', NULL)
GO
INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (1, N'ABC130', NULL)
GO
INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (1, N'ABC135', NULL)
GO
INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (1, N'ABC140', CAST(2310.70 AS Decimal(18, 2)))
GO
INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (1, N'ABC146', NULL)
GO
INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (1, N'ABC151', NULL)
GO
INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (1, N'ABC159', CAST(3450.30 AS Decimal(18, 2)))
GO
INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (1, N'ABC160', NULL)
GO
INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (2, N'CDAD002', CAST(1000.30 AS Decimal(18, 2)))
GO
INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (2, N'CDAD060', NULL)
GO
INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (2, N'CDAD100', NULL)
GO
INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (2, N'CDAD101', NULL)
GO
INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (2, N'CDAD102', NULL)
GO
INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (2, N'CDAD105', CAST(2010.45 AS Decimal(18, 2)))
GO
INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (2, N'CDAD110', NULL)
GO
INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (2, N'CDAD150', NULL)
GO
INSERT [dbo].[Product] ([ProductID], [ProductItemID], [Price]) VALUES (2, N'CDAD450', CAST(3410.50 AS Decimal(18, 2)))
GO





Код: sql
1.
2.
3.
4.
5.
SELECT ProductID
      ,ProductItemID
      ,Price
FROM Product
ORDER BY 1,2



Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
+-----------+---------------+---------+
| ProductID | ProductItemID | Price   |
+-----------+---------------+---------+
| 1         | ABC100        | 1450.50 |
| 1         | ABC120        |         |
| 1         | ABC130        |         |
| 1         | ABC135        |         |
| 1         | ABC140        | 2310.70 |
| 1         | ABC146        |         |
| 1         | ABC151        |         |
| 1         | ABC159        | 3450.30 |
| 1         | ABC160        |         |
| 2         | CDAD002       | 1000.30 |
| 2         | CDAD060       |         |
| 2         | CDAD100       |         |
| 2         | CDAD101       |         |
| 2         | CDAD102       |         |
| 2         | CDAD105       | 2010.45 |
| 2         | CDAD110       |         |
| 2         | CDAD150       |         |
| 2         | CDAD450       | 3410.50 |
+-----------+---------------+---------+

 суть запроса : 

 - нужно заменить NULL значение в колонке Price, сумму нужно перенести на нижние строки которые имеют значение NULL.
+-----------+---------------+---------+
| ProductID | ProductItemID | Price   |
+-----------+---------------+---------+
| 1         | ABC100        |  1450.50  |
| 1         | ABC120        |  1450.50  |
| 1         | ABC130        |  1450.50  |
| 1         | ABC135        |  1450.50  |
| 1         | ABC140        |  2310.70  |
| 1         | ABC146        |  2310.70  |
| 1         | ABC151        |  2310.70  |
| 1         | ABC159        |  3450.30  |
| 1         | ABC160        |  3450.30  |
| 2         | CDAD002       |  1000.30  |
| 2         | CDAD060       |  1000.30  |
| 2         | CDAD100       |  1000.30  |
| 2         | CDAD101       |  1000.30  |
| 2         | CDAD102       |  1000.30  |
| 2         | CDAD105       |  2010.45  |
| 2         | CDAD110       |  2010.45  |
| 2         | CDAD150       |  2010.45  |
| 2         | CDAD450       |  3410.50  |
+-----------+---------------+---------+


версия
Microsoft SQL Server 2012
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2020, 21:10
    #39912636
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql запрос для View заменить NULL значение
Igorz, Если ProductItemId - монотонно возрастает, то нет ничего проще:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
Select 
a.ProductID
,a.ProductItemID
,tt.Price
From dbo.Product a
cross apply
(Select top(1) t.Price
From dbo.Product t
Where a.ProductId = t.ProductId
and t.ProtuctItemId <= a.ProductItemID
and t.Price is not Null
Order by t.ProductItemID Desc
) tt


Тормозить только будет, на больших объемах.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2020, 23:24
    #39912667
Igorz
Igorz
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql запрос для View заменить NULL значение
uaggster, спасибо за быстрый ответ!

Поправил название t.ProtuctItemId и работает прекрасно! но возможно ли построить конструкцию с left join? так как есть ограничение в использование cross apply?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
Select 
a.ProductID
,a.ProductItemID
,tt.Price
From dbo.Product a
cross apply
(Select top(1) 
t.Price
From dbo.Product t
Where t.ProductID=a.ProductID
and t.ProductItemID <= a.ProductItemID
and t.Price is not Null
Order by t.ProductID,t.ProductItemID DESC
) tt
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2020, 23:49
    #39912677
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql запрос для View заменить NULL значение
Igorz,

cross apply можно заменить на такой же корелированный подзапрос.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2020, 00:13
    #39912685
nullin
nullin
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql запрос для View заменить NULL значение
Можно и без подзапросов, но как оно по скорости будет надо смотреть.
Код: sql
1.
2.
3.
4.
select a.ProductID, a.ProductItemID,
       convert(decimal(18, 2), right(max(iif(a.Price is null, null, concat(convert(char(50), a.ProductItemID), convert(char(20), a.Price))))
                                      over(order by a.ProductID, a.ProductItemID), 20)) as notNullPrice
  from dbo.Product a
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2020, 00:21
    #39912687
nullin
nullin
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql запрос для View заменить NULL значение
Igorz, кстати, если Price по всей таблице может быть null, то cross apply усечёт выборку, и тогда его лучше на outer apply заменить.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2020, 00:21
    #39912688
Igorz
Igorz
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql запрос для View заменить NULL значение
Владислав Колосов,
спасибо за подсказу. Будем разбератса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2020, 09:50
    #39912719
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql запрос для View заменить NULL значение
Igorz
uaggster, спасибо за быстрый ответ!

Поправил название t.ProtuctItemId и работает прекрасно! но возможно ли построить конструкцию с left join? так как есть ограничение в использование cross apply?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
Select 
a.ProductID
,a.ProductItemID
,tt.Price
From dbo.Product a
cross apply
(Select top(1) 
t.Price
From dbo.Product t
Where t.ProductID=a.ProductID
and t.ProductItemID <= a.ProductItemID
and t.Price is not Null
Order by t.ProductID,t.ProductItemID DESC
) tt


Order by t.ProductID, t.ProductItemID DESC
Выделенное - лишнее, т.к. вверху есть Where t.ProductID=a.ProductID
И да, конечно нужно outer apply.

но возможно ли построить конструкцию с left join?
Можно. Например так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
;WITH cte
AS (
	SELECT a.[ProductID]
		,a.[ProductItemID]
		,LEAD(a.[ProductItemID]) OVER (
			PARTITION BY a.[ProductID] ORDER BY a.[ProductItemID] ASC
			) [ProductItemID_NEXT]
		,a.[Price]
	FROM [dbo].[Product] a
	WHERE a.Price IS NOT NULL
	)
SELECT a.[ProductID]
	,a.[ProductItemID]
	,b.Price Price
FROM [dbo].[Product] a
LEFT JOIN cte b ON a.ProductID = b.ProductID
	AND a.ProductItemID >= b.ProductItemID
	AND (
		a.ProductItemID < b.[ProductItemID_NEXT]
		OR b.[ProductItemID_NEXT] IS NULL
		)


Но, боюсь у него с производительностью тоже не фонтан будет.
Хотя, на очень больших выборках, возможно, быстрее чем первый, т.к. в первом случае - всегда будет nested loop, а во втором случае LEFT JOIN может быть и hash и merge, даже принудительно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2020, 09:52
    #39912720
L_argo
L_argo
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql запрос для View заменить NULL значение
Задача из курсача или экзамена ?
Сама формулировка идиотская. В нормальном бизнесе такого нет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2020, 15:08
    #39912784
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql запрос для View заменить NULL значение
L_argo
Задача из курсача или экзамена ?
Сама формулировка идиотская. В нормальном бизнесе такого нет.

На бухгалтерском языке это называется "протащить до конца"
:-)
И делается монотонным даблкликом на квадратике в правом нижнем углу в каждой ячейке с заполненной ценой в Эксель. Ну, или если бухгалтер - космически продвинут, то с формулой ЕПусто со ссылкой на предыдущую ячейку в соседнем столбце.
:-)))).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / sql запрос для View заменить NULL значение / 10 сообщений из 10, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 154ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1686664]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 41ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 47ms
get tp. blocked users:
 1ms
others: 19ms
total:  154ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]