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

Откуда: Санкт-Петербург
Сообщений: 3662
Есть такая вот функция для агрегации строк

CREATE FUNCTION GetAgentRefNos(@UID INTEGER,

@CustomerID INTEGER,
@DateFrom DateTime,
@DateTo DateTime
)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @Result NVARCHAR(4000)
SET @Result = ''

Select @Result = @Result + CAST(Accidents.ID AS nvarchar) + ', '
FROM Calls, Accidents, CustomerProduct, Users
WHERE CustomerProduct.CustomerID = @CustomerID AND
Users.ID = Calls.UserID AND
Users.ID = @UID AND
Calls.AccidentID = Accidents.ID AND
Accidents.CustProdID = CustomerProduct.ID AND
Calls.DateTime >= cast(cast(@DateFrom - 0.5 as int) as datetime) AND
Calls.DateTime < cast(cast(@DateTo + 0.5 as int) as datetime)

Set @Result = RTRIM(@Result)
RETURN LEFT(@Result, Len(@Result) - 1)
END
GO


Ее результат

declare @DateFrom datetime

declare @DateTo datetime
declare @CustomerID int

set @DateFrom = '20040304'
set @DateTo = '20040304'
set @CustomerID = 3

Select Users.ShortName, Users.Name,
dbo.GetAgentRefNos(Users.ID, @CustomerID, @DateFrom, @DateTo)
from Users;

ShortName RefNOs
--------- -----------------------

CS 1, 1, 2

(1 row(s) affected)


Теперь хочу исключить дупликаты для этого помещаю distinct

CREATE FUNCTION GetAgentRefNos(@UID INTEGER,

@CustomerID INTEGER,
@DateFrom DateTime,
@DateTo DateTime
)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @Result NVARCHAR(4000)
SET @Result = ''

Select distinct @Result = @Result + CAST(Accidents.ID AS nvarchar) + ', '
FROM Calls, Accidents, CustomerProduct, Users
WHERE CustomerProduct.CustomerID = @CustomerID AND
Users.ID = Calls.UserID AND
Users.ID = @UID AND
Calls.AccidentID = Accidents.ID AND
Accidents.CustProdID = CustomerProduct.ID AND
Calls.DateTime >= cast(cast(@DateFrom - 0.5 as int) as datetime) AND
Calls.DateTime < cast(cast(@DateTo + 0.5 as int) as datetime)

Set @Result = RTRIM(@Result)
RETURN LEFT(@Result, Len(@Result) - 1)
END
GO


Результат

ShortName RefNOs                                                                                               

--------- ---------------------

CS 2

(1 row(s) affected)


В чем может быть дело?
4 мар 04, 09:56    [562843]     Ответить | Цитировать Сообщить модератору
 Re: Использование distinct в конструкции вида Select distinct @Result = @Result + ...  [new]
Glory
Member

Откуда:
Сообщений: 104760
Select @Result = @Result + A.ID + ', '  

FROM
(Select distinct CAST(Accidents.ID AS nvarchar) as ID
FROM Calls, Accidents, CustomerProduct, Users
WHERE CustomerProduct.CustomerID = @CustomerID AND
Users.ID = Calls.UserID AND
Users.ID = @UID AND
Calls.AccidentID = Accidents.ID AND
Accidents.CustProdID = CustomerProduct.ID AND
Calls.DateTime >= cast(cast(@DateFrom - 0.5 as int) as datetime) AND
Calls.DateTime < cast(cast(@DateTo + 0.5 as int) as datetime)
) AS a
4 мар 04, 09:59    [562848]     Ответить | Цитировать Сообщить модератору
 Re: Использование distinct в конструкции вида Select distinct @Result = @Result + ...  [new]
Violina
Member

Откуда: Санкт-Петербург
Сообщений: 3662
Пока что решила эту проблему с пом. подзапроса, но надеюсь что можно решить и без него

CREATE FUNCTION GetAgentRefNos(@UID INTEGER,

@CustomerID INTEGER,
@DateFrom DateTime,
@DateTo DateTime
)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @Result NVARCHAR(4000)
SET @Result = ''

Select @Result = @Result + a.idsr + ', '
from
(Select distinct CAST(Accidents.ID AS nvarchar(15)) idsr
FROM Calls, Accidents, CustomerProduct, Users
WHERE CustomerProduct.CustomerID = @CustomerID AND
Users.ID = Calls.UserID AND
Users.ID = @UID AND
Calls.AccidentID = Accidents.ID AND
Accidents.CustProdID = CustomerProduct.ID AND
Calls.DateTime >= cast(cast(@DateFrom - 0.5 as int) as datetime) AND
Calls.DateTime < cast(cast(@DateTo + 0.5 as int) as datetime)) a

Set @Result = RTRIM(@Result)
RETURN LEFT(@Result, Len(@Result) - 1)
END
GO
4 мар 04, 09:59    [562851]     Ответить | Цитировать Сообщить модератору
 Re: Использование distinct в конструкции вида Select distinct @Result = @Result + ...  [new]
ura
Member [заблокирован]

Откуда: Киев
Сообщений: 932
Поскольку это недокументированный способ агрегации строк, ничего удивительного здесь нет... еще такой эффект мне встречался при использовании order by в сложном запросе. Придется результат select-а скинуть во врем. таблицу
4 мар 04, 10:01    [562859]     Ответить | Цитировать Сообщить модератору
 Re: Использование distinct в конструкции вида Select distinct @Result = @Result + ...  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
А если попробовать немного по другому переписать запрос
Select @Result = @Result + CAST(ID AS nvarchar) + ', ' 

from(
Select distinct Accidents.ID
FROM Calls, Accidents, CustomerProduct, Users
WHERE CustomerProduct.CustomerID = @CustomerID AND
Users.ID = Calls.UserID AND
Users.ID = @UID AND
Calls.AccidentID = Accidents.ID AND
Accidents.CustProdID = CustomerProduct.ID AND
Calls.DateTime >= cast(cast(@DateFrom - 0.5 as int) as datetime) AND
Calls.DateTime < cast(cast(@DateTo + 0.5 as int) as datetime)
) aaa
4 мар 04, 10:01    [562860]     Ответить | Цитировать Сообщить модератору
 Re: Использование distinct в конструкции вида Select distinct @Result = @Result + ...  [new]
Violina
Member

Откуда: Санкт-Петербург
Сообщений: 3662
Glory

Да, у форума MS SQL неплохое время отклика:) Мы даже alias одинаково назвали ...

А почему с distinct не работает

Select distinct @Result = @Result + CAST(Accidents.ID AS nvarchar) + ', '


не уже ли такая конструкция не поддерживается?
4 мар 04, 10:02    [562863]     Ответить | Цитировать Сообщить модератору
 Re: Использование distinct в конструкции вида Select distinct @Result = @Result + ...  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
У меня круче - вместо одного "a" целых три
4 мар 04, 10:02    [562864]     Ответить | Цитировать Сообщить модератору
 Re: Использование distinct в конструкции вида Select distinct @Result = @Result + ...  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
Попробуй так

CREATE FUNCTION GetAgentRefNos(@UID INTEGER,
@CustomerID INTEGER,
@DateFrom DateTime,
@DateTo DateTime
)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @Result NVARCHAR(4000)
SET @Result = ''

Select @Result = @Result + CAST(T.ID AS nvarchar) + ', '
from
(SELECT distinct Accidents.ID FROM Calls, Accidents, CustomerProduct, Users
WHERE CustomerProduct.CustomerID = @CustomerID AND
Users.ID = Calls.UserID AND
Users.ID = @UID AND
Calls.AccidentID = Accidents.ID AND
Accidents.CustProdID = CustomerProduct.ID AND
Calls.DateTime >= cast(cast(@DateFrom - 0.5 as int) as datetime) AND
Calls.DateTime < cast(cast(@DateTo + 0.5 as int) as datetime)) T

Set @Result = RTRIM(@Result)
RETURN LEFT(@Result, Len(@Result) - 1)
END
GO

4 мар 04, 10:12    [562910]     Ответить | Цитировать Сообщить модератору
 Re: Использование distinct в конструкции вида Select distinct @Result = @Result + ...  [new]
Glory
Member

Откуда:
Сообщений: 104760
Как уже сказал ura данный способ сложения символьных столбцов недокументирован и значит нет никаких гарантий того, как оптмизатор будет обрабатывать конструкции типа distinct/top/order в этой ситуации. Многое может зависеть от версии сервера и/или самих таблиц из запроса. Т.е. от плана выполнения.
Поскольку конкретно distinct делается последним шагом. А вот к какому набору применит его оптимизатор - 100%но неизвестно.
4 мар 04, 10:13    [562913]     Ответить | Цитировать Сообщить модератору
 Re: Использование distinct в конструкции вида Select distinct @Result = @Result + ...  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
еще вариант а то я последний был по первому варианту

CREATE FUNCTION GetAgentRefNos(@UID INTEGER,
@CustomerID INTEGER,
@DateFrom DateTime,
@DateTo DateTime
)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @Result NVARCHAR(4000)
SET @Result = ''

Select @Result = @Result +
case when charindex(CAST(T.ID AS nvarchar),@result)=0
then CAST(T.ID AS nvarchar)+ ', '
else '' end
from
(SELECT distinct Accidents.ID FROM Calls, Accidents, CustomerProduct, Users
WHERE CustomerProduct.CustomerID = @CustomerID AND
Users.ID = Calls.UserID AND
Users.ID = @UID AND
Calls.AccidentID = Accidents.ID AND
Accidents.CustProdID = CustomerProduct.ID AND
Calls.DateTime >= cast(cast(@DateFrom - 0.5 as int) as datetime) AND
Calls.DateTime < cast(cast(@DateTo + 0.5 as int) as datetime)) T

Set @Result = RTRIM(@Result)
RETURN LEFT(@Result, Len(@Result) - 1)
END
GO

4 мар 04, 10:19    [562930]     Ответить | Цитировать Сообщить модератору
 Re: Использование distinct в конструкции вида Select distinct @Result = @Result + ...  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
пардон
подзапрос конечно тогда не нужен

CREATE FUNCTION GetAgentRefNos(@UID INTEGER,
@CustomerID INTEGER,
@DateFrom DateTime,
@DateTo DateTime
)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @Result NVARCHAR(4000)
SET @Result = ''

Select @Result = @Result +
case when charindex(CAST(T.ID AS nvarchar),@result)=0
then CAST(T.ID AS nvarchar)+ ', '
else '' end
FROM Calls, Accidents, CustomerProduct, Users
WHERE CustomerProduct.CustomerID = @CustomerID AND
Users.ID = Calls.UserID AND
Users.ID = @UID AND
Calls.AccidentID = Accidents.ID AND
Accidents.CustProdID = CustomerProduct.ID AND
Calls.DateTime >= cast(cast(@DateFrom - 0.5 as int) as datetime) AND
Calls.DateTime < cast(cast(@DateTo + 0.5 as int) as datetime)

Set @Result = RTRIM(@Result)
RETURN LEFT(@Result, Len(@Result) - 1)
END
GO
4 мар 04, 10:21    [562939]     Ответить | Цитировать Сообщить модератору
 Re: Использование distinct в конструкции вида Select distinct @Result = @Result + ...  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Тогда уж вместо

case when charindex(CAST(T.ID AS nvarchar),@result)=0


надо

case when charindex(replace(CAST(T.ID AS nvarchar), ',', ''),@result)=0 

а то после первой вставки запятой эта конструкция прекратит наращивать строку.
4 мар 04, 10:24    [562946]     Ответить | Цитировать Сообщить модератору
 Re: Использование distinct в конструкции вида Select distinct @Result = @Result + ...  [new]
Glory
Member

Откуда:
Сообщений: 104760
ИМХО - это не сработат, если T.ID содержит значания больше 9

select charindex('1', '11, 12')
4 мар 04, 10:29    [562962]     Ответить | Цитировать Сообщить модератору
 Re: Использование distinct в конструкции вида Select distinct @Result = @Result + ...  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
to Glory

можно ордернуть по возрастанию ID :)
4 мар 04, 10:36    [562975]     Ответить | Цитировать Сообщить модератору
 Re: Использование distinct в конструкции вида Select distinct @Result = @Result + ...  [new]
Violina
Member

Откуда: Санкт-Петербург
Сообщений: 3662
Немного модифицировала функцию, чтобы она возвращала NULL если нет результатов для определнного Agent

Set @Result = RTRIM(@Result) 

if Len(@Result) > 2 SET @Result = LEFT(@Result, Len(@Result) - 1)
else SET @Result = NULL

RETURN @Result


Вопрос могу я быть уверенной что оптимайзер не будет вычислять функцию дважды в след. конструкции?

Select Users.ShortName, Users.Name,  

dbo.GetAgentRefNos(Users.ID, @CustomerID, @DateFrom, @DateTo) as RefNOs
from Users
WHERE dbo.GetAgentRefNos(Users.ID, @CustomerID, @DateFrom, @DateTo) is not NULL;


Или все же стоит использовать подзапрос?

select * from

(Select Users.ShortName, Users.Name,
dbo.GetAgentRefNos(Users.ID, @CustomerID, @DateFrom, @DateTo) as RefNOs
from Users
) a
WHERE a.RefNOs is not NULL
4 мар 04, 10:50    [563011]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить