случайное соединение в SQL Server с различным количеством случайных результатов

SQL в SQL Server стал настолько умным, что то, что кажется требующим процедурного решения, часто можно сделать с помощью чистого SQL. Мне интересно, это один из таких случаев.
Допустим, у нас есть таблица STATES и таблица CITIES.

STATES:
State:  NY

CITIES
State: NY
City:  Armonk

Теперь давайте усложним ситуацию с третьей таблицей: ИНСТРУКЦИИ

INSTRUCTIONS
State: NY
HowMany: 17

State: NJ
HowMany: 11

Есть ли способ в SQL Server SQL выбрать HowMany города случайным образом из таблицы CITIES, когда три таблицы объединены в State?

Мы не знаем "top N" заранее. Он меняется в зависимости от штата.

Конечно, в таблице «Штаты» будут все 50 штатов, в таблице «Города» — все города в каждом штате, а в «Инструкциях» будет по одной записи для каждого штата, определяющей, сколько городов из этого штата необходимо (выбирается случайным образом).

P.S. Пример желаемых результатов (при условии, что инструкция для Нью-Йорка — HowMany=5, а инструкция для Нью-Джерси — HowMany = 4 и order by STATES.state):

NJ.....Princeton
NJ.....Newark
NJ.....Camden
NJ.....Princeton
NY.....Armonk
NY.....Schenectady
NY.....White Plains
NY.....Niagara Falls
NY.....Rochester

random sql-server-2012 top-n
person Tim    schedule 19.07.2017    source источник
comment
Не могли бы вы опубликовать образец желаемого результата?   -  person Mehrad Eslami    schedule 20.07.2017
comment
Инструкции будут иметь одну запись для каждого состояния. Тогда зачем использовать две таблицы? HowMany можно было бы переместить из INSTRUCTIONS в таблицу STATES.   -  person Kjetil S.    schedule 20.07.2017
comment
@Kjetil S. Согласен, но на данный момент нет необходимости проводить рефакторинг.   -  person Tim    schedule 20.07.2017

Ответы (3)


arrow_upward
1
arrow_downward

Я считаю, что функция RAND(), используемая в других ответах, вызывает некоторые проблемы, поскольку не является новым случайным числом для каждой строки.

КОНТРОЛЬНАЯ СУММА(NEWID()) хорошо сработала для меня в этом сценарии. (См. RAND не отличается для каждой строки в T-SQL. ОБНОВЛЕНИЕ)

Я думаю, что это решение красивое и аккуратное:

SELECT
RandomCities.[State]
,[RandomCities].City
FROM
    (
        SELECT 
        s.[state]
        ,city
        ,ROW_NUMBER() OVER (PARTITION BY s.[State] ORDER BY CHECKSUM(NEWID())) AS [RandomOrder]
        FROM
        States s
        INNER JOIN Cities c ON c.[state]=s.[state]
    ) AS RandomCities
INNER JOIN instructions i ON i.[state]=RandomCities.[state]
WHERE RandomCities.RandomOrder<=i.HowMany
person Max xaM    schedule 19.07.2017
comment
Красиво и аккуратно. - person Tim; 20.07.2017

arrow_upward
0
arrow_downward

РАЗЛИЧНЫЙ, чем другой ответ, использующий запрос, который не использует DDL (язык определения данных)

SET @row_num2= 0;
SELECT *,@row_num2 := @row_num2+1 as rownum2 FROM (SELECT States.State,Cities.City,Instructions.HowMany
from States,Cities, Instructions
WHERE States.State = Cities.State and States.State = Instructions.State
ORDER BY RAND()) as t HAVING rownum2 >= t.HowMany

http://sqlfiddle.com/#!9/b96d3b/37

person Joshua Klein    schedule 19.07.2017

arrow_upward

arrow_downward

person    schedule
comment
Ошибка: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. - person Tim; 20.07.2017