Вопрос по sql, mysql, oracle, sql-server, postgresql – SQL: повторить строку результата несколько раз и пронумеровать строки

24

У меня SQL-запрос с таким результатом:

value | count
------+------
foo   |     1
bar   |     3
baz   |     2

Теперь я хочу расширить это так, чтобы каждая строка сcount больше 1 встречается несколько раз. Мне также нужно, чтобы эти строки были пронумерованы. Так что я бы получил:

value | count | index
------+-------+------
foo   |     1 |     1
bar   |     3 |     1
bar   |     3 |     2
bar   |     3 |     3
baz   |     2 |     1
baz   |     2 |     2

Я должен заставить это работать на всех основных базах данных (Oracle, SQL Server, MySQL, PostgreSQL и, возможно, больше). Таким образом, решение, которое работает в разных базах данных, было бы идеальным, но умные способы заставить его работать в любой базе данных приветствуются.

Ваш Ответ

9   ответов
21

Generate_series, что делается с помощью просмотров. MySQL - единственная СУБД средибольшой четверк у которого нет функции CTE.

На самом деле вы можете использовать эту технику в базе данных, которая поддерживает представление. Так что это практически все базы данных

Техника генераторов здесь:http: //use-the-index-luke.com/blog/2011-07-30/mysql-row-generator#mysql_generator_cod

Единственная небольшая модификация, которую мы сделали, - это замена побитовой сдвиг влево а также бит или) техника из оригинальной техники с простым умножением и сложением соответственно; Sql Server и Oracle не имеют оператора сдвига влево.

Эта абстракция гарантированно на 99% работает на всех базах данных, кроме Oracle; ОракулSELECT не может функционировать без какой-либо таблицы, для этого нужно выбрать пустую таблицу, Oracle уже предоставил такую, она называетсяDUAL стол. Переносимость базы данных - несбыточная мечта: -)

Вот абстрагированные представления, работающие на всех СУБД, лишенные побитовых операций (которые в любом случае не являются необходимостью в этом сценарии) и нюансы функций (мы удаляемOR REPLACE наCREATE VIEW, только Postgresql и MySQL их поддерживают) среди всех основных баз данных.

Предостережение оракула: Просто положиFROM DUAL после каждогоSELECT выражение

CREATE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE VIEW generator_256
AS SELECT ( ( hi.n * 16 ) + lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE VIEW generator_4k
AS SELECT ( ( hi.n * 256 ) + lo.n ) AS n
     FROM generator_256 lo, generator_16 hi;

CREATE VIEW generator_64k
AS SELECT ( ( hi.n * 256 ) + lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

CREATE VIEW generator_1m
AS SELECT ( ( hi.n * 65536 ) + lo.n ) AS n
     FROM generator_64k lo, generator_16 hi;

Затем используйте этот запрос:

SELECT t.value, t.cnt, i.n
FROM tbl t
JOIN generator_64k i 
ON i.n between 1 and t.cnt
order by t.value, i.n

Postgresql:http: //www.sqlfiddle.com/# 1 / 1541d / 1

Oracle:http: //www.sqlfiddle.com/# 4 / 26c05 / 1

Sql сервер:http: //www.sqlfiddle.com/# 6 / 84bee / 1

MySQL:http: //www.sqlfiddle.com/# 2 / 78f5b / 1

Мне больше нравится этот ответ, потому что он портативный. cygri
Этот ответ помог мне решить основную проблему в наших запросах и помог мне создать целый ряд новых отчетов с использованием этого кода. Хотел бы я сделать больше, чем просто проголосовать за этот ответ. :) Neels
2

SQL Server 2005 и более поздние версии могут справиться с этим рекурсивно:

declare @Stuff as Table ( Name VarChar(10), Number Int )
insert into @Stuff ( Name, Number ) values ( 'foo', 1 ), ( 'bar', 3 ), ( 'baz', 2 )

select * from @Stuff

; with Repeat ( Name, Number, Counter ) as (
  select Name, Number, 1
    from @Stuff
    where Number > 0
  union all
  select Name, Number, Counter + 1
    from Repeat
    where Counter < Number
  )
select *
  from Repeat
  order by Name, Counter -- Group by name.
  option ( maxrecursion 0 )
1

ПростымJOIN Вы можете достичь цели повторения записей n раз.
Следующий запрос повторяется каждую запись 20 раз.

SELECT  TableName.*
FROM    TableName
JOIN    master.dbo.spt_values on type = 'P' and number < 20


Примечание дляmaster.dbo.spt_values on type = 'P':
Эта таблица используется для получения серии чисел, жестко закодированных в ней при условииtype='P'.

0

Вы можете использовать CTE:

WITH Numbers(Num) AS
(
    SELECT 1 AS Num
    UNION ALL 
    SELECT Num + 1
    FROM   Numbers c
    WHERE  c.Num < 1000
)

SELECT VALUE,COUNT, number
FROM   TABLE
       JOIN Numbers
            ON  TABLE.count >= Numbers.Num
OPTION(MAXRECURSION 1000)
0

В Oracle мы могли бы использовать комбинациюLEVEL а такжеCROSS JOIN.

  SELECT *
    FROM yourtable
         CROSS JOIN (    SELECT ROWNUM index_t
                           FROM DUAL
                     CONNECT BY LEVEL <= (SELECT MAX (count_t) FROM yourtable))
   WHERE index_t <= count_t
ORDER BY VALUE, index_t;

DEMO

4

в зависимости от платформы (это для SQL Server):

CREATE TABLE Numbers(Number INT PRIMARY KEY);

INSERT Numbers 
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_columns;

Теперь эта временная шкала также является SQL Server, но демонстрирует синтаксис объединения, который должен быть действительным для всех указанных вами СУБД (хотя, признаюсь, я их не использую, поэтому не могу тестировать):

DECLARE @foo TABLE(value VARCHAR(32), [count] INT);

INSERT @foo SELECT 'foo', 1
UNION ALL SELECT 'bar', 3
UNION ALL SELECT 'baz', 2;

SELECT f.value, f.[count], [index] = n.Number
FROM @foo AS f, Numbers AS n
WHERE n.Number <= f.[count];

Результаты (опять же, SQL Server):

value | count | index
------+-------+------
foo   |     1 |     1
bar   |     3 |     1
bar   |     3 |     2
bar   |     3 |     3
baz   |     2 |     1
baz   |     2 |     2
6

не зависящее от БД, и @ Джастин дал вам хорошее решение.
Ты тоже просил

умные способы заставить это работать на любой базе данных

Есть один дляPostgreSQL:generate_series() делает то, что вы просили из коробки:

SELECT val, ct, generate_series(1, ct) AS index
FROM   tbl;

Кстати, я бы предпочел не использоватьvalue а такжеcount как имена столбцов. Это плохая практика, чтобы использовать зарезервированные слова в качестве идентификаторов. С помощьюval а такжеct вместо.

Brilliant! Я хотел бы, чтобы это работало везде. И да, вы правы насчет зарезервированных слов, спасибо за указание. cygri
7

и он настолько сдержанный, когда речь заходит о стандартах и возможностях.

Работает на всех основных СУБД, кроме MySQL:

with 
-- Please add this on Postgresql:
-- RECURSIVE
tbl_populate(value, cnt, ndx) as
(
  select value, cnt, 1 from tbl

  union all

  select t.value, t.cnt, tp.ndx + 1
  from tbl t
  join tbl_populate tp 
  on tp.value = t.value  
  and tp.ndx + 1 <= t.cnt
)
select * from tbl_populate
order by cnt, ndx

SQL сервер:http: //www.sqlfiddle.com/# 6 / 911a9 / 1

Oracle:http: //www.sqlfiddle.com/# 4 / 198cd / 1

Postgresql:http: //www.sqlfiddle.com/# 1 / 0b03d / 1

+ 1 Отличная альтернатива классическому решению с таблицей чисел. Florin Ghita
Хорошо, это работает, хотя я не совсем понимаю, что он делает: -) cygri
+ 1 для " IE мира баз данных". Вы сделали мой ден a_horse_with_no_name
не должно бытьrecursive ключевое слово послеwith ? vyegorov
Хе-хе, правда? Теперь я вдохновлен вести блог о рекурсии на CTE. Но в основном генераторы строк - это самый простой вид рекурсии CTE, он следует логике хвостовой рекурсии; Хвостовая рекурсия почти не имеет однозначного соответствия с циклом. Иерархические рекурсии и рекурсии графов объектов в CTE немного сложнее понят Michael Buen
32

Ты можешь использовать таблицу чисел

SELECT value, count, number
FROM table
    JOIN Numbers 
        ON table.count >= Numbers.number

Вот SQLFiddle, использующий MSSQL

Любое понимание, почему это работает? Отлично сработало для моего решения 4 года спустя. nzaleski
Самое простое объяснение состоит в том, что вместо объединения в 1 строку он объединяет все строки вплоть до числа после подсчета. то есть. Счет = 3, затем он соответствует 1,2,3, создавая результат 3 | 1, 3 | 2 и 3 | 3 Justin Pihony
+ 1 это самый лучший и ясный ответ! Florin Ghita

Похожие вопросы