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

Откуда:
Сообщений: 12
Здравствуйте, помогите, пожалуйста с решением:

Есть одна таблица, например
Регион Человек Оценка
Москва Иванов 10
Москва Петров 5
Москва Путенова 7
Москва Песковинская 9
Пермь Смирнов 5
Пермь Петров 10
Пермь Петрова 15
Пермь Смирнова 20
и тд.

Нужно вывести для каждого региона по 2 человека с наименьшими оценками, например
Регион Человек Оценка
Москва Петров 5
Москва Путенова 7
Пермь Смирнов 5
Пермь Петров 10
25 дек 17, 14:43    [21060569]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
АндрейИванов
Member

Откуда:
Сообщений: 64
GROUP BY
25 дек 17, 14:44    [21060572]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Ого, уже второй человек с похожей задачей.
Экзамены што-ле?
25 дек 17, 14:45    [21060581]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
iap
Member

Откуда: Москва
Сообщений: 46977
WITH CTE AS(SELECT *, N=ROW_NUMBER()OVER(PARTITION BY Регион ORDER BY Оценка))
SELECT Регион, Человек, Оценка
FROM CTE
WHERE N<3
ORDER BY Регион, Человек, Оценка;
25 дек 17, 15:08    [21060699]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
iap
Member

Откуда: Москва
Сообщений: 46977
iap
WITH CTE AS(SELECT *, N=ROW_NUMBER()OVER(PARTITION BY Регион ORDER BY Оценка))
SELECT Регион, Человек, Оценка
FROM CTE
WHERE N<3
ORDER BY Регион, Человек, Оценка;
Как обычно, забыл FROM
WITH CTE AS(SELECT *, N=ROW_NUMBER()OVER(PARTITION BY Регион ORDER BY Оценка) FROM Таблица)
SELECT Регион, Человек, Оценка
FROM CTE
WHERE N<3
ORDER BY Регион, Человек, Оценка;
25 дек 17, 15:12    [21060713]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
RC88
Member

Откуда:
Сообщений: 12
iap,
Спасибо большое, все получилось!
С наступающим новым годом=)
26 дек 17, 10:09    [21062222]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
Goga-Gola
Guest
RC88,

только экзаменатор может что-то заподозрить глядя на код :)
26 дек 17, 10:37    [21062325]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
Jaffar
Member

Откуда:
Сообщений: 633
какие CTE -только подзапросы
select t."Регион", t."Человек", t."Оценка"
from (select t.*, row_number() over(partition by t."Регион" order by t."Оценка" asc) NN
	  from Tabel t with(nolock) /**/ ) t
where
		t.NN in (1, 2)
order by 1, 2, 3
26 дек 17, 11:59    [21062676]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
TaPaK
Member

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

о, а вы поди думаете что это не одно и тоже?
26 дек 17, 12:04    [21062698]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
iap
Member

Откуда: Москва
Сообщений: 46977
TaPaK
Jaffar,

о, а вы поди думаете что это не одно и тоже?
Строго говоря, наверно это не одно и то же.
Небось это обрабатывается разными фрагментами кода сервера.
Ведь синтаксис производной таблицы исключает, к примеру, рекурсию.
26 дек 17, 12:44    [21062840]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
TaPaK
Member

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

если брать конкретно эти примеры, то лпан и обработка будут очень похожи, а скорее одинаоквы. Еси брать рекурсию, то там тоже ничего особого, кроме table spool со значениями рекурсии. Так что тут больше вопрос организации операторов рекурсии.
26 дек 17, 12:59    [21062892]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
iap
Member

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

если брать конкретно эти примеры, то лпан и обработка будут очень похожи, а скорее одинаоквы. Еси брать рекурсию, то там тоже ничего особого, кроме table spool со значениями рекурсии. Так что тут больше вопрос организации операторов рекурсии.
Вы видели программный код сервера?
В частности, относящийся к CTE и производной таблицы?
Уверен, что нет. Можем только гадать.
26 дек 17, 13:09    [21062933]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
TaPaK
Member

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

если брать конкретно эти примеры, то лпан и обработка будут очень похожи, а скорее одинаоквы. Еси брать рекурсию, то там тоже ничего особого, кроме table spool со значениями рекурсии. Так что тут больше вопрос организации операторов рекурсии.
Вы видели программный код сервера?
В частности, относящийся к CTE и производной таблицы?
Уверен, что нет. Можем только гадать.

ну нам доступен только план, на него и смотрим :)
26 дек 17, 13:20    [21062969]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
__Avenger__
Member

Откуда:
Сообщений: 1984
select top 2 with ties t.*
from Tabel t with(nolock)
order by row_number() over(partition by t."Регион" order by t."Оценка" asc), 1, 2, 3[/SRC]

?
27 дек 17, 09:41    [21065560]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
__Avenger__
select top 2 with ties t.*
[/SRC]

Ваш запрос всегда будет выдавать две записи. А вовсе не по две записи для каждого региона.
27 дек 17, 09:50    [21065579]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
__Avenger__
Member

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

Да, а если проверить? Про WITH TIES слышал?
27 дек 17, 09:53    [21065585]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
__Avenger__,

Конечено слышал. Он к данной задаче не имеет никакого отношения, так как ТС прости 2 человека, без детерминированности.
Сравните результат:
IF EXISTS (SELECT 1 FROM sysobjects WHERE name=N'Tabel' and type='U') DROP TABLE Tabel
CREATE TABLE Tabel (
  [Человек] nvarchar(50),
  [Регион] nvarchar(30),
  [Оценка] int
)
GO
DECLARE
  @I int = 10000
BEGIN TRAN
WHILE @I>0 BEGIN
  INSERT Tabel ([Человек], [Регион], [Оценка])
    VALUES (N'Name'+CONVERT(nvarchar(11),@I)+N' Middlename'+CONVERT(nvarchar(11),@I)+
	  N' Lastname'+CONVERT(nvarchar(11),@I),
	  N'Name'+CONVERT(nvarchar(11),CONVERT(int,RAND()/0.1*10)), CONVERT(int,RAND()/0.1*10)
	)
  SELECT @I=@I-1
END
COMMIT TRAN
GO

select top 2 with ties t.*
from Tabel t with(nolock)
order by row_number() over(partition by t."Регион" order by t."Оценка" asc), 1, 2, 3

SELECT [Регион], [Человек], [Оценка]
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY [Регион] ORDER BY [Оценка]) AS N FROM Tabel) Q
WHERE N<3
ORDER BY [Регион], [Человек], [Оценка]
27 дек 17, 11:17    [21065876]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
over,
Guest
ptr128
Сравните результат:
IF EXISTS (SELECT 1 FROM sysobjects WHERE name=N'Tabel' and type='U') DROP TABLE Tabel
CREATE TABLE Tabel (
  [Человек] nvarchar(50),
  [Регион] nvarchar(30),
  [Оценка] int
)
GO
DECLARE
  @I int = 10000
BEGIN TRAN
WHILE @I>0 BEGIN
  INSERT Tabel ([Человек], [Регион], [Оценка])
    VALUES (N'Name'+CONVERT(nvarchar(11),@I)+N' Middlename'+CONVERT(nvarchar(11),@I)+
	  N' Lastname'+CONVERT(nvarchar(11),@I),
	  N'Name'+CONVERT(nvarchar(11),CONVERT(int,RAND()/0.1*10)), CONVERT(int,RAND()/0.1*10)
	)
  SELECT @I=@I-1
END
COMMIT TRAN
GO

select top 2 with ties t.*
from Tabel t with(nolock)
order by (row_number() over(partition by t."Регион" order by t."Оценка" asc) - 1) / 2 --, 1, 2, 3

SELECT [Регион], [Человек], [Оценка]
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY [Регион] ORDER BY [Оценка]) AS N FROM Tabel) Q
WHERE N<3
ORDER BY [Регион], [Человек], [Оценка]
27 дек 17, 11:51    [21066071]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
iap
Member

Откуда: Москва
Сообщений: 46977
over,
ptr128
Сравните результат:
IF EXISTS (SELECT 1 FROM sysobjects WHERE name=N'Tabel' and type='U') DROP TABLE Tabel
CREATE TABLE Tabel (
  [Человек] nvarchar(50),
  [Регион] nvarchar(30),
  [Оценка] int
)
GO
DECLARE
  @I int = 10000
BEGIN TRAN
WHILE @I>0 BEGIN
  INSERT Tabel ([Человек], [Регион], [Оценка])
    VALUES (N'Name'+CONVERT(nvarchar(11),@I)+N' Middlename'+CONVERT(nvarchar(11),@I)+
	  N' Lastname'+CONVERT(nvarchar(11),@I),
	  N'Name'+CONVERT(nvarchar(11),CONVERT(int,RAND()/0.1*10)), CONVERT(int,RAND()/0.1*10)
	)
  SELECT @I=@I-1
END
COMMIT TRAN
GO

select top 2 with ties t.*
from Tabel t with(nolock)
orderby(row_number()over(partition byt."Регион" orderbyt."Оценка" asc)-1)/2 --, 1, 2, 3

SELECT [Регион], [Человек], [Оценка]
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY [Регион] ORDER BY [Оценка]) AS N FROM Tabel) Q
WHERE N<3
ORDER BY [Регион], [Человек], [Оценка]

Тогда уж надо TOP(1), а не TOP(2)
27 дек 17, 12:03    [21066130]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
iap
over,
пропущено...
Тогда уж надо TOP(1), а не TOP(2)

Ну я скопировал код один к одному, чтобы у автора не было претензий )
27 дек 17, 12:12    [21066151]     Ответить | Цитировать Сообщить модератору
 Re: Рейтинг TOP2 по условию  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
__Avenger__
ptr128,

Да, а если проверить? Про WITH TIES слышал?


Приношу извинения. Был не прав. Действительно детерменирование номера строки позволяет добиться требуемого результата.
27 дек 17, 12:13    [21066154]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить