Как мне использовать цикл для динамической генерации имен столбцов?

ProgramBox

Как мне использовать цикл для динамической генерации имен столбцов?

Post author:admin
Запись опубликована:7 февраля, 2023
Post category:Вопросы по программированию

#sql #sql-server #loops

#sql #sql-сервер #циклы

Вопрос:

У меня есть таблица sdata , и в ней 35 столбцов ( id , name , TRx1 , TRx2 , TRx3 , TRx4 ,…, TRx30 , city , score , total )

Я хочу извлекать данные из TRx1,...TRx30 столбцов.

Могу ли я использовать цикл здесь?

Я выполнил следующий код:

 DECLARE @flag INT
DECLARE @sel varchar(255)
DECLARE @frm varchar(255)

SET @flag = 1;
SET @sel = 'select TRx';
SET @frm = ' from sdata';

exec(@sel   

    (WHILE @flag <=5
        @flag
    SET @flag = @flag   1)

  @frm)

Что я делаю не так? И как я могу это решить?

1. Копирование / вставка и изменение имен столбцов было бы в миллион раз быстрее (и менее сложным / подверженным ошибкам), чем написание while цикла для построения динамического запроса..

2. не используйте цикл в SQL. никогда. не делайте этого. SQL — это не циклы.

3. Я не понимаю, почему люди ненавидят цикл while. Это loop просто для генерации строки, в которой нет ничего плохого…. Когда вы обрабатываете данные запись за записью (ie) RBAR , тогда вам нужно беспокоиться, но этот цикл в порядке.

4. @Prdp — Дело не в том, что я ненавижу циклы, проблема в том, что циклы работают медленно. Циклы работают плохо — они «работают» в SQL, но не очень хорошо. Это означает (в качестве примера), что там, где я работаю, около 2000 запросов в час, и если бы они были реализованы как циклы, они никогда бы не завершились и не привели к сбою всей системы. SQL разработан так, чтобы не использовать циклы — хотя вы можете их использовать, но это ДЕЙСТВИТЕЛЬНО ОЧЕНЬ плохая идея. Кроме того, вы ничего не можете сделать с циклом в SQL, вы также не можете обойтись без цикла и быстрее, так зачем их использовать?

5. Не используйте это просто для сохранения нажатий клавиш, потому что это имеет большое значение в SQL, является ли ваш запрос статическим или динамическим. Если 5 в вашем примере выше не будет переменной или параметром, не беспокойтесь об этом и просто выпишите запрос полностью, каким бы неуклюжим он ни выглядел. Также рассмотрите возможность простого извлечения всех столбцов и использования клиентского фильтра. Это может быть или не быть подходящим с точки зрения производительности, но это, безусловно, упрощает работу с SQL.

Ответ №1:

Если ваше имя таблицы sdata, этот код должен работать для вас:

 -- Grab the names of all the remaining columns
DECLARE @sql nvarchar(MAX);
DECLARE @columns nvarchar(MAX);
SELECT @columns = STUFF ( ( SELECT N'], ['   name
            FROM sys.columns
           WHERE object_id = (select top 1 object_id FROM sys.objects where name = 'sdata') 
             AND name LIKE 'TRx%' -- To limit which columns
           ORDER BY column_id
             FOR XML PATH('')), 1, 2, '')   ']';
PRINT @columns
SELECT @sql = 'SELECT '   @columns   ' FROM sdata';
PRINT @sql;
EXEC (@sql);

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

1. Использование QUOTENAME для экранирования столбцов немного более надежно (хотя, по общему признанию, вы мазохист, если используете ] в своих именах столбцов). SELECT QUOTENAME(name) ',' и удалите финал , с REVERSE(STUFF(REVERSE((...)), 1, 1, '')) помощью .

2. Это отсутствует AND name LIKE 'TRx%' , если нам нужны только эти столбцы. (Одной из сильных сторон этого подхода является возможность выбора из реальных столбцов на основе любых критериев, которые нам нравятся, так что вы можете также показать это.)

3. Правильно… Я хотел предоставить более «общий» шаблон, который можно было бы настраивать, а также быть максимально «понятным». Я обновлю фрагмент, чтобы он был более понятным. Спасибо.

Ответ №2:

Это было бы намного проще сделать, просто скопировав / вставив имена столбцов и изменив их на правильные. Однако, если вы должны сделать это таким образом, я вообще не советую использовать цикл. Этот метод использует таблицу подсчета для генерации столбцов, которые вы хотите выбрать (в этом примере столбцы 1 сквозные 30 , но это можно изменить), затем генерирует динамическую инструкцию SQL для выполнения с SData таблицей:

 Declare @From   Int = 1,
        @To     Int = 30,
        @Sql    NVarchar (Max)

Declare @Columns Table (Col Varchar (255))

;With Nums As
(
    Select  *
    From    (Values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) As V(N)
), Tally As
(
    Select  Row_Number() Over (Order By (Select Null)) As N
    From        Nums    A   --10
    Cross Join  Nums    B   --100
    Cross Join  Nums    C   --1000
)
Insert  @Columns
Select  'TRx'   Cast(N As Varchar)
From    Tally
Where   N Between @From And @To

;With Cols As
(
    Select  (
                Select  QuoteName(Col)   ',' As [text()]
                From    @Columns
                For Xml Path ('')
            ) As Cols
) 
Select  @Sql = 'Select '   Left(Cols, Len(Cols) - 1)   ' From SData'
From    Cols

--Select    @Sql
Execute (@Sql)

Примечание: --Select @Sql раздел предназначен для предварительного просмотра сгенерированного запроса перед его выполнением.

Ответ №3:

Вы можете выбрать имена столбцов следующим образом:

 SELECT column_name 
FROM information_schema.columns
WHERE table_name = 'my name here'