Блог

Используя SQL-запрос, мне нужно включить пустые строки в результат, чтобы каждая группа (family_id) равнялась 4 строкам

 SELECT   
    ROW_NUMBER() OVER(PARTITION BY family_id ORDER BY family_id) AS rowNum,
    full_name as name, family_id
FROM
    tbl_person
 

Вот так:

и результат будет

Просто определите второй запрос, который создает нужные вам пустые строки, и объедините их вместе.

 SELECT ROW_NUMBER() OVER(PARTITION BY family_id ORDER BY family_id) AS rowNum,
    full_name as name,
    family_id
FROM tbl_person

UNION ALL

SELECT n.N AS rowNum,
    NULL AS name,
    p.family_id
FROM (SELECT family_id, COUNT(*) family_count FROM tbl_person group by family_id) p
INNER JOIN (
    SELECT 1 AS N
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    ) n
    ON n.n > p.family_count
ORDER BY family_id,
    rowNum,
    name;
 

Если вы используете таблицу подсчета (поскольку в ней всего 4 числа, она может быть встроенной), и вы перекрестно применяете их ко всем family_id, вы получаете записи с номерами от 1 до 4 для каждого family_id. Затем левое соединение как для nr, так и для family_id, результат будет содержать 4 строки для каждого идентификатора, в которых заполняются только данные существующих строк. (Чтобы предотвратить двойные ссылки, вы можете использовать cte в главной таблице, чтобы получить оба идентификатора и выполнить левое соединение, но вы также можете создать группу непосредственно в главной таблице)

 with p as
(
    SELECT   *,ROW_NUMBER() OVER(PARTITION BY family_id ORDER BY family_id) AS rowNum  
    FROM tbl_person
)
select nr as row_num, full_name as name, f.family_id
from (values(1),(2),(3),(4)) as nrs(nr) --inline tally table
cross apply (select family_id from p group by family_id) f --group by family id to get all existing id's
left join  p on p.rowNum = nr and f.family_id = p.family_id
 

Редактировать
Поскольку вам нужно больше строк, проще (для последующего создания и изменения) использовать какую-то таблицу подсчета. Одним из моих личных фаворитов является использование существующей таблицы sys:

 with p as
(
    SELECT   *,ROW_NUMBER() OVER(PARTITION BY family_id ORDER BY family_id) AS rowNum  
    FROM tbl_person
)
select nr as row_num, full_name as name, f.family_id
from (select top 14 ROW_NUMBER() over (order by (select 1)) nr from sys.all_columns) nrs --use sys table for tallying
cross apply (select family_id from p group by family_id) f
left join  p on p.rowNum = nr and f.family_id = p.family_id
order by family_id,nr
 

Добавьте пустые строки в последнюю строку каждой группы, используя рекурсивный CTE:

 with Q as(
 SELECT full_name as name, age, family_id,
        ROW_NUMBER() OVER(PARTITION BY family_id ORDER BY family_id) AS rowNum,
        count(1) over(partition by family_id) cnt
   FROM tbl_person
 union all
  select NULL,NULL,family_id, Q.rowNum 1, Q.rowNum 1
    from Q
   where Q.cnt=Q.rowNum and Q.rowNum < 4
)
select * from Q
 order by family_id, rowNum
 

Я бы использовал таблицу подсчета cte для построения скелета на основе ваших уникальных family_id значений и таблицу результатов cte, которую можно оставить присоединенной к скелету на основе tally number = номер строки.

 Declare @family_row_count int = 14;

-- Tally Table CTE script (SQL 2005  only)
-- You can use this to create many different numbers of rows... for example:
-- You could use a 3 way cross join (t3 x, t3 y, t3 z) instead of just 2 way to generate a different number of rows.
-- The # of rows this would generate for each is noted in the X3 comment column below.
-- For most common usage, I find t3 or t4 to be enough, so that is what is coded here.
-- If you use t3 in ‘Tally’, you can delete t4 and t5.


; WITH
    -- Tally table Gen            Tally Rows:     X2                X3
t1 AS (SELECT 1 N UNION ALL SELECT 1 N),    -- 4            ,    8
t2 AS (SELECT 1 N FROM t1 x, t1 y),            -- 16            ,    64
t3 AS (SELECT 1 N FROM t2 x, t2 y),            -- 256            ,    4096
t4 AS (SELECT 1 N FROM t3 x, t3 y),            -- 65536        ,    16,777,216
t5 AS (SELECT 1 N FROM t4 x, t4 y),            -- 4,294,967,296,    A lot
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
          FROM t3 x, t3 y) -- Change the t3's to one of the other numbers above for more/less rows 

, cte_Skeleton
as (
    select
         tally.N as [row_number]
         , family.family_id
    from Tally tally
    cross join ( select family_id from tbl_person group by family_id ) family
    where tally.N <= @family_row_count
)

, cte_person
as
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY family_id ORDER BY family_id) AS rowNum
        , full_name as name
        , family_id
    FROM tbl_person
)

select
      skeleton.[row_number] as [rowNum]
    , person.name
    , person.age
    , skeleton.family_id

from cte_Skeleton skeleton

left join cte_person person
    on person.family_id = skeleton.family_id
    and person.rownum = skeleton.[row_number]
 

ПРИМЕЧАНИЕ: поскольку вы сначала определили скелет и остались присоединяться к нему, даже если family_id содержит более 14 человек, только первые 14 будут иметь строки скелета для сопоставления в соединении.

Общее решение для любого количества людей (SQLFiddle — http://sqlfiddle.com /#!3/00677/25):

 WITH 

maxRow AS (
  SELECT TOP 1 COUNT(*) maxRow FROM tbl_person GROUP BY family_id ORDER BY 1 DESC 
),

rn AS (
  SELECT 1 as rowNum
  UNION ALL
  SELECT rowNum   1
  FROM rn 
  WHERE rowNum < (SELECT * FROM maxRow)
),

rnFi AS (
SELECT 
  * 
FROM 
  rn,
  (SELECT DISTINCT family_id FROM tbl_person) fi
)

SELECT
  rnFi.rowNum,
  rnFi.family_id,
  t.name
FROM
  rnFi
LEFT JOIN
  (SELECT   
    ROW_NUMBER() OVER(PARTITION BY family_id ORDER BY family_id) AS rowNum,
    family_id,
    name
  FROM
    tbl_person) t ON rnFi.family_id = t.family_id AND rnFi.rowNum = t.rowNum
ORDER BY
  2, 1
;