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? (2)
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
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
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)
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
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;
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 |