Подтвердить что ты не робот

Мне нужно знать, как создать запрос кросс-таблицы

Мне нужна помощь в создании приведенных ниже результатов. Я думал о sql pivot, но я не знаю, как его использовать. Посмотрел несколько примеров и не смог найти решение. Любые другие идеи о том, как это сделать, также приветствуются. Строки состояния должны динамически генерироваться.

У вас есть три таблицы, активы, типы активов, assetstatus

Table: assets
assetid     int
assettag    varchar(25)
assettype   int
assetstatus int

Table: assettypes
id         int
typename   varchar(20)  (ex: Desktop, Laptop, Server, etc.)

Table: assetstatus
id         int
statusname varchar(20)  (ex: Deployed, Inventory, Shipped, etc.)

Желаемые результаты:

AssetType     Total   Deployed   Inventory  Shipped     ...
-----------------------------------------------------------
Desktop         100       75        20          5       ...
Laptop           75       56        19          1       ...
Server           60       50        10          0       ...

Некоторые данные:

assets table:
1,hol1234,1,1
2,hol1233,1,2
3,hol3421,2,3
4,svr1234,3,1

assettypes table:
1,Desktop
2,Laptop
3,Server

assetstatus table:
1,Deployed
2,Inventory
3,Shipped
4b9b3361

ОТВЕТЫ

Ответ 1

Этот тип преобразования называется стержнем. Вы не указали, какую базу данных вы используете, поэтому я дам ответы для SQL Server и MySQL.

SQL Server: Если вы используете SQL Server 2005+, вы можете реализовать функцию PIVOT.

Если у вас есть известное количество значений, которые вы хотите преобразовать в столбцы, вы можете запрограммировать запрос.

select typename, total, Deployed, Inventory, shipped
from
(
  select count(*) over(partition by t.typename) total,
    s.statusname,
    t.typename
  from assets a
  inner join assettypes t
    on a.assettype = t.id
  inner join assetstatus s
    on a.assetstatus = s.id
) d
pivot
(
  count(statusname)
  for statusname in (Deployed, Inventory, shipped)
) piv;

Смотрите SQL Fiddle with Demo.

Но если у вас есть неизвестное количество значений status, вам нужно будет использовать динамический sql для генерации списка столбцов во время выполнения.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(statusname) 
                    from assetstatus
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT typename, total,' + @cols + ' from 
             (
                select count(*) over(partition by t.typename) total,
                  s.statusname,
                  t.typename
                from assets a
                inner join assettypes t
                  on a.assettype = t.id
                inner join assetstatus s
                  on a.assetstatus = s.id
            ) x
            pivot 
            (
                count(statusname)
                for statusname in (' + @cols + ')
            ) p '

execute(@query)

Смотрите SQL Fiddle with Demo

Это также можно записать с использованием агрегатной функции с выражением case:

select typename,
  total,
  sum(case when statusname ='Deployed' then 1 else 0 end) Deployed,
  sum(case when statusname ='Inventory' then 1 else 0 end) Inventory,
  sum(case when statusname ='Shipped' then 1 else 0 end) Shipped
from
(
  select count(*) over(partition by t.typename) total,
    s.statusname,
    t.typename
  from assets a
  inner join assettypes t
    on a.assettype = t.id
  inner join assetstatus s
    on a.assetstatus = s.id
) d
group by typename, total

Смотрите SQL Fiddle with Demo

MySQL:. В этой базе данных нет сводной функции, поэтому вам придется использовать функцию aggregate и выражение CASE. Он также не имеет оконных функций, поэтому вам придется немного изменить запрос:

select typename,
  total,
  sum(case when statusname ='Deployed' then 1 else 0 end) Deployed,
  sum(case when statusname ='Inventory' then 1 else 0 end) Inventory,
  sum(case when statusname ='Shipped' then 1 else 0 end) Shipped
from
(
  select t.typename,
    (select count(*) 
     from assets a1 
     where a1.assettype = t.id 
     group by a1.assettype) total,
    s.statusname
  from assets a
  inner join assettypes t
    on a.assettype = t.id
  inner join assetstatus s
    on a.assetstatus = s.id
) d
group by typename, total;

См. SQL Fiddle with Demo

Затем, если вам нужно динамическое решение в MySQL, вам нужно будет использовать подготовленный оператор для генерации строки sql для выполнения:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN statusname = ''',
      statusname,
      ''' THEN 1 else 0 END) AS `',
      statusname, '`'
    )
  ) INTO @sql
FROM assetstatus;

SET @sql 
  = CONCAT('SELECT typename,
              total, ', @sql, ' 
            from
            (
              select t.typename,
                (select count(*) 
                 from assets a1 
                 where a1.assettype = t.id 
                 group by a1.assettype) total,
                s.statusname
              from assets a
              inner join assettypes t
                on a.assettype = t.id
              inner join assetstatus s
                on a.assetstatus = s.id
            ) d
            group by typename, total');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Смотрите SQL Fiddle with Demo.

Результат одинаковый для всех запросов в обеих базах данных:

| TYPENAME | TOTAL | DEPLOYED | INVENTORY | SHIPPED |
-----------------------------------------------------
|  Desktop |     2 |        1 |         1 |       0 |
|   Laptop |     1 |        0 |         0 |       1 |
|   Server |     1 |        1 |         0 |       0 |