7

Вопрос по sql – Как SQL может создавать дубликаты записей?

Error: User Rate Limit ExceededError: User Rate Limit ExceededError: User Rate Limit Exceeded

id| name| count
---------------
 1| Anne|     3
 2|  Joe|     2

в:

id| name
--------
 1| Anne
 1| Anne
 1| Anne
 2|  Joe
 2|  Joe

Error: User Rate Limit ExceededAnneError: User Rate Limit ExceededJoeError: User Rate Limit ExceededcountError: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

EDIT:

Error: User Rate Limit Exceeded

ANOTHER EDIT:

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

Apr 09, 2012, 8:47 AMот

Error: User Rate Limit Exceeded<b>Yes, it can be done</b> :)

Apr 09, 2012, 8:46 AMот

Error: User Rate Limit Exceeded

Apr 09, 2012, 8:47 AMот

Error: User Rate Limit Exceeded

Apr 09, 2012, 8:52 AMот

3ответа

8

Error: User Rate Limit Exceeded

CREATE TABLE num
( i UNSIGNED INT NOT NULL
, PRIMARY KEY (i)
) ;

INSERT INTO num (i)
VALUES
  (1), (2), ..., (1000000) ;

Error: User Rate Limit Exceeded

SELECT 
    t.id, t.name
FROM 
        tableX AS t
    JOIN
        num
            ON num.i <= t."count"

WarningError: User Rate Limit ExceededcountError: User Rate Limit ExceededcountError: User Rate Limit Exceeded

5

Error: User Rate Limit Exceeded

WITH cte AS (
    SELECT id, name, count FROM tbl
    UNION ALL
    SELECT id, name, count-1 FROM cte WHERE count>1
)
SELECT id, name FROM cte
2

Error: User Rate Limit Exceeded

;WITH CTE_Data as (
    select id=1, name='Anne',[COUNT]=3
    union select id=2, name='Joe',[COUNT]=2
),
CTE_List as (
    select
        id,
        name,
        ind=1
    from CTE_Data
    union all
    select
        l.id,
        l.name,
        ind=ind+1
    from CTE_List l
        join CTE_Data d on l.id = d.id
    where 
        l.ind < d.[count]
)
select id,name from CTE_List
order by id,ind

Error: User Rate Limit Exceeded

RelatedQuestions