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

Откуда:
Сообщений: 517
День добрый!
Есть таблица типа: продавец, клиент, N
нужно пронумировать строки(задать N) так, чтоб для каждого продавца N нaчинался с единицы.
create table #t (sales_person_id int, customer varchar(10)null, n int null) 
insert into #t (sales_person_id,customer)values(1,'aaa')
insert into #t (sales_person_id,customer)values(33,'bbb')
insert into #t (sales_person_id,customer)values(258,'ccc')
insert into #t (sales_person_id,customer)values(456,'ddd')
insert into #t (sales_person_id,customer)values(1,'sss')
insert into #t (sales_person_id,customer)values(258,'eee')
insert into #t (sales_person_id,customer)values(1,'lll')
insert into #t (sales_person_id,customer)values(456,'mmm')
insert into #t (sales_person_id,customer)values(1,'kkk')
select * from #t order by 1,2

Должно быть так:
sales_person_id customer N
1aaa1
1kkk2
1lll3
1sss4
33bbb1
258ccc1
258eee2
456ddd1
456mmm2
28 июл 09, 17:19    [7470408]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
abrashka
Member

Откуда:
Сообщений: 517
Сорри, забыл сказать, что речь идет об SQL 2000
28 июл 09, 17:20    [7470421]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
helloword
Member

Откуда:
Сообщений: 686
курсор?
28 июл 09, 17:39    [7470551]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
iap
Member

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

а где первичный ключ? Добавьте.
28 июл 09, 17:39    [7470555]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
iap
Member

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

а где первичный ключ? Добавьте.
И расскажите, какое поле будет задавать порядок нумерации
28 июл 09, 17:40    [7470561]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
helloword
Member

Откуда:
Сообщений: 686
на 2000 я б наворотил)
create table #t (sales_person_id int, customer varchar(10)null, n int null) 
insert into #t (sales_person_id,customer)values(1,'aaa')
insert into #t (sales_person_id,customer)values(33,'bbb')
insert into #t (sales_person_id,customer)values(258,'ccc')
insert into #t (sales_person_id,customer)values(456,'ddd')
insert into #t (sales_person_id,customer)values(1,'sss')
insert into #t (sales_person_id,customer)values(258,'eee')
insert into #t (sales_person_id,customer)values(1,'lll')
insert into #t (sales_person_id,customer)values(456,'mmm')
insert into #t (sales_person_id,customer)values(1,'kkk')

declare @Pos int, @sales_person_id int, @customer varchar(200), @sales_person_id_old int

set @Pos = 1

declare _cursor cursor fast_forward for
 select sales_person_id, customer from #t order by 1,2

open _cursor
 fetch next from _cursor into @sales_person_id, @customer
   while @@Fetch_Status = 0
    begin
 
       if @sales_person_id_old != @sales_person_id set @Pos = 1

        set @sales_person_id_old = @sales_person_id
        select @sales_person_id, @customer, @Pos
        set @Pos = @Pos + 1

     fetch next from _cursor into @sales_person_id, @customer
    end
close _cursor
deallocate _cursor

drop table #t

28 июл 09, 17:44    [7470597]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
abrashka
Member

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

первичный ключ может быть sales_person_id и customer, такие связки не должны повторяться, у каждого покупателя только один продавец

не понял, что значит: "какое поле будет задавать порядок нумерации?"

для каждого продавца клиенты начинаются с единицы, порядок клиентов не имеет значения, но можно клиентов тоже отсортировать.

Например у продавца "1" есть 4 клиента, значит N в ефо случае будет от 1 до 4, далее продавец 33, у него один покупатель, т.e. N будет 1

Понимаю что нужно сделать курсор по продавцам и для каждого продавца определить N в зависимости от клиентов, но что-то не получается :(
28 июл 09, 17:50    [7470643]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
так можна
Guest
select count(*) As n, a.sales_person_id, a.customer
from #t a
inner join #t b
  on     a.sales_person_id = b.sales_person_id 
    and  a.customer <= b.customer
group by a.sales_person_id, a.customer
order by a.sales_person_id, n
28 июл 09, 17:55    [7470673]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
abrashka
Member

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

Спасибо огромное! Это именно то что нужно.
На самом деле таблица уже есть, нужно переодически задавать новые номера.
Т.е. нужно сделать UPDATE при момощи Вашего кода
28 июл 09, 17:56    [7470680]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Без гарантии воспроизводимости:

create table #t (sales_person_id int NOT NULL, customer varchar(10)NOT null, n int null, PRIMARY KEY CLUSTERED(sales_person_id, customer)) 

...

DECLARE 
  @RowNum int,
  @id	int
SET @RowNum = 0

UPDATE
  #t
SET
  @RowNum = n = CASE WHEN @id <> sales_person_id THEN 1 ELSE @RowNum + 1 END,
  @id = sales_person_id 

Лучше апдейтить приведенной выше выборкой.
28 июл 09, 18:00    [7470699]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Или можно вот так
USE [tempdb]
GO

create table #t (sales_person_id int, customer varchar(10)null, n int null) 
insert into #t (sales_person_id,customer)values(1,'aaa')
insert into #t (sales_person_id,customer)values(33,'bbb')
insert into #t (sales_person_id,customer)values(258,'ccc')
insert into #t (sales_person_id,customer)values(456,'ddd')
insert into #t (sales_person_id,customer)values(1,'sss')
insert into #t (sales_person_id,customer)values(258,'eee')
insert into #t (sales_person_id,customer)values(1,'lll')
insert into #t (sales_person_id,customer)values(456,'mmm')
insert into #t (sales_person_id,customer)values(1,'kkk')

SELECT
	(SELECT COUNT(*)	FROM [#t] tt WHERE tt.[sales_person_id] < t.[sales_person_id]) 
	 + (SELECT COUNT(*)	FROM [#t] tt WHERE tt.[sales_person_id] = t.[sales_person_id] AND tt.[customer] > t.[customer]) 
	+ 1 AS I,
	*
FROM [#t] t
ORDER BY 1


DROP TABLE [#t]
28 июл 09, 18:02    [7470715]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
тагда так
Guest
abrashka
...
Т.е. нужно сделать UPDATE ...


update #t
set n = (select c.n from (
                        select count(*) As n, a.sales_person_id, a.customer
                        from #t a
                        inner join #t b
                          on     a.sales_person_id = b.sales_person_id 
                            and  a.customer <= b.customer
                        group by a.sales_person_id, a.customer) c
         where  c.sales_person_id = #t.sales_person_id
           and  c.customer = #t.customer)
28 июл 09, 18:05    [7470730]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
тагда так
Guest
тагда так

если это
abrashka
....

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


соответствует действительности
28 июл 09, 18:08    [7470739]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Ну а если UPDATE, то "можна и так" :)
USE [tempdb]
GO

create table #t (sales_person_id int, customer varchar(10)null, n int null) 
insert into #t (sales_person_id,customer)values(1,'aaa')
insert into #t (sales_person_id,customer)values(33,'bbb')
insert into #t (sales_person_id,customer)values(258,'ccc')
insert into #t (sales_person_id,customer)values(456,'ddd')
insert into #t (sales_person_id,customer)values(1,'sss')
insert into #t (sales_person_id,customer)values(258,'eee')
insert into #t (sales_person_id,customer)values(1,'lll')
insert into #t (sales_person_id,customer)values(456,'mmm')
insert into #t (sales_person_id,customer)values(1,'kkk')

UPDATE t SET 
	[n] = A.I
FROM [#t] t
	JOIN (
		SELECT
			(SELECT COUNT(*)	FROM [#t] tt WHERE tt.[sales_person_id] < t.[sales_person_id]) 
			 + (SELECT COUNT(*)	FROM [#t] tt WHERE tt.[sales_person_id] = t.[sales_person_id] AND tt.[customer] > t.[customer]) 
			+ 1 AS I,
			*
		FROM [#t] t
	) A ON t.[customer] = A.[customer] AND t.[sales_person_id] = A.[sales_person_id]

SELECT * FROM [#t] ORDER BY 3

DROP TABLE [#t]
28 июл 09, 18:11    [7470757]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Очепятался
UPDATE t SET 
	[n] = A.I
FROM [#t] t
	JOIN (
		SELECT
			(SELECT COUNT(*) FROM [#t] tt WHERE tt.[sales_person_id] < t.[sales_person_id]) 
			 + (SELECT COUNT(*) FROM [#t] tt WHERE tt.[sales_person_id] = t.[sales_person_id] AND tt.[customer] < t.[customer]) 
			+ 1 AS I,
			*
		FROM [#t] t
	) A ON t.[customer] = A.[customer] AND t.[sales_person_id] = A.[sales_person_id]
28 июл 09, 18:20    [7470781]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
abrashka
Member

Откуда:
Сообщений: 517
Всем спасибо!
Для описанной мною задачи все скрипты подходят, но я немного упростил ситуацию, на самом деле
кроме клиента есть еще и страна, например клиент 'ааа' может быть и в России и в Англии
тогда первичный ключ будет состоять из продавец, клиент, страна
т.е. может быть ситуация, что у клиентов с одним номером из разных стран есть разные продавцы, сорри за неточность.
Моя таблица выглядит так:

create table #t (sales_person_id int, customer varchar(10), subsidiary_key varchar(10), n int null) 
insert into #t (sales_person_id,customer,subsidiary_key)values(1,'aaa','chn')
insert into #t (sales_person_id,customer,subsidiary_key)values(33,'bbb','usa')
insert into #t (sales_person_id,customer,subsidiary_key)values(258,'ccc','usa')
insert into #t (sales_person_id,customer,subsidiary_key)values(456,'ddd','kaz')
insert into #t (sales_person_id,customer,subsidiary_key)values(1,'sss','chn')
insert into #t (sales_person_id,customer,subsidiary_key)values(258,'eee','kaz')
insert into #t (sales_person_id,customer,subsidiary_key)values(1,'lll','ukr')
insert into #t (sales_person_id,customer,subsidiary_key)values(456,'mmm','swi')
insert into #t (sales_person_id,customer,subsidiary_key)values(1,'kkk','usa')
insert into #t (sales_person_id,customer,subsidiary_key)values(1,'aaa','bel')

select * from #t order by 1,2

1aaachnNULL
1aaabelNULL
1kkkusaNULL
1lllukrNULL
1ssschnNULL
33bbbusaNULL
258cccusaNULL
258eeekazNULL
456dddkazNULL
456mmmswiNULL
28 июл 09, 18:24    [7470791]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
Senya_L
Member

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

И в чем проблема?
USE [tempdb]
GO

create table #t (sales_person_id int, customer varchar(10), subsidiary_key varchar(10), n int null) 
insert into #t (sales_person_id,customer,subsidiary_key)values(1,'aaa','chn')
insert into #t (sales_person_id,customer,subsidiary_key)values(33,'bbb','usa')
insert into #t (sales_person_id,customer,subsidiary_key)values(258,'ccc','usa')
insert into #t (sales_person_id,customer,subsidiary_key)values(456,'ddd','kaz')
insert into #t (sales_person_id,customer,subsidiary_key)values(1,'sss','chn')
insert into #t (sales_person_id,customer,subsidiary_key)values(258,'eee','kaz')
insert into #t (sales_person_id,customer,subsidiary_key)values(1,'lll','ukr')
insert into #t (sales_person_id,customer,subsidiary_key)values(456,'mmm','swi')
insert into #t (sales_person_id,customer,subsidiary_key)values(1,'kkk','usa')
insert into #t (sales_person_id,customer,subsidiary_key)values(1,'aaa','bel')

INSERT INTO [#t] SELECT * FROM [#t]

CREATE INDEX IX_1 ON [#t](sales_person_id) include(customer, subsidiary_key)
CREATE INDEX IX_2 ON [#t](customer) include(sales_person_id, subsidiary_key)
CREATE INDEX IX_3 ON [#t](sales_person_id,customer, subsidiary_key)


UPDATE t SET 
	[n] = A.I
FROM [#t] t
	JOIN (
		SELECT
			(SELECT COUNT(*) FROM [#t] tt WHERE tt.[sales_person_id] < t.[sales_person_id]) 
			 + (SELECT COUNT(*) FROM [#t] tt WHERE tt.[sales_person_id] = t.[sales_person_id] AND tt.[customer] < t.[customer]) 
			 + (SELECT COUNT(*) FROM [#t] tt WHERE tt.[sales_person_id] = t.[sales_person_id] AND tt.[customer] = t.[customer] AND tt.[subsidiary_key] < t.[subsidiary_key]) 
			+ 1 AS I,
			*
		FROM [#t] t
	) A ON t.[customer] = A.[customer] AND t.[sales_person_id] = A.[sales_person_id]

SELECT * FROM [#t] ORDER BY 4


DROP TABLE [#t]
На более чем 320000 записях отрабатывает за 5 сек
28 июл 09, 18:37    [7470827]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
abrashka
Member

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

Спасибо, но так не правильно, клиенты с одним и тем же именем но в разных странах- это разные клиенты, должно быть так:

select * from #t order by 1,2,3
1aaabel1
1aaachn2
1kkkusa3
1lllukr4
1ssschn5
33bbbusa1
258cccusa1
258eeekaz2
456dddkaz1
456mmmswi2
28 июл 09, 18:46    [7470856]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
abrashka
Member

Откуда:
Сообщений: 517
По советуhelloword-a воспользовался курсором, немного изменил код из-за "страны" клиента.
Но мне почему-то кажется, что такое использование не будет эффективным, может можно как-то изменить UPDATE?

-- Create Table
create table #t (sales_person_id int, customer varchar(10)null, subsidiary_key varchar(10) null,n int null) 
insert into #t (sales_person_id,customer,subsidiary_key)values(1,'aaa','chn')
insert into #t (sales_person_id,customer,subsidiary_key)values(33,'bbb','usa')
insert into #t (sales_person_id,customer,subsidiary_key)values(258,'ccc','usa')
insert into #t (sales_person_id,customer,subsidiary_key)values(456,'ddd','kaz')
insert into #t (sales_person_id,customer,subsidiary_key)values(1,'sss','chn')
insert into #t (sales_person_id,customer,subsidiary_key)values(258,'eee','kaz')
insert into #t (sales_person_id,customer,subsidiary_key)values(1,'lll','ukr')
insert into #t (sales_person_id,customer,subsidiary_key)values(456,'mmm','swi')
insert into #t (sales_person_id,customer,subsidiary_key)values(1,'kkk','usa')
insert into #t (sales_person_id,customer,subsidiary_key)values(1,'aaa','bel')


-- Update Table
declare @Pos int, @sales_person_id int, @customer varchar(200), @subsidiary_key varchar(20),@sales_person_id_old int

set @Pos = 1
declare _cursor cursor fast_forward for
 select sales_person_id, customer,subsidiary_key from #t order by 1,2,3
open _cursor
 fetch next from _cursor into @sales_person_id, @customer,@subsidiary_key
   while @@Fetch_Status = 0
    begin
        if @sales_person_id_old != @sales_person_id set @Pos = 1
        set @sales_person_id_old = @sales_person_id
/******************** UPDATE ******************/
	update #t
	set n=@pos
	 	where sales_person_id=@sales_person_id
	 	and customer=@customer
		and subsidiary_key=@subsidiary_key
        set @Pos = @Pos + 1
/************************************************/
     fetch next from _cursor into @sales_person_id, @customer,@subsidiary_key
    end
close _cursor
deallocate _cursor


28 июл 09, 19:05    [7470913]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
abrashka
Senya_L,

Спасибо, но так не правильно, клиенты с одним и тем же именем но в разных странах- это разные клиенты
Вы нигде не сказали, что нумерация должна сбрасываться для каждой страны.

Ну а если подумать?
UPDATE t SET 
	[n] = A.I
FROM [#t] t
	JOIN (
		SELECT
			(SELECT COUNT(*) FROM [#t] tt WHERE tt.[subsidiary_key] = t.[subsidiary_key] AND tt.[sales_person_id] < t.[sales_person_id]) 
			 + (SELECT COUNT(*) FROM [#t] tt WHERE tt.[subsidiary_key] = t.[subsidiary_key] AND tt.[sales_person_id] = t.[sales_person_id] AND tt.[customer] < t.[customer]) 
			+ 1 AS I,
			*
		FROM [#t] t
	) A ON t.[customer] = A.[customer] AND t.[sales_person_id] = A.[sales_person_id]
28 июл 09, 19:05    [7470914]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
abrashka
Member

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

Спасибо огромное!
Прошу прощения, я на самом деле не провильно изложил вопрос, думал упростить его, получить идею на форуме, а дальше думать самому...
На самом деле ситуация такая, речь идет о продавцах, клиентах и странах клиентов.
Код клиената может быть одинаковым, но если они находятся в разных странах, то для нас это разные клиенты. У них могут быть разные продавцы, но может быть и один продавец. Грубо говоря для каждого продавца N не может повторяться. Например в ситуации с продавцом "1" и клиентом "ааа" в Англии N будет 1, а для того же продавца и клиента с тем же кодом, но например в Китае- код уже не может быть 1. Еслиб речь шла о другом продавце, то тогда могло быть 1
28 июл 09, 19:13    [7470929]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Непонятно, почему курсор для упдейта не упдейтится?
declare _cursor cursor for
 select sales_person_id, customer,subsidiary_key from #t order by 1,2,3
 for update of n
Для него не проходит для него
update ... where current of...
Это запретили в 2005SP3 или я чего-то недообъявил?
28 июл 09, 19:22    [7470943]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
abrashka
Member

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

Странно, приведенный мной выше код с курсором работает на SQL 2000
28 июл 09, 19:28    [7470954]     Ответить | Цитировать Сообщить модератору
 Re: запросом по нумерации строк  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
abrashka
Senya_L,

Странно, приведенный мной выше код с курсором работает на SQL 2000
У Вас там нет такого
update ... where current of...
28 июл 09, 20:45    [7471105]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить