sql server - right - sql cross join: ¿qué uso ha encontrado alguien para él?
sql inner left join (8)
Personalmente trato de evitar productos cartesianos en mis consultas. Supongo que tener un conjunto de resultados de cada combinación de tu unión podría ser útil, pero generalmente si termino con uno, sé que tengo algo mal.
Hoy, por primera vez en 10 años de desarrollo con servidor sql, utilicé una combinación cruzada en una consulta de producción. Necesitaba rellenar un conjunto de resultados para un informe y descubrí que una combinación cruzada entre dos tablas con una cláusula creativa where era una buena solución. Me preguntaba qué uso ha encontrado alguien en el código de producción para la unión cruzada.
Actualización: el código publicado por Tony Andrews está muy cerca de lo que usé para la combinación cruzada. Créame, entiendo las implicaciones de usar un cruce y no lo haría a la ligera. Estaba emocionado de haberlo usado finalmente (soy un nerd) - algo así como la vez que usé por primera vez una combinación externa completa.
Gracias a todos por las respuestas! Así es como utilicé la combinación cruzada:
SELECT CLASS, [Trans-Date] as Trans_Date,
SUM(CASE TRANS
WHEN ''SCR'' THEN [Std-Labor-Value]
WHEN ''S+'' THEN [Std-Labor-Value]
WHEN ''S-'' THEN [Std-Labor-Value]
WHEN ''SAL'' THEN [Std-Labor-Value]
WHEN ''OUT'' THEN [Std-Labor-Value]
ELSE 0
END) AS [LABOR SCRAP],
SUM(CASE TRANS
WHEN ''SCR'' THEN [Std-Material-Value]
WHEN ''S+'' THEN [Std-Material-Value]
WHEN ''S-'' THEN [Std-Material-Value]
WHEN ''SAL'' THEN [Std-Material-Value]
ELSE 0
END) AS [MATERIAL SCRAP],
SUM(CASE TRANS WHEN ''RWK'' THEN [Act-Labor-Value] ELSE 0 END) AS [LABOR REWORK],
SUM(CASE TRANS
WHEN ''PRD'' THEN [Act-Labor-Value]
WHEN ''TRN'' THEN [Act-Labor-Value]
WHEN ''RWK'' THEN [Act-Labor-Value]
ELSE 0
END) AS [ACTUAL LABOR],
SUM(CASE TRANS
WHEN ''PRD'' THEN [Std-Labor-Value]
WHEN ''TRN'' THEN [Std-Labor-Value]
ELSE 0
END) AS [STANDARD LABOR],
SUM(CASE TRANS
WHEN ''PRD'' THEN [Act-Labor-Value] - [Std-Labor-Value]
WHEN ''TRN'' THEN [Act-Labor-Value] - [Std-Labor-Value]
--WHEN ''RWK'' THEN [Act-Labor-Value]
ELSE 0 END) -- - SUM([Std-Labor-Value]) -- - SUM(CASE TRANS WHEN ''RWK'' THEN [Act-Labor-Value] ELSE 0 END)
AS [LABOR VARIANCE]
FROM v_Labor_Dist_Detail
where [Trans-Date] between @startdate and @enddate
--and CLASS = (CASE @class WHEN ''~ALL'' THEN CLASS ELSE @class END)
GROUP BY [Trans-Date], CLASS
UNION --REL 2/6/09 Pad result set with any missing dates for each class.
select distinct [Description] as class, cast([Date] as datetime) as [Trans-Date], 0,0,0,0,0,0
FROM Calendar_To_Fiscal cross join PRMS.Product_Class
where cast([Date] as datetime) between @startdate and @enddate and
not exists (select class FROM v_Labor_Dist_Detail vl where [Trans-Date] between @startdate and @enddate
and vl.[Trans-Date] = cast(Calendar_To_Fiscal.[Date] as datetime)
and vl.class= PRMS.Product_Class.[Description]
GROUP BY [Trans-Date], CLASS)
order by [Trans-Date], CLASS
Tengo diferentes informes que prefiltran el conjunto de registros (por varias líneas de negocios dentro de la empresa), pero hubo cálculos que requerían porcentajes de ingresos en toda la empresa. La fuente de registro debía contener el total de la empresa en lugar de confiar en el cálculo de la suma global en el informe en sí.
Ejemplo: el conjunto de registros tiene saldos para cada cliente y la línea de negocio de la que provienen los ingresos del cliente. El informe solo puede mostrar clientes ''minoristas''. No hay forma de obtener una suma de los saldos de toda la empresa, pero el informe muestra el porcentaje de los ingresos de la empresa.
Dado que hay diferentes campos de saldo, sentí que era menos complicado tener una unión completa con la vista que tiene varios saldos (también puedo reutilizar esta vista de totales firmes) en lugar de múltiples campos formados por subconsultas.
Otra es una declaración de actualización donde se deben crear múltiples registros (un registro para cada paso en un proceso de flujo de trabajo preestablecido).
Un uso legítimo típico de una unión cruzada sería un informe que muestre, por ejemplo, las ventas totales por producto y región. Si no se hicieron ventas del producto P en la región R, entonces queremos ver una fila con un cero, en lugar de simplemente no mostrar una fila.
select r.region_name, p.product_name, sum(s.sales_amount)
from regions r
cross join products p
left outer join sales s on s.region_id = r.region_id
and s.product_id = p.product_id
group by r.region_name, p.product_name
order by r.region_name, p.product_name;
Un uso que he encontrado mucho es dividir los registros en varios registros, principalmente con fines informativos.
Imagine una secuencia donde cada personaje representa algún evento durante la hora correspondiente.
ID | Hourly Event Data
1 | -----X-------X-------X--
2 | ---X-----X------X-------
3 | -----X---X--X-----------
4 | ----------------X--X-X--
5 | ---X--------X-------X---
6 | -------X-------X-----X--
Ahora quiere un informe que muestre cuántos eventos sucedieron en qué día. Cruza la mesa con una tabla de ID 1 a 24, luego trabaja tu magia ...
SELECT
[hour].id,
SUM(CASE WHEN SUBSTRING([data].string, [hour].id, 1) = ''X'' THEN 1 ELSE 0 END)
FROM
[data]
CROSS JOIN
[hours]
GROUP BY
[hours].id
=>
1, 0
2, 0
3, 0
4, 2
5, 0
6, 2
7, 0
8, 1
9, 0
10, 2
11, 0
12, 0
13, 2
14, 1
15, 0
16, 1
17, 2
18, 0
19, 0
20, 1
21, 1
22, 3
23, 0
24, 0
Recientemente, utilicé CROSS JOIN en un informe que utilizamos para el forcasting de ventas, el informe debe dividir la cantidad de ventas que ha hecho una persona de ventas en cada cuenta del Libro mayor.
Entonces, en el informe, hago algo en este sentido:
SELECT gla.AccountN, s.SalespersonN
FROM
GLAccounts gla
CROSS JOIN Salesperson s
WHERE (gla.SalesAnalysis = 1 OR gla.AccountN = 47500)
Esto me da cada cuenta GL para cada persona de ventas como:
SalesPsn AccountN 1000 40100 1000 40200 1000 40300 1000 48150 1000 49980 1000 49990 1005 40100 1005 40200 1005 40300 1054 48150 1054 49980 1054 49990 1078 40100 1078 40200 1078 40300 1078 48150 1078 49980 1078 49990 1081 40100 1081 40200 1081 40300 1081 48150 1081 49980 1081 49990 1188 40100 1188 40200 1188 40300 1188 48150 1188 49980 1188 49990
Para gráficos (informes) donde cada agrupación debe tener un registro, incluso si es cero. (por ejemplo, RadCharts)
Aquí hay uno, donde CROSS JOIN sustituye a INNER JOIN. Esto es útil y legítimo cuando no hay valores idénticos entre dos tablas en las que unirse. Por ejemplo, supongamos que tiene una tabla que contiene la versión 1, la versión 2 y la versión 3 de alguna declaración o documento de empresa, todo guardado en una tabla de SQL Server para que pueda recrear un documento que está asociado con una orden, sobre la marcha, mucho después del pedido, y mucho después de que su documento haya sido reescrito en una nueva versión. Pero solo una de las dos tablas a las que debe unirse (la tabla Documentos) tiene una columna VersionID. Aquí hay una manera de hacer esto:
SELECT DocumentText, VersionID =
(
SELECT d.VersionID
FROM Documents d
CROSS JOIN Orders o
WHERE o.DateOrdered BETWEEN d.EffectiveStart AND d.EffectiveEnd
)
FROM Documents
Tenía combinaciones de campo de insolvencia de mis datos de origen. Hay 5 tipos distintos pero los datos tienen combinaciones de 2 de estos. Así que creé la tabla de búsqueda de los 5 valores distintos y usé una combinación cruzada para una declaración de inserción para completar el resto. al igual que
insert into LK_Insolvency (code,value)
select a.code+b.code, a.value+'' ''+b.value
from LK_Insolvency a
cross join LK_Insolvency b
where a.code <> b.code <--this makes sure the x product of the value with itself is not used as this does not appear in the source data.