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

Откуда:
Сообщений: 11
Есть запрос к базе mssql который выдает примерно такой вывод:

State Date User
1 15.01.2014 9:00 Vasya
1 15.01.2014 9:00 Vasya
1 15.01.2014 9:00 Vasya
1 15.01.2014 9:07 Vasya
1 15.01.2014 9:09 Vasya
1 15.01.2014 9:26 Vasya
2 15.01.2014 9:27 Vasya
1 15.01.2014 12:59 Vasya
1 15.01.2014 12:59 Vasya
2 15.01.2014 14:43 Vasya
1 15.01.2014 9:06 Petya
1 15.01.2014 9:06 Petya
1 15.01.2014 9:06 Petya
2 15.01.2014 15:20 Petya
1 15.01.2014 16:32 Petya
1 15.01.2014 16:32 Petya
1 15.01.2014 16:32 Petya
1 15.01.2014 16:43 Petya
2 15.01.2014 16:43 Petya
1 15.01.2014 12:59 Jora
1 15.01.2014 13:16 Jora
2 15.01.2014 13:16 Jora
1 15.01.2014 14:32 Jora
1 15.01.2014 14:32 Jora
1 15.01.2014 14:32 Jora
1 15.01.2014 14:43 Jora
2 15.01.2014 14:43 Jora

Надо что бы в выводе поставлялся уникальный номер как только в столбике State меняется цифра или имя в столбике Name.
Выглядеть должно вот так:

N State Date User
1 1 15.01.2014 9:00 Vasya
1 1 15.01.2014 9:00 Vasya
1 1 15.01.2014 9:00 Vasya
1 1 15.01.2014 9:07 Vasya
1 1 15.01.2014 9:09 Vasya
1 1 15.01.2014 9:26 Vasya
2 2 15.01.2014 9:27 Vasya
3 1 15.01.2014 12:59 Vasya
3 1 15.01.2014 12:59 Vasya
4 2 15.01.2014 14:43 Vasya
5 1 15.01.2014 9:06 Petya
5 1 15.01.2014 9:06 Petya
5 1 15.01.2014 9:06 Petya
6 2 15.01.2014 15:20 Petya
7 1 15.01.2014 16:32 Petya
7 1 15.01.2014 16:32 Petya
7 1 15.01.2014 16:32 Petya
7 1 15.01.2014 16:43 Petya
8 2 15.01.2014 16:43 Petya
9 1 15.01.2014 12:59 Jora
9 1 15.01.2014 13:16 Jora
10 2 15.01.2014 13:16 Jora
11 1 15.01.2014 14:32 Jora
11 1 15.01.2014 14:32 Jora
11 1 15.01.2014 14:32 Jora
11 1 15.01.2014 14:43 Jora
12 2 15.01.2014 14:43 Jora


Помогите пожалуйста, а то выглядит просто, а как это сделать вообще без понятия!
27 янв 14, 14:26    [15475326]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
BloodRaven,

DENSE_RANK()?
http://msdn.microsoft.com/ru-ru/library/ms189798(v=sql.120).aspx
27 янв 14, 14:36    [15475452]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
virtuOS
BloodRaven,

DENSE_RANK()?
http://msdn.microsoft.com/ru-ru/library/ms189798(v=sql.120).aspx
Это если только Вася не вернётся...
27 янв 14, 14:40    [15475492]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
BloodRaven
Member

Откуда:
Сообщений: 11
virtuOS
BloodRaven,

DENSE_RANK()?
http://msdn.microsoft.com/ru-ru/library/ms189798(v=sql.120).aspx


Это первое что пришло мне на ум, но оно работает не так как надо (или я его не правильно оформил). Надо уникальный N, а когда State и User повторяется где то дальше в выводе оно выводит ранг который уже встречался!
27 янв 14, 14:41    [15475501]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
Jaffar
Member

Откуда:
Сообщений: 633
select distinct TYPE from SB_BNFC
select t.NN, t2.State, t2.Date, t2.Name
from (select row_number() over(order by State, Name) NN, State, Name from (select distinct State, Name from table_q) t) t
join table_q t2 on t2.State = t.State and t2.Name  = t.Name
27 янв 14, 14:59    [15475648]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
BloodRaven
Member

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

Что то не то.
select distinct TYPE from SB_BNFC
не работает, а без него выдает неправильный результат (я подозреваю что и с
select distinct TYPE from SB_BNFC
не будет работать как надо)!
27 янв 14, 15:22    [15475871]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
iap
Member

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

можно было бы подумать, если б вопрос был оформлен как положено.
27 янв 14, 15:28    [15475923]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
Добрый Э - Эх
Guest
решение во многом зависит от версии сервера. На 2012 можно использовать так называемый start_of_group-метод посредством суперпозиции LEAD и накопительной суммы SUM() over(order by)
27 янв 14, 15:34    [15475963]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
BloodRaven
Member

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

Каждый раз когда в State или Name происходит изменение в N возрастает значение на 1. Это достаточно легко представить, а сделать не получается.
27 янв 14, 15:40    [15476013]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
BloodRaven
Member

Откуда:
Сообщений: 11
Добрый Э - Эх,

Круто! Но к сожалению работаю с 2008 сервером!
27 янв 14, 15:41    [15476024]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
BloodRaven
iap,

Каждый раз когда в State или Name происходит изменение в N возрастает значение на 1. Это достаточно легко представить, а сделать не получается.
Предлагается бросить всё и засесть за скрипты создания
таблиц и заполнение их данными с ваших рисунков?
Нет, так не пойдёт.
27 янв 14, 15:42    [15476050]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
Добрый Э - Эх
Guest
BloodRaven
Добрый Э - Эх,

Круто! Но к сожалению работаю с 2008 сервером!
тогда разбить строки на группы посредством разности двух разнооконных row_number-ов, в одном из которых указать секционирование по конкатенации полей State и User
27 янв 14, 15:51    [15476159]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
Добрый Э - Эх
Guest
сортировка, кстати, чем задается? Ибо на тех столбцах, что есть в примере, результат сортировки будет недетерминированным.
27 янв 14, 15:54    [15476194]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
BloodRaven
Member

Откуда:
Сообщений: 11
Вот:
if OBJECT_ID ('tempdb..#tt') is not null
drop table #tt
CREATE TABLE #tt (Stat int, Dat datetime, Nam varchar(30))
insert into #tt values  (1, '20140115 09:00:00','Vasya'),        
						(1,	'20140115 09:00:00','Vasya'),
						(1,	'20140115 09:00:00','Vasya'),
						(1,	'20140115 09:07:00','Vasya'),
						(1,	'20140115 09:09:00','Vasya'),
						(1,	'20140115 09:26:00','Vasya'),
						(2,	'20140115 09:27:00','Vasya'),
						(1,	'20140115 12:59:00','Vasya'),
						(1,	'20140115 12:59:00','Vasya'),
						(2,	'20140115 14:43:00','Vasya'),
						(1,	'20140115 09:06:00','Petya'),
						(1,	'20140115 09:06:00','Petya'),
						(1,	'20140115 09:06:00','Petya'),
						(2,	'20140115 15:20:00','Petya'),
						(1,	'20140115 16:32:00','Petya'),
						(1,	'20140115 16:32:00','Petya'),
						(1,	'20140115 16:32:00','Petya'),
						(1,	'20140115 16:43:00','Petya'),
						(2,	'20140115 16:43:00','Petya'),
						(1,	'20140115 12:59:00','Jora'),
						(1,	'20140115 13:16:00','Jora'),
						(2,	'20140115 13:16:00','Jora'),
						(1,	'20140115 14:32:00','Jora'),
						(1,	'20140115 14:32:00','Jora'),
						(1,	'20140115 14:32:00','Jora'),
						(1,	'20140115 14:43:00','Jora'),
						(2,	'20140115 14:43:00','Jora')
select * from #tt
27 янв 14, 16:07    [15476352]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
BloodRaven
Member

Откуда:
Сообщений: 11
Добрый Э - Эх,
Сортировка по Name, Date.
27 янв 14, 16:10    [15476374]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
Ответ в стиле вопроса
Guest
BloodRaven,

;WITH T AS
(
    SELECT *
          ,ROW_NUMBER() OVER(ORDER BY Nam DESC ,Dat) AS RN
    FROM   #tt
)
, t3 AS 
(
    SELECT t1.RN
          ,t1.Stat
          ,t1.Dat
          ,t1.Nam
          ,CASE 
                WHEN t1.Stat = t2.Stat
    OR t2.Stat IS NULL THEN 0 ELSE 1 END AS Stat2
       FROM T t1
       LEFT JOIN T t2
       ON t1.RN = t2.RN + 1
)
SELECT t1.RN
      ,t1.Stat
      ,t1.Dat
      ,t1.Nam
      ,1 + COUNT(t2.RN)
FROM   t3 t1
       LEFT JOIN t3 t2
            ON  t1.RN >= t2.RN
            AND t2.Stat2 = 1
GROUP BY
       t1.RN
      ,t1.Stat
      ,t1.Dat
      ,t1.Nam
ORDER BY
       t1.RN
27 янв 14, 16:35    [15476648]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
BloodRaven
Member

Откуда:
Сообщений: 11
Ответ в стиле вопроса,

Спасибо дружище, это как раз то что нужно! Начну разбираться! Ник "Ответ в стиле вопроса" как бы намекает, что это не самый лучший вариант, но это лучшее чем у меня 100%. Спасибо еще раз!
27 янв 14, 16:41    [15476705]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
BloodRaven
Добрый Э - Эх,
Сортировка по Name, Date.
Там есть одинаковые пары (Name, Date)
27 янв 14, 16:48    [15476774]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
BloodRaven
Member

Откуда:
Сообщений: 11
Ответ в стиле вопроса,

Хм, в рабочей базе не все так весело :( Десятки тысяч строк делают этот вариант не очень подходящим, но вариантов по лучше пока нету.
27 янв 14, 16:56    [15476849]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
if OBJECT_ID ('tempdb..#tt') is not null
drop table #tt
CREATE TABLE #tt (ID INT NOT NULL IDENTITY, Stat int, Dat datetime, Nam varchar(30))
insert into #tt(Stat, Dat, Nam)values
(1, '20140115 09:00:00','Vasya'),        
(1,	'20140115 09:00:00','Vasya'),
(1,	'20140115 09:00:00','Vasya'),
(1,	'20140115 09:07:00','Vasya'),
(1,	'20140115 09:09:00','Vasya'),
(1,	'20140115 09:26:00','Vasya'),
(2,	'20140115 09:27:00','Vasya'),
(1,	'20140115 12:59:00','Vasya'),
(1,	'20140115 12:59:00','Vasya'),
(2,	'20140115 14:43:00','Vasya'),
(1,	'20140115 09:06:00','Petya'),
(1,	'20140115 09:06:00','Petya'),
(1,	'20140115 09:06:00','Petya'),
(2,	'20140115 15:20:00','Petya'),
(1,	'20140115 16:32:00','Petya'),
(1,	'20140115 16:32:00','Petya'),
(1,	'20140115 16:32:00','Petya'),
(1,	'20140115 16:43:00','Petya'),
(2,	'20140115 16:43:00','Petya'),
(1,	'20140115 12:59:00','Jora'),
(1,	'20140115 13:16:00','Jora'),
(2,	'20140115 13:16:00','Jora'),
(1,	'20140115 14:32:00','Jora'),
(1,	'20140115 14:32:00','Jora'),
(1,	'20140115 14:32:00','Jora'),
(1,	'20140115 14:43:00','Jora'),
(2,	'20140115 14:43:00','Jora');

WITH
 T1 AS(SELECT N=ROW_NUMBER()OVER(ORDER BY ID)-ROW_NUMBER()OVER(PARTITION BY Stat,Nam ORDER BY ID),* FROM #tt)
,T2 AS(SELECT M=MIN(ID)OVER(PARTITION BY N,Stat,Nam),* FROM T1)
SELECT DENSE_RANK()OVER(ORDER BY M),ID,Stat,Dat,Nam
FROM T2
ORDER BY ID;
27 янв 14, 16:58    [15476871]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
iap
Member

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

короче, делайте в ЛЮБОЙ таблице PK.
Это - принцип! А принципы нарушать нехорошо.
27 янв 14, 16:59    [15476882]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
BloodRaven
Member

Откуда:
Сообщений: 11
iap,
Вот, то что нужно! 11000 строк обработало моментально. Спасибо.
27 янв 14, 17:20    [15477023]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь в построении запроса.  [new]
BloodRaven
Member

Откуда:
Сообщений: 11
iap,
Все, ежедневный отчет построен. Спс тебе огромное!!! Кстати, весьма хитрую штуку ты мне скинул, я ее еще не раз использую это точно.
28 янв 14, 12:38    [15480992]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить