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

Откуда:
Сообщений: 50
доброго времени суток!
есть следующая задача: необходимо выбрать последние изменения, сделанные в документах по всем полям. Т.е. имеем таблицу:
ID      Doc     Col1     Col1     Col1     ColXX     
1    |   X   |   11   |   12   |   13   |        
2    |   X   |        |   22   |   23   |   24   
3    |   X   |   31   |        |   33   |        
4    |   X   |        |   42   |        |        
5    |   X   |        |        |   53   |   54   

6    |   Y   |        |        |        |   64   
7    |   Y   |        |        |   73   |   74   
8    |   Y   |        |   82   |        |        

запрос должен вернуть:

Doc      Col1     Col1     Col1     ColXX  
X     |   31   |   42   |   53   |   54   
Y     |        |   82   |   73   |   74   

При этом количество столбцов может изменяться. Последние изменения - это значение столбца из строки с максимальным ID.

Буду рад советам по решению
28 окт 09, 09:06    [7847522]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
losk
Member

Откуда:
Сообщений: 50
Как вариант можно перебрать курсором все столбцы из sys.columns и выгрузить данные во временную таблицу с тремя столбцами(Doc, Название поля и Значение).
Потом развернуть временную таблицу пивотом..

Но может есть более красивое решение?
28 окт 09, 09:25    [7847599]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
aleks2
Guest
losk,

1. Совет: не делайте так больше НИКОГДА.

2. Это будет ОЧЕНЬ небыстро...
select T.Doc
, (select TOP 1 Col1 FROM [имеем таблицу] X WHERE X.Doc=T.Doc AND X.Col1 is not null ORDER BY X.ID DESC)
...
, (select TOP 1 ColXX FROM [имеем таблицу] X WHERE X.Doc=T.Doc AND X.ColXX is not null ORDER BY X.ID DESC)
from [имеем таблицу] T
GROUP BY T.Doc
28 окт 09, 09:29    [7847612]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
Taffy
Member

Откуда:
Сообщений: 20498
кака это - "количество столбцов может меняться?" - странная стуктура базы, однако....


select *
from mytable m inner join
(
select top 1  with ties id
from mytable
order by row_number() over(partition by  doc order by id desc)
) a on m.id = a.id

ЗЫ
не проверяла
28 окт 09, 09:36    [7847636]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
losk
Member

Откуда:
Сообщений: 50
2aleks2
Спасибо за совет, но кол-во столбцов может меняться часто, и не хотелось бы все время корректировать запрос.
28 окт 09, 09:47    [7847685]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
losk
Member

Откуда:
Сообщений: 50
2Taffy
Ваш запрос возвращает значения из строки с максимальным ID, это не верно.

Ничего странного в структуре базы, у пользователей есть userfriendly интерфейс, через который они добавляют/убриают поля из таблиц(система MS CRM).


Привожу скрипт таблицы:
CREATE TABLE tbl_TEST(ID integer, Doc NVARCHAR(100), Col1 NVARCHAR(5), Col2 NVARCHAR(5), Col3 NVARCHAR(5), ColXX NVARCHAR(5))
GO

INSERT tbl_TEST VALUES(1, N'X', '11', '12', '13', '')
INSERT tbl_TEST VALUES(2, N'X', '', '22', '23', '24')
INSERT tbl_TEST VALUES(3, N'X', '31', '', '33', '')
INSERT tbl_TEST VALUES(4, N'X', '', '42', '', '')
INSERT tbl_TEST VALUES(5, N'X', '', '', '53', '54')
INSERT tbl_TEST VALUES(6, N'Y', '', '', '', '64')
INSERT tbl_TEST VALUES(7, N'Y', '', '', '73', '74')
INSERT tbl_TEST VALUES(8, N'Y', '', '82', '', '')
GO
28 окт 09, 09:58    [7847753]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
Taffy
Member

Откуда:
Сообщений: 20498
losk
Последние изменения - это значение столбца из строки с максимальным ID.



losk
2Taffy
Ваш запрос возвращает значения из строки с максимальным ID, это не верно.


Где-то противоречие... Где?
28 окт 09, 10:01    [7847775]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
SELECT
 Doc,
 NULLIF(CAST(SUBSTRING(MAX(ISNULL(STR(ID,11)+STR(Col1,11), STR(0,22))),12,11)AS INT),0)Col1,
 NULLIF(CAST(SUBSTRING(MAX(ISNULL(STR(ID,11)+STR(Col2,11), STR(0,22))),12,11)AS INT),0)Col2,
 NULLIF(CAST(SUBSTRING(MAX(ISNULL(STR(ID,11)+STR(Col3,11), STR(0,22))),12,11)AS INT),0)Col3,
 NULLIF(CAST(SUBSTRING(MAX(ISNULL(STR(ID,11)+STR(ColXX,11), STR(0,22))),12,11)AS INT),0)ColXX
FROM T
GROUP BY Doc;
28 окт 09, 10:02    [7847779]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
losk
Member

Откуда:
Сообщений: 50
2iap, Taffy
Забыл указать в начале темы - выбирать необходимо последние значения отличные от нуля
28 окт 09, 10:05    [7847801]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
iap
Member

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

зачем там NVARCHAR, если поля содержат только цифры?
Кстати, я-то думал, там тип INTEGER...
28 окт 09, 10:05    [7847802]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
losk
2iap, Taffy
Забыл указать в начале темы - выбирать необходимо последние значения отличные от нуля
От какого нуля? Там и нуля-то нет нигде!
Пустые места в колонках из первого поста я принял за NULL.

Можно же было оформить тему по-человечески!
28 окт 09, 10:08    [7847822]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
losk
Member

Откуда:
Сообщений: 50
2iap
Тип столбцов может быть и строковым и числовым, я привел пример из головы.
28 окт 09, 10:09    [7847833]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
losk
2iap
Тип столбцов может быть и строковым и числовым, я привел пример из головы.
Тогда чем Вас не устраивает мой ответ?
28 окт 09, 10:11    [7847856]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
losk
Member

Откуда:
Сообщений: 50
2iap
Ваш запрос возвращает
Doc      Col1     Col1     Col1     ColXX  
X     |        |        |   53   |   54   
Y     |        |   82   |        |        

задача заключается в том, чтобы на выходе получить:

Doc      Col1     Col1     Col1     ColXX  
X     |   31   |   42   |   53   |   54   
Y     |        |   82   |   73   |   74   
28 окт 09, 10:15    [7847880]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
losk
2iap
Ваш запрос возвращает
Doc      Col1     Col1     Col1     ColXX  
X     |        |        |   53   |   54   
Y     |        |   82   |        |        

задача заключается в том, чтобы на выходе получить:

Doc      Col1     Col1     Col1     ColXX  
X     |   31   |   42   |   53   |   54   
Y     |        |   82   |   73   |   74   
По Вашему стартовому сообщению я сделал так:
SET NOCOUNT ON;
DROP TABLE T;
CREATE TABLE T(ID INT, Doc CHAR, Col1 INT, Col2 INT, Col3 INT, ColXX INT);
INSERT T(ID, Doc, Col1, Col2, Col3, ColXX)VALUES
 (1    ,   'X'   ,   11   ,   12   ,   13   , NULL       )
,(2    ,   'X'   ,  NULL      ,   22   ,   23   ,   24   )
,(3    ,   'X'   ,   31   ,  NULL      ,   33   , NULL   )
,(4    ,   'X'   ,  NULL      ,   42   , NULL       , NULL)   
,(5    ,   'X'   ,  NULL      ,  NULL      ,   53   ,   54 )  
,(6    ,   'Y'   ,  NULL      ,  NULL      ,  NULL      ,   64   )
,(7    ,   'Y'   ,  NULL      ,  NULL      ,   73   ,   74   )
,(8    ,   'Y'   ,  NULL      ,   82   , NULL       , NULL  );

SELECT
 Doc,
 NULLIF(CAST(SUBSTRING(MAX(ISNULL(STR(ID,11)+STR(Col1,11), STR(0,22))),12,11)AS INT),0)Col1,
 NULLIF(CAST(SUBSTRING(MAX(ISNULL(STR(ID,11)+STR(Col2,11), STR(0,22))),12,11)AS INT),0)Col2,
 NULLIF(CAST(SUBSTRING(MAX(ISNULL(STR(ID,11)+STR(Col3,11), STR(0,22))),12,11)AS INT),0)Col3,
 NULLIF(CAST(SUBSTRING(MAX(ISNULL(STR(ID,11)+STR(ColXX,11), STR(0,22))),12,11)AS INT),0)ColXX
FROM T
GROUP BY Docж
Результат получился таким:
Doc Col1 Col2 Col3 ColXX
X 31 42 53 54
Y NULL 82 73 74
28 окт 09, 10:19    [7847908]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Да! Забыл, что у меня-то SQL2008.
Так что для более старых версий VALUES надо бы заменить на SELECT ... UNION ALL SELECT ... UNION ALL SELECT ...
Или много отдельных INSERTов писать.
28 окт 09, 10:21    [7847924]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
iap
Member

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

кстати, надеюсь, вы не стали просто копипастить предлагаемые Вам варианты решения?
Для Вашего реального случая надо, конечно, доработать идеи.
28 окт 09, 10:23    [7847940]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
losk
Member

Откуда:
Сообщений: 50
2iap
Моя невнимательность, Ваш запрос запрос отработал верно.
Но как быть когда добавили 10 новых столбцов и удалили 5 старых? Прийдется заново лопатить запрос. Также стобцы могут быть разной длинны(от 1 до 2000) и содержать данные любых типов.


Все предложенные варианты я проверяю на тестовой таблице(из этой темы) :-)
28 окт 09, 10:41    [7848075]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
losk
2iap
Моя невнимательность, Ваш запрос запрос отработал верно.
Но как быть когда добавили 10 новых столбцов и удалили 5 старых? Прийдется заново лопатить запрос. Также стобцы могут быть разной длинны(от 1 до 2000) и содержать данные любых типов.


Все предложенные варианты я проверяю на тестовой таблице(из этой темы) :-)
Вас устроит только динамический SQL. IMHO
Который будет строить запрос, подобный тому, который предложил aleks2.
28 окт 09, 10:49    [7848136]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
мимо шел
Guest
то ли я чета не понял в постановке вопроса, то ли народ не ищет легких путей
SELECT Doc, Max(Col1), Max(Col2), Max(Col3), Max(ColXX)
FROM tbl_TEST
GROUP BY Doc
28 окт 09, 10:50    [7848146]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
Supra93
Member

Откуда:
Сообщений: 8174
мимо шел
то ли я чета не понял в постановке вопроса, то ли народ не ищет легких путей
SELECT Doc, Max(Col1), Max(Col2), Max(Col3), Max(ColXX)
FROM tbl_TEST
GROUP BY Doc

ТС
Последние изменения - это значение столбца из строки с максимальным ID.
28 окт 09, 10:55    [7848191]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
мимо шел,

просто losk привёл очень неудачный пример, в котором Col1, например, возрастает вместе с ID.
А из его слов ясно, что это необязательно.
28 окт 09, 11:02    [7848263]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
losk
Member

Откуда:
Сообщений: 50
2iap
Вариант с динамическим формированием строки запроса будет выполняться долго(как и вариант с курсором+пивот). Я надеялся, что есть эдакая встроенная функция, которая сразу сделает как надо :)


мимо шел
кол-во столбцов в таблице может меняться, и max(varchar) выдаст некорректный результат.
28 окт 09, 11:02    [7848265]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
losk
max(varchar) выдаст некорректный результат.
Ничего себе!
Интересно, а разработчики из фирмы Microsoft об этом знают?
28 окт 09, 11:04    [7848280]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
losk
2iap
Вариант с динамическим формированием строки запроса будет выполняться долго(как и вариант с курсором+пивот). Я надеялся, что есть эдакая встроенная функция, которая сразу сделает как надо :)


мимо шел
кол-во столбцов в таблице может меняться, и max(varchar) выдаст некорректный результат.
При чём здесь количество столбцов, никак не пойму?
И чем поможет пресловутый PIVOT в этом случае?

Что же касается уже готовой функции, которая будет всё делать вместо вас,
то её можно нанять на форуме "Работа"!
28 окт 09, 11:06    [7848298]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить