Вопрос:
Мне удалось создать простой запрос, который выбирает случайное имя и фамилию и вставляет их в таблицу результатов. Я хотел создать что-то, что я мог бы обменять с различными тестами, которые я запускал, где мне нужно было создавать много данных. Вот код (я включил только 5 первых и последних имен для целей простоты):
SELECT FirstName, LastName FROM (SELECT TOP 1 FirstName FROM (SELECT ‘John’ AS FirstName UNION SELECT ‘Tim’ AS FirstName UNION SELECT ‘Laura’ AS FirstName UNION SELECT ‘Jeff’ AS FirstName UNION SELECT ‘Sara’ AS FirstName) AS First_Names ORDER BY NEWID()) n1 FULL OUTER JOIN (SELECT TOP 1 LastName FROM (SELECT ‘Johnson’ AS LastName UNION SELECT ‘Hudson’ AS LastName UNION SELECT ‘Jackson’ AS LastName UNION SELECT ‘Ranallo’ AS LastName UNION SELECT ‘Curry’ AS LastName) AS Last_Names ORDER BY NEWID()) n2 ON [n1].FirstName = [n2].LastName WHERE n1.FirstName IS NOT NULL OR n2.LastName IS NOT NULL
Вот результаты:
FirstName LastName NULL Hudson John NULL
Я хочу, чтобы результаты возвращали одну строку с первым и фамильным именем, случайным образом сгенерированным так, чтобы каждая строка имела полное имя (без значений NULL). Я уверен, что это что-то простое, я пропускаю.
Лучший ответ:
Следующий код позволит вам генерировать ряд случайных имен, в которых решение перекрестного соединения допускает только одно за раз. Не уверен, что вам нужно сделать это несколько раз, но если вы это сделаете:
create table #table (firstname varchar(50), lastname varchar(50)) declare @counter int = 1 declare @max int = 5 —set number of repetitions here declare @a varchar(50) declare @b varchar(50) while @counter <= @max begin SET @a = (SELECT TOP 1 FirstName FROM (SELECT ‘John’ AS FirstName UNION SELECT ‘Tim’ AS FirstName UNION SELECT ‘Laura’ AS FirstName UNION SELECT ‘Jeff’ AS FirstName UNION SELECT ‘Sara’ AS FirstName) AS First_Names ORDER BY NEWID()) SET @b = (SELECT TOP 1 LastName FROM (SELECT ‘Johnson’ AS LastName UNION SELECT ‘Hudson’ AS LastName UNION SELECT ‘Jackson’ AS LastName UNION SELECT ‘Ranallo’ AS LastName UNION SELECT ‘Curry’ AS LastName) AS Last_Names ORDER BY NEWID()) insert into #table values (@a, @b) set @counter = @counter + 1 end select * from #table Ответ №1
Проблема в вашем соединении. Так вы можете это сделать:
SELECT FirstName, LastName FROM (SELECT TOP 1 FirstName FROM (SELECT ‘John’ AS FirstName UNION SELECT ‘Tim’ AS FirstName UNION SELECT ‘Laura’ AS FirstName UNION SELECT ‘Jeff’ AS FirstName UNION SELECT ‘Sara’ AS FirstName) AS First_Names ORDER BY NEWID())n1 CROSS JOIN (SELECT TOP 1 LastName FROM (SELECT ‘Johnson’ AS LastName UNION SELECT ‘Hudson’ AS LastName UNION SELECT ‘Jackson’ AS LastName UNION SELECT ‘Ranallo’ AS LastName UNION SELECT ‘Curry’ AS LastName) AS Last_Names ORDER BY NEWID())n2 Ответ №2
Если вам нужно получить более 1 комбинации, это альтернатива использованию цикла для этого типа вещей.
declare @max int = 5; with FirstNames(FName) as ( SELECT ‘John’ UNION ALL SELECT ‘Tim’ UNION ALL SELECT ‘Laura’ UNION ALL SELECT ‘Jeff’ UNION ALL SELECT ‘Sara’ ) , LastNames(LName) as ( SELECT ‘Johnson’ UNION ALL SELECT ‘Hudson’ UNION ALL SELECT ‘Jackson’ UNION ALL SELECT ‘Ranallo’ UNION ALL SELECT ‘Curry’ ) , SortedNames(FName, LName, RowNum) as ( select FName , LName , ROW_NUMBER() over (Order by newid()) from FirstNames cross join LastNames ) select FName , LName from SortedNames where RowNum <= @max order by NEWID(); Ответ №3
Если вы хотите эффективно генерировать несколько строк (вы упомянули о необходимости создания большого количества выборочных данных), то вот что я только что опубликовал ранее сегодня по другому вопросу о SO (предупреждение Msg 6522, уровень 16 во время выполнения хранимой процедуры clr). Этот вопрос касался рандомизации 4 полей вместо двух, но я сохраняю эти дополнительные поля здесь, чтобы вы могли видеть, как легко адаптироваться к другим сценариям, которые у вас могут быть. Достаточно просто удалить любое из полей, а также легко добавить новые значения в любую из 4 переменных таблицы (чтобы увеличить количество возможных комбинаций), поскольку запрос динамически корректирует диапазон рандомизации для соответствия любым данным каждая переменная таблицы (т.е. строки 1 – n).
DECLARE @TelNumber TABLE (TelNumberID INT NOT NULL IDENTITY(1, 1), Num VARCHAR(30) NOT NULL); INSERT INTO @TelNumber (Num) VALUES (‘1525407’), (‘5423986’), (‘1245398’), (‘32657891’), (‘123658974’), (‘7896534’), (‘12354698’); DECLARE @FirstName TABLE (FirstNameID INT NOT NULL IDENTITY(1, 1), Name NVARCHAR(30) NOT NULL); INSERT INTO @FirstName (Name) VALUES (‘Babak’), (‘Carolin’), (‘Martin’), (‘Marie’), (‘Susane’), (‘Michail’), (‘Ramona’), (‘Ulf’), (‘Dirk’), (‘Sebastian’); DECLARE @LastName TABLE (LastNameID INT NOT NULL IDENTITY(1, 1), Name NVARCHAR(30) NOT NULL); INSERT INTO @LastName (Name) VALUES (‘Bastan’), (‘Krause’), (‘Rosner’), (‘Gartenmeister’), (‘Rentsch’), (‘Benn’), (‘Kycik’), (‘Leuoth’), (‘Kamkar’), (‘Kolaee’); DECLARE @Address TABLE (AddressID INT NOT NULL IDENTITY(1, 1), Addr NVARCHAR(100) NOT NULL); INSERT INTO @Address (Addr) VALUES (‘Deutschlan Chemnitz Sonnenstraße 59’), (»), (‘Deutschland Chemnitz Arthur-Strobel straße 124’), (‘Deutschland Chemnitz Brückenstraße 3’), (‘Iran Shiraz Chamran Blvd, Niayesh straße Nr.155’), (»), (‘Deutschland Berlin Charlotenburg Pudbulesky Alleee 52’), (‘United State of America Washington DC. Farbod Alle’), (»); DECLARE @RowsToInsert INT = 10000; ;WITH rowcounts AS ( SELECT (SELECT COUNT(*) FROM @TelNumber) AS [TelNumberRows], (SELECT COUNT(*) FROM @FirstName) AS [FirstNameRows], (SELECT COUNT(*) FROM @LastName) AS [LastNameRows], (SELECT COUNT(*) FROM @Address) AS [AddressRows] ), nums AS ( SELECT TOP (@RowsToInsert) (CRYPT_GEN_RANDOM(1) % rc.TelNumberRows) + 1 AS [RandomTelNumberID], (CRYPT_GEN_RANDOM(1) % rc.FirstNameRows) + 1 AS [RandomFirstNameID], (CRYPT_GEN_RANDOM(1) % rc.LastNameRows) + 1 AS [RandomLastNameID], (CRYPT_GEN_RANDOM(1) % rc.AddressRows) + 1 AS [RandomAddressID] FROM rowcounts rc CROSS JOIN msdb.sys.all_columns sac1 CROSS JOIN msdb.sys.all_columns sac2 ) — INSERT dbo.Unsprstb(Firstname, Lastname, Tel, Address) SELECT fn.Name, ln.Name, tn.Num, ad.Addr FROM @FirstName fn FULL JOIN nums ON nums.RandomFirstNameID = fn.FirstNameID FULL JOIN @LastName ln ON ln.LastNameID = nums.RandomLastNameID FULL JOIN @TelNumber tn ON tn.TelNumberID = nums.RandomTelNumberID FULL JOIN @Address ad ON ad.AddressID = nums.RandomAddressID;
Заметки:
- FULL JOIN необходимы вместо INNER JOIN чтобы получить все количество строк @RowsToInsert.
- Дублирующие строки возможны из-за самой природы этой рандомизации И не отфильтровывают их с помощью DISTINCT. Однако DISTINCT не может использоваться с данными данного образца в вопросе, так как количество элементов в каждой переменной массива/таблицы обеспечивает только 6300 уникальных комбинаций, а запрошенное количество строк для генерации составляет 10 000. Если к табличным переменным добавлено больше значений, так что суммарные возможные уникальные комбинации превышают запрошенное количество строк, то либо ключевое слово DISTINCT может быть добавлено в nums CTE, либо запрос может быть реструктурирован, чтобы просто CROSS JOIN все table, ROW_COUNT() и захватите TOP(n) используя ORDER BY NEWID() (но этот подход имеет свои плюсы и минусы).
- INSERT закомментирован, поэтому легче видеть, что вышеприведенный запрос дает желаемый результат. Просто раскомментируйте INSERT чтобы запрос выполнял фактическую операцию DML.
