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

Откуда: Харьков
Сообщений: 11
Добрый день, подскажите пожалуйста как мне запрос правильно построить.
Есть таблица:
CREATE TABLE [dbo].[Category](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[CategoryTitle] [nvarchar](128) COLLATE Cyrillic_General_CI_AS NOT NULL,
	[CategoryDescription] [nvarchar](128) COLLATE Cyrillic_General_CI_AS NULL,
	[ParentCategoryId] [int] NULL,
	[UserId] [int] NOT NULL,
	[Visible] [bit] NULL CONSTRAINT [DF_Category_Hide]  DEFAULT ((0)),
	[CreationDate] [datetime] NOT NULL
 

Кроме нее еще 3 таблицы с темами, и сообщениями.
мне необходимо найти количество сообщений в каждой категории, при этом если у категории указана родительская то необходимо сумму ее постов учитывать в родительской..
Вот запрос который находит сумму по категориям
select dbo.Category.id as CategoryId,isnull(Count(dbo.Post.Id),0) as CountPost,dbo.Category.ParentCategoryId as Parent
from dbo.Category
left join (dbo.Topic
inner join dbo.Post
on dbo.Topic.Id=dbo.Post.TopicId
)
on (dbo.Category.Id=dbo.Topic.ParentCategoryId
and dbo.Category.ParentCategoryId=dbo.Topic.ParentCategoryId)
group by dbo.Category.id,dbo.Category.ParentCategoryId

Во результат:

2 0 NULL
3 0 NULL
5 0 NULL
6 0 3
7 0 3
8 0 3
9 0 2
13 0 5
14 0 NULL
15 0 NULL
16 0 2
17 0 2

Как мне просуммировать дочерние категории?? Спасибо...!
11 июн 09, 16:43    [7291506]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
dj_pulia
Member

Откуда: Харьков
Сообщений: 11
Ой, не тот запрос
новый:
select dbo.Category.id as CategoryId,isnull(Count(dbo.Post.Id),0) as CountPost,dbo.Category.ParentCategoryId as Parent
from dbo.Category
left join (dbo.Topic
inner join dbo.Post
on dbo.Topic.Id=dbo.Post.TopicId
)
on dbo.Category.Id=dbo.Topic.ParentCategoryId
group by dbo.Category.id,dbo.Category.ParentCategoryId

Результат запроса:
2	9	NULL
3 0 NULL
5 0 NULL
6 0 3
7 0 3
8 0 3
9 0 2
13 0 5
14 0 NULL
15 0 NULL
16 3 2
17 0 2
11 июн 09, 16:47    [7291534]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
ключевые слова для поиска "дерево". поищите, почитайте. и думаю все встанет на свои места

для спящего время бодрствования равносильно сну
11 июн 09, 16:49    [7291553]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
dj_pulia
Member

Откуда: Харьков
Сообщений: 11
Алексей2003
ключевые слова для поиска "дерево". поищите, почитайте. и думаю все встанет на свои места

для спящего время бодрствования равносильно сну


Спасибо, сча погуглю..
11 июн 09, 16:51    [7291565]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
ura
Member [заблокирован]

Откуда: Киев
Сообщений: 932
прежде чем гуглить по дереву, уточните - у вас сколько уровней категорий ?
что-то я не припомню сайтов с неограниченным количеством уровней подфорумов
Ежели их всего два, то примерно так:
select c.id as CategoryId
	,(select count(*) from  dbo.Topic t
			join dbo.Post p on t.Id=p.TopicId
		where  t.ParentCategoryId in (c.Id,cc.id)
		) 
from dbo.Category c
	left join dbo.Category cc on cc.ParentCategoryId=c.Id
group by c.id
11 июн 09, 16:57    [7291612]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2ura
на этом сайте неоднократно обсуждалась как сама организация деревьев, так и выборка из них, в том числе с неограниченным количеством вложений.

для спящего время бодрствования равносильно сну
11 июн 09, 17:00    [7291636]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2ura
и только потом прочитал сам запрос..
вы проверяли свой скрипт на быстродействие примерно на 1млн записей? чтото у меня он вызывает смутные подозрения на жесткие тормоза... даже с индексами.

для спящего время бодрствования равносильно сну
11 июн 09, 17:03    [7291649]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
dj_pulia
Member

Откуда: Харьков
Сообщений: 11
ura

прежде чем гуглить по дереву, уточните - у вас сколько уровней категорий ?
что-то я не припомню сайтов с неограниченным количеством уровней подфорумов
Ежели их всего два, то примерно так:
select c.id as CategoryId
	,(select count(*) from  dbo.Topic t
			join dbo.Post p on t.Id=p.TopicId
		where  t.ParentCategoryId in (c.Id,cc.id)
		) 
from dbo.Category c
	left join dbo.Category cc on cc.ParentCategoryId=c.Id
group by c.id



Сервер материться:
Column 'dbo.Category.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
11 июн 09, 17:06    [7291667]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
ura
Member [заблокирован]

Откуда: Киев
Сообщений: 932
Алексей2003
2ura
и только потом прочитал сам запрос..
вы проверяли свой скрипт на быстродействие примерно на 1млн записей? чтото у меня он вызывает смутные подозрения на жесткие тормоза... даже с индексами.

голословное утверждение... конечно, тормоза возможны из-за in, но они легко нивелируются, если переписать запрос через union... это при том, если оптимизатор выберет scan - что далеко не факт.
А тестировать на млн записей я не собираюсь, сие прерогатива автора
11 июн 09, 17:09    [7291691]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
голословное конечно, но основанное на опыте работы как с IN, так и с select в построчной выборке (каждое из них может вызвать тормоза жуткие). а тут прям целый букет.
как они нивелируются я знаю, и что это можно сделать легко тоже.

для спящего время бодрствования равносильно сну
11 июн 09, 17:12    [7291714]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
А версия сервера какая?
11 июн 09, 17:15    [7291733]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
dj_pulia
Member

Откуда: Харьков
Сообщений: 11
Ray D
А версия сервера какая?


Microsoft SQL Server Express Edition
9.00.3042.00
11 июн 09, 17:19    [7291745]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
ura
Member [заблокирован]

Откуда: Киев
Сообщений: 932
Алексей2003
голословное конечно, но основанное на опыте работы как с IN, так и с select в построчной выборке (каждое из них может вызвать тормоза жуткие). а тут прям целый букет.
как они нивелируются я знаю, и что это можно сделать легко тоже.

IN тормоза вызвать может, а вот select в построчной выборке здесь отработает нормально. точнее отработал-бы...

автор
Сервер материться:
Column 'dbo.Category.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

да, фигню написал-с...
для оптимальности воспользуемся временной таблицей:
select c.Id
	,c.ParentCategoryId
	,count(*) as cnt
into #t
from  dbo.Topic t join dbo.Post p on t.Id=p.TopicId
	join dbo.Category c on c.Id=t.ParentCategoryId
group by c.Id,c.ParentCategoryId

select c.Id,IsNull(t.cnt,0)+IsNull(sum(tc.cnt),0)
from Category c left join #t t on t.CategoryId=c.CategoryId
	left join #t tc on tc.ParentCategoryId=c.CategoryId
11 июн 09, 17:46    [7291875]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2ura
в построчной выборке Вы количество вызовов подзапросов смотрели?

для спящего время бодрствования равносильно сну
11 июн 09, 17:52    [7291902]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
ura
Member [заблокирован]

Откуда: Киев
Сообщений: 932
Алексей2003
2ura
в построчной выборке Вы количество вызовов подзапросов смотрели?

для вашего опыта у вас странные представления о механизмах оптимизатора
это ж не функция, чтоб его построчно вызывать
11 июн 09, 18:05    [7291946]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2ura
наверное я не так читаю.
set nocount on
create table t (i int, i2 int, i3 int)
declare @i int
set @i = 0
while @i < 10000 begin
 insert into t values (@i, rand() * 2, rand() * 3)
 set @i = @i + 1
end
--сюда вставлять индексы
go
SET showplan_all on
go
select *, (select top 1 i from t t2 where t2.i3 = t1.i3) from t t1

select t1.*, t2.i from t t1 join t t2 on t2.i3 = t1.i3


go
SET showplan_all off
go
drop table t

смотреть колонку Estimate Executions.

для спящего время бодрствования равносильно сну
11 июн 09, 18:18    [7292001]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
ura
Member [заблокирован]

Откуда: Киев
Сообщений: 932
2Алексей2003

ну не нужно подменять запросы... про top 1 речи не было
с count(*) такого не происходит
кроме того, не нужно путать выполнение подзапроса с конкретнім пунктом плана (в данном случае, table scan)
11 июн 09, 18:30    [7292051]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
dj_pulia
Member

Откуда: Харьков
Сообщений: 11
Подскажите что в запросе не верно:
with tree (CategoryId, PostCount)
as (select dbo.Category.id as CategoryId,Count(dbo.Post.Id) as PostCount
		from dbo.Category
			left join dbo.Topic
			inner join dbo.Post
			on Topic.Id=Post.TopicId
			on Category.Id=Topic.ParentCategoryId
		where dbo.Category.ParentCategoryId is null
		group by dbo.Category.id
union all
   select dbo.Category.id as CategoryId, Count(dbo.Post.Id)as PostCount
   from (dbo.Category
			left join dbo.Topic
			inner join dbo.Post
			on Topic.Id=Post.TopicId
			on Category.Id=Topic.ParentCategoryId)
			inner join tree on tree.CategoryId = dbo.Category.ParentCategoryId
			group by dbo.Category.id
	)
select CategoryId, PostCount
from tree
order by CategoryId

Ошибка:

Msg 467, Level 16, State 1, Line 1
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'tree'.
Msg 462, Level 16, State 1, Line 1
Outer join is not allowed in the recursive part of a recursive common table expression 'tree'.
11 июн 09, 18:45    [7292112]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
Интересно, а рекурсивным CTE это можно сделать?

Попробовал что-то такое, но пока чего-то немного не то получается :)

+
drop table dbo.category
go
create table dbo.category	(
		category_id			int				identity
	,	name				nvarchar(128)
	,	parent_category_id	int				null
)
go
insert into dbo.category	(
		name
)
select name
from sys.objects
go
update cat
set parent_category_id = case when abs(checksum(newid())) % 100 < 30 then null else (
								select top 1 category_id 
								from dbo.category ct
								where ct.category_id != cat.category_id
								order by NEWID()
							)
						end
from dbo.category cat
go
drop table dbo.post
go
create table dbo.post	(
		category_id			int
	,	title				nvarchar(128)
)
go
insert into dbo.post	(
		category_id
	,	title
)
select top 100
		abs(checksum(newid())) % (select count(*) from dbo.category) + 1 as category_id
	,	cast(newid() as varchar(48))	as title
from sys.objects s1, sys.objects s2, sys.objects s3
go

with posts(category_id, parent_category, dummy) AS 
(
    select category_id, category_id as parent_category, 1 as dummy
    from dbo.post
    union all
    select p.category_id, parent_cat.category_id, 1 as dummy
    from posts p
    inner join (select con.parent_category_id, pp.category_id
			    from dbo.post pp
				inner join dbo.category con	on pp.category_id = con.category_id
	) parent_cat on p.parent_category = parent_cat.parent_category_id
)
select	
		pst.category_id
	,	c.name
	,	sum(dummy) as in_this_category
from posts pst
left join dbo.category c on c.category_id = pst.category_id
group by pst.category_id, c.name
option (maxrecursion 32000)
go
11 июн 09, 18:47    [7292122]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
left join и group by в рекурсивных запросах не разрешены к сожалению..

для спящего время бодрствования равносильно сну
11 июн 09, 18:48    [7292124]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
dj_pulia,

Ну дак все же в ошибке написано. Рекурсивные CTE имеют ряд ограничений.
11 июн 09, 18:48    [7292125]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
dj_pulia
Member

Откуда: Харьков
Сообщений: 11
Ray D
dj_pulia,

Ну дак все же в ошибке написано. Рекурсивные CTE имеют ряд ограничений.


эх, я надеялся что в запросе бок какойто.. :(
11 июн 09, 18:49    [7292128]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
ura
Member [заблокирован]

Откуда: Киев
Сообщений: 932
объясни наконец, у тебя дерево или фиксированное количество уровней ??
11 июн 09, 18:53    [7292139]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
dj_pulia
Member

Откуда: Харьков
Сообщений: 11
ura
объясни наконец, у тебя дерево или фиксированное количество уровней ??


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

Достаточно 2 уровня..
11 июн 09, 18:59    [7292155]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по запросу  [new]
dj_pulia
Member

Откуда: Харьков
Сообщений: 11
ura
объясни наконец, у тебя дерево или фиксированное количество уровней ??


запрос тот что ты писал просто не пашет,
а так бы я не мучился.. :)
11 июн 09, 19:01    [7292162]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить