Хранить динамический список изображений в 1 строке

Я присоединяюсь к этим двум таблицам и хочу отправить представление Test с его изображениями.

  • Тест

    • Name (VARCHAR(255))
    • Идентификатор теста (INT)
    • Время начала (DATETIME2(7))
    • Статус (VARCHAR(255))

  • Картинки

    • ImageId (INT)
    • Идентификатор теста (INT)
    • Изображение (VARBINARY(MAX))

Прямо сейчас я делаю это:

SELECT Name, t.TestId, StartTime, Status, Image
FROM [Test] t
LEFT JOIN [Images] i ON i.TestId = t.TestId

Что мне здесь не нравится, так это то, что у меня есть несколько строк из одного и того же теста, но с разными изображениями. Есть ли удобный способ вернуть только одну строку каждого теста с его изображениями?

Мои изображения не такие тяжелые (максимум 100 КБ), и у меня не так много изображений на тест (максимум 10). Вот что я думаю:

  1. Это может показаться сумасшедшим, но, возможно, в SQL-сервере есть способ преобразовать Image в список изображений в виде VARBINARY(MAX).
  2. Я мог бы иметь 10 столбцов (изображение1, изображение2...) в представлении и заполнить их изображениями, если они существуют (НЕ NULL)

Я немного застрял с этими решениями, так как я мало знаю о SQL, но я все еще пытаюсь. Вы видите другой способ сделать это? Если нет, не могли бы вы помочь мне полезным советом, как добиться 1. или 2.


sql-server tsql join group-by varbinarymax
person vvilin    schedule 22.04.2020    source источник

Ответы (2)


arrow_upward
2
arrow_downward

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

И что? Для каждой строки повторяется всего несколько байтов, что незначительно по сравнению с большим двоичным объектом размером 100 КБ.

Но вы можете получить данные именно в той форме, в которой вы хотите, если у вас есть SQL Server, преобразующий их в JSON. varbinary(max) будет закодирован в base64, и добавление всех накладных расходов JSON приведет к большему размеру результата. Но такой запрос

SELECT Name , 
       t.TestId , 
       StartTime , 
       Status ,  
       (select Image from Images where testid = t.testid for json path) Images
FROM [Test] t
for json path

будет выводить данные как

[
    {
        "Name": "Test1",
        "TestId": 1,
        "StartTime": "2020-04-22T18:15:47.9533333",
        "Status": "complete",
        "Images": [
            {
                "Image": "j2LYPy9Uy0Wbz0/qsPk0qo9i2D8vVMtFm89P6rD5NKqPYtg/L1TLRZvPT+qw+TSqj2LYPy9Uy0Wbz0/qsPk0qo9i2D8vVMtFm89P6rD5NKo="
            },
            {
                "Image": "j2LYPy9Uy0Wbz0/qsPk0qo9i2D8vVMtFm89P6rD5NKqPYtg/L1TLRZvPT+qw+TSqj2LYPy9Uy0Wbz0/qsPk0qo9i2D8vVMtFm89P6rD5NKo="
            }
        ]
    }
]
person David Browne - Microsoft    schedule 22.04.2020

arrow_upward
1
arrow_downward

Второй вариант можно реализовать с помощью row_number() и условной агрегации, например:

select 
    t.name,
    t.testid,
    t.starttime,
    t.status,
    max(case when i.rn =  1 then i.image end) image01,
    max(case when i.rn =  2 then i.image end) image02,
    ...
    max(case when i.rn = 10 then i.image end) image10
from test t
left join (
    select testid, image, row_number() over(partition by testid order by imageid) rn
    from images
) i on i.testid = t.testid
group by t.name, t.testid, t.starttime, t.status

Вы также можете использовать outer apply, что может быть более эффективным:

select
    t.*,
    i.*
from test t
outer apply (
    select 
        max(case when rn =  1 then image end) image01,
        max(case when rn =  2 then image end) image02
        ...
        max(case when rn = 10 then image end) image10
    from (
        select image, row_number() over(order by imageid) rn
        from image i
        where i.testid = t.testid
    ) i
) i
person GMB    schedule 22.04.2020