sql - son - Necesito saber cómo crear una consulta de tabla de referencias cruzadas

¿cuáles son los métodos para crear una consulta de tabla de referencias cruzadas?

Question

Necesito ayuda para crear los siguientes resultados. Pensé en un pivote de sql pero no sé cómo usarlo. Miré algunos ejemplos y no puedo encontrar una solución. Cualquier otra idea sobre cómo lograr esto también es bienvenida. Las columnas de estado deben ser generadas dinámicamente.

Tener tres tablas, activos, assettypes, 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.)

Resultados deseados:

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

Algunos datos:

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

Answer 1

Al usar un DBMS (Base de datos de Absolute) que no cumple con el pivote, tuve más éxito al usar esta declaración equivalente de tablas cruzadas de SQL:

SELECT
  sub.TypeName
, SUM(sub.[Count]) AS "Total"
, SUM(CASE WHEN AssetStatus=''1'' THEN sub.[Count] ELSE 0 END) AS "Deployed"
, SUM(CASE WHEN AssetStatus=''2'' THEN sub.[Count] ELSE 0 END) AS "Inventory"
, SUM(CASE WHEN AssetStatus=''3'' THEN sub.[Count] ELSE 0 END) AS "Shipped"
FROM
 (
SELECT
  t.TypeName
, AssetStatus
, COUNT(AssetID) AS "Count"
FROM
  Assets
  JOIN AssetTypes t ON t.ID = AssetType
  JOIN AssetStatus s ON s.ID = AssetStatus
GROUP BY t.TypeName, AssetStatus, s.StatusName
 ) sub
GROUP BY sub.TypeName
;

Cuando me di cuenta de que este código (arriba) no funcionaba con MySQL, adapté mi código como el que se muestra a continuación, ejecutándome igualmente bien en MySQL como en mi actual base de datos absoluta. El motivo es el manejo NULL específico que evita la trampa de dBase, Paradox y Absolute Database que aceptan generosamente COUNT (NULL) = 0 no aceptado en las bases de datos principales. Así que creyendo que esto funcionará bien en la mayoría de las bases de datos (manejando CASE ...) este es mi código adaptado:

SELECT
  sub.TypeName
, SUM(sub.AssetCase) AS "Total"
, SUM(CASE WHEN sub.StatusName = ''Deployed'' THEN sub.AssetCase ELSE 0 END) AS "Deployed"
, SUM(CASE WHEN sub.StatusName = ''Inventory'' THEN sub.AssetCase ELSE 0 END) AS "Inventory"
, SUM(CASE WHEN sub.StatusName = ''Shipped'' THEN sub.AssetCase ELSE 0 END) AS "Shipped"
FROM
  (
   SELECT
     c.TypeName
   , c.StatusName
   , CASE WHEN a.AssetID IS NULL THEN 0 ELSE 1 END AS "AssetCase"
   FROM
     (
      SELECT
        t.ID AS tID
      , t.TypeName
      , s.ID AS sID
      , s.StatusName
      FROM
        AssetTypes t, AssetStatus s
     ) c
   LEFT JOIN Assets a
     ON a.AssetType = c.tID AND a.AssetStatus = c.sID
   ) sub
GROUP BY
  sub.TypeName
;

Saludos cordiales Niels Knabe

Answer 2

Este tipo de transformación se llama un pivote. No especificó qué base de datos está utilizando, por lo que le proporcionaré respuestas para SQL Server y MySQL.

SQL Server: si está utilizando SQL Server 2005+, puede implementar la función PIVOT .

Si tiene un número conocido de valores que desea convertir en columnas, puede codificar la consulta.

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;

Ver SQL Fiddle con Demo .

Pero si tiene un número desconocido de valores de status , entonces necesitará usar sql dinámico para generar la lista de columnas en tiempo de ejecución.

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)

Ver SQL Fiddle con Demo.

Esto también se puede escribir usando una función agregada con una expresión de caso:

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

Ver SQL Fiddle con Demo.

MySQL: esta base de datos no tiene una función dinámica , por lo que tendrá que usar la función agregada y una expresión CASE . Tampoco tiene funciones de ventanas, por lo que tendrá que modificar la consulta ligeramente a lo siguiente:

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;

Ver SQL Fiddle con Demo.

Entonces, si necesita una solución dinámica en MySQL, tendrá que usar una declaración preparada para generar la cadena sql para ejecutar:

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;

Ver SQL Fiddle con Demo .

El resultado es el mismo para todas las consultas en ambas bases de datos:

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