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

Откуда:
Сообщений: 34
Уважаемые форумчане, добрый день
Прошу помочь, подсказать направление для решения такой задачи:

-- creating table
CREATE TABLE ToyotaModels
(
ID int NOT NULL,
CarModel varchar(20) NOT NULL,
Color varchar(20) NOT NULL,
Price money NOT NULL
primary key (ID, CarModel)
)

-- inserting test data
INSERT INTO ToyotaModels
VALUES
(1, 'Toyota Yaris', 'Red', 22000),
(2, 'Toyota Yaris', 'Blue', 22300),
(3, 'Toyota Yaris', 'Black', 21800),
(4, 'Toyota Yaris', 'White', 22350),
(5, 'Toyota Yaris', 'Brass', 20990),
(6, 'Toyota Yaris', 'Silver', 22100),
(1, 'Toyota Camry', 'Black', 34600),
(2, 'Toyota Camry', 'Red', 34200),
(3, 'Toyota Camry', 'White', 34250),
(4, 'Toyota Camry', 'Blue', 33870),
(5, 'Toyota Camry', 'Silver', 33600),
(6, 'Toyota Camry', 'Camel', 33900),
(1, 'Toyota Corolla', 'White', 25230),
(2, 'Toyota Corolla', 'Yellow', 25400),
(3, 'Toyota Corolla', 'Red', 25800),
(4, 'Toyota Corolla', 'Silver', 25100),
(5, 'Toyota Corolla', 'Black', 24900),
(6, 'Toyota Corolla', 'Apple Green', 25350),
(7, 'Toyota Corolla', 'Brass', 25700),
(8, 'Toyota Corolla', 'Aquamarine', 25900),
(9, 'Toyota Corolla', 'Green', 24770),
(10,'Toyota Corolla', 'Camel', 25320),
(11, 'Toyota Corolla', 'Dark blue', 25650),
(12, 'Toyota Corolla', 'Dark tan', 24950)


Требуется пронумеровать строки таблицы так, чтобы сначала шли первые значения ID для моделей Toyota Yaris, Toyota Camry, Toyota Corolla, затем последние, затем опять первые и т. д. Результат должен быть таким, как на скриншоте.

К сообщению приложен файл. Размер - 110Kb
25 апр 18, 10:52    [21367036]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2395
Гордон Шамуэй,

order by case CarModel  when 'Toyota'  then 1 when 'Lada' then 10 else 15 end
25 апр 18, 11:12    [21367091]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
StarikNavy
Гордон Шамуэй,

order by case CarModel  when 'Toyota'  then 1 when 'Lada' then 10 else 15 end

главно никогда не читать того что просят
25 апр 18, 11:13    [21367093]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20527
Гордон Шамуэй
чтобы сначала шли первые значения ID для моделей Toyota Yaris, Toyota Camry, Toyota Corolla, затем последние, затем опять первые и т. д.

Формально это в рамках одной группы (модели) соответствует такой сортировке:
ORDER BY ABS(min_id+max_id-2*curr_id) DESC, curr_id ASC

Если реверсировать первое выражение, и добавить сортировку по модели - получится то, что требуется.
25 апр 18, 11:29    [21367146]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Гордон Шамуэй,

With cte as (
Select *,
row_number() over (Partition by CarModel Order by ID) as rn,
row_number() over (Partition by CarModel Order by ID desc) as rn_desc
 From #ToyotaModels)
Select * From cte
Order by case When rn<rn_desc then rn else rn_desc end,
rn
25 апр 18, 11:38    [21367176]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
Konst_One
Member

Откуда:
Сообщений: 11517
не взлетит
25 апр 18, 11:41    [21367189]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
tip78
Member

Откуда: Москва
Сообщений: 986
думаю такой индекс будет только мешать:
автор
primary key (ID, CarModel)
25 апр 18, 11:45    [21367205]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Akina
Гордон Шамуэй
чтобы сначала шли первые значения ID для моделей Toyota Yaris, Toyota Camry, Toyota Corolla, затем последние, затем опять первые и т. д.

Формально это в рамках одной группы (модели) соответствует такой сортировке:
ORDER BY ABS(min_id+max_id-2*curr_id) DESC, curr_id ASC

Если реверсировать первое выражение, и добавить сортировку по модели - получится то, что требуется.

DECLARE @Order TABLE(Id INT IDENTITY, Code VARCHAR(255) )
INSERT INTO @Order(Code)
VALUES ('Toyota Yaris'), ('Toyota Camry'), ('Toyota Corolla')

;WITH x as 
(
	SELECT  a.*, 
		MIN(a.Id) OVER (PARTITION BY CarModel) as min_id,
		MAX(a.Id) OVER (PARTITION BY CarModel) as max_id,
		b.Id as rnk
	FROM 
		ToyotaModels	a
	INNER JOIN
		@Order		b
	ON
		b.Code	= a.CarModel
),
y as 
(
SELECT 
*,
b= ROW_NUMBER() OVER (PARTITION BY CarModel ORDER BY rnk ,ABS(min_id+max_id-2*id)  DESC, Id ASC )
FROM x
)
SELECT 
	No = ROW_NUMBER() OVER (ORDER BY b,rnk),
	Id,
	CarModel,
	Color,
	Price
FROM y
ORDER BY b,rnk 

и в примере, как я понимаю ошибка в последних записях
25 апр 18, 11:48    [21367214]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
Konst_One
Member

Откуда:
Сообщений: 11517
сортировка кастомная должна быть :
order by ID, 
case CarModel  
when 'Toyota Yaris' then 1
when 'Toyota Camry' then 2
when 'Toyota Corolla' then 3
else 0 end


ну и далее сами
25 апр 18, 11:50    [21367222]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Konst_One
сортировка кастомная должна быть :
order by ID, 
case CarModel  
when 'Toyota Yaris' then 1
when 'Toyota Camry' then 2
when 'Toyota Corolla' then 3
else 0 end


ну и далее сами

ценнейшая рекомендайция!

автор
думаю такой индекс будет только мешать:
автор
primary key (ID, CarModel)

какой вы видите PK ?

автор
With cte as (
Select *,
row_number() over (Partition by CarModel Order by ID) as rn,
row_number() over (Partition by CarModel Order by ID desc) as rn_desc
From #ToyotaModels)
Select * From cte
Order by case When rn<rn_desc then rn else rn_desc end,
rn

ТЗ! какое ТЗ?
25 апр 18, 11:51    [21367232]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
tip78
Member

Откуда: Москва
Сообщений: 986
я не знаю, как там в mssql, но вообще можно каждую модель засунуть в свою колонку и просто строками потом выбрать - 1,2,3 с каждой строки
25 апр 18, 11:51    [21367233]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
я так понимаю студенты дипломы получили, или откуда такое нашествие ада
25 апр 18, 11:52    [21367236]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
TaPaK,

Будет работать в случае непрерывной последовательности "ToyotaModels.ID". При пропусках результат может быть некорректный.
Следует заменить
....
1 as min_id,
count(*) OVER (PARTITION BY CarModel) as max_id,
ROW_number() Over (PARTITION BY CarModel Order by ID) as rn
.....
...ABS(min_id+max_id-2*rn),...
....
25 апр 18, 11:59    [21367273]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
nullin
Member

Откуда: pullin
Сообщений: 174
Kopelly, как вариант, при пропусках тоже ок:

select 
row_number() over(order by case
                           when a.r < a.q - a.r + 1 then 2 * a.r - 1
                           when a.r = a.q - a.r + 1 then 2 * (a.q - a.r) + 1
                           when a.r > a.q - a.r + 1 then 2 * (a.q - a.r + 1)
                           end,
                           case a.CarModel  
                           when 'Toyota Yaris'   then 1
                           when 'Toyota Camry'   then 2
                           when 'Toyota Corolla' then 3
                           else                       0 
                           end) as rng,
a.ID, a.CarModel, a.Color, a.Price                          
from (select t.ID, t.CarModel, t.Color, t.Price,
             row_number() over(partition by t.CarModel order by t.ID) as r,
             sum(1) over(partition by t.CarModel) as q
from ToyotaModels as t) as a
25 апр 18, 13:32    [21367649]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
nullin
Member

Откуда: pullin
Сообщений: 174
nullin, чтоб, сортировку первую убрать там еще индекс просится по ID, CarModel
25 апр 18, 13:44    [21367695]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
nullin
nullin, чтоб, сортировку первую убрать там еще индекс просится по ID, CarModel

OMG

автор
primary key (ID, CarModel)

или как-то покрасивее?
25 апр 18, 13:46    [21367701]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
Glebanski
Member

Откуда: Msk ->NL
Сообщений: 308
ИМХО надо в консерватории что-нить подправить

CREATE TABLE ToyotaModels
(
ID int NOT NULL IDENTITY(1,1) primary key,
CarModel varchar(20) NOT NULL,
Color varchar(20) NOT NULL,
Price money NOT NULL,
OrdNum int
)
ALTER TABLE ToyotaModels ADD  CONSTRAINT  UX_model_Color UNIQUE(CarModel,Color );
create index ix_OrdNum on ToyotaModels  (OrdNum );
25 апр 18, 13:52    [21367720]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
nullin
Member

Откуда: pullin
Сообщений: 174
TaPaK, Ок вопрос как к гуру:
scan+sort: http://sqlfiddle.com/#!18/f0c17/1
seek: http://sqlfiddle.com/#!18/4391d/1
Что лучше?
25 апр 18, 13:53    [21367724]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
Glebanski
Member

Откуда: Msk ->NL
Сообщений: 308
Кроме того,
Google
Toyota Motor Company owns:
Lexus,
Scion,
Daihatsu and
Hino Motors, with a stake in Fuji Industries (Subaru's parent company)
and Isuzu


как вы себе представляете БД где на каждый бренд одна таблица? Адская ересь
25 апр 18, 13:56    [21367738]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
nullin
Member

Откуда: pullin
Сообщений: 174
Glebanski, это ТС троллит
25 апр 18, 13:58    [21367746]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
nullin
TaPaK, Ок вопрос как к гуру:
scan+sort: http://sqlfiddle.com/#!18/f0c17/1
seek: http://sqlfiddle.com/#!18/4391d/1
Что лучше?

это вы лукап в seek записали? :)
25 апр 18, 13:58    [21367747]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
nullin
Member

Откуда: pullin
Сообщений: 174
TaPaK, Вы не ответили на вопрос)))
25 апр 18, 14:01    [21367755]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
Glebanski
Member

Откуда: Msk ->NL
Сообщений: 308
nullin
Glebanski, это ТС троллит

Блин, вот я лох...
25 апр 18, 14:02    [21367758]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2384
Блог
TaPaK,

;with a as (select max(id) as maxid, CarModel from ToyotaModels group by CarModel)
select tm.* from ToyotaModels tm join a on tm.CarModel = a.CarModel
order by case when tm.id % 2 = 1 then tm.id else a.maxid-tm.id+2 end, tm.CarModel
25 апр 18, 14:04    [21367764]     Ответить | Цитировать Сообщить модератору
 Re: Пронумеровать строки по определенному алгоритму  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2384
Блог
Точнее
;with a as (select max(id) as maxid, CarModel from ToyotaModels group by CarModel),
b as (select max(maxid) as mm from a
union all
select mm-1 as mm from b where mm > 1)
select tm.* from ToyotaModels tm join a on tm.CarModel = a.CarModel join b on b.mm = case when b.mm %2 = 1 then (tm.id-1)*2+1 else (a.maxid - tm.id + 1)*2 end
order by b.mm, tm.CarModel
25 апр 18, 14:15    [21367810]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить