sql - 每列需要一个总数，并为空数据点显示 0
问题描述
我有一个脚本（如下），它有多个连接列，我需要在底部的最后一行加上每列的总和。我知道 sum 函数，但我不知道将它放在脚本中的哪个位置。我还想知道如何或在何处合并此语句SELECT ISNULL(myColumn, 0 ) FROM myTable，以便空值显示 0。
SELECT A.[start_tran_hour],
[singles picked],
[single packed],
[multis picked],
[units sorted],
[multis packed],
[sa packed],
[sa picked],
[total picked],
[total packed],
[total shipped]
FROM (SELECT Datepart(hour, start_tran_time)start_tran_hour,
Sum(tran_qty) AS 'Total Shipped'
FROM t_tran_log WITH(nolock)
WHERE tran_type IN ( '340', '341' )
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time))A
LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
Sum(tran_qty) AS 'Total Picked'
FROM t_tran_log WITH(nolock)
WHERE tran_type = '301'
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time))B
ON A.start_tran_hour = B.start_tran_hour
LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
Sum(tran_qty) AS 'Single Packed'
FROM t_tran_log WITH(nolock)
WHERE tran_type = '315'
AND description = 'Single Packing'
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time))C
ON A.start_tran_hour = C.start_tran_hour
LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
Sum(tran_qty) AS 'Singles Picked'
FROM t_tran_log WITH(nolock)
LEFT JOIN t_order WITH(nolock)
ON
t_tran_log.control_number = t_order.order_number
WHERE tran_type = '301'
AND t_order.route = 'SINGLE'
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time))D
ON A.start_tran_hour = D.start_tran_hour
LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
Sum(tran_qty) AS 'Multis Picked'
FROM t_tran_log WITH(nolock)
LEFT JOIN t_order WITH(nolock)
ON
t_tran_log.control_number = t_order.order_number
WHERE tran_type = '301'
AND t_order.route = 'MULTI'
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time))E
ON A.start_tran_hour = E.start_tran_hour
LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
Sum(tran_qty) AS 'Multis Packed'
FROM t_tran_log WITH(nolock)
WHERE tran_type = '315'
AND description = 'Multi Packing'
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time))F
ON A.start_tran_hour = F.start_tran_hour
LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
Sum(tran_qty) AS 'SA Picked'
FROM t_tran_log WITH(nolock)
WHERE tran_type = '301'
AND ( location_id LIKE 'PR%'
OR location_id LIKE 'SA%' )
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time))G
ON A.start_tran_hour = G.start_tran_hour
LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
Sum(tran_qty) AS 'SA Packed'
FROM t_tran_log WITH(nolock)
WHERE tran_type = '315'
AND ( location_id LIKE 'PACKSA%' )
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time))H
ON A.start_tran_hour = H.start_tran_hour
LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
Sum(tran_qty) AS 'Units Sorted'
FROM t_tran_log WITH(nolock)
WHERE tran_type = '311'
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time))I
ON A.start_tran_hour = I.start_tran_hour
LEFT JOIN (SELECT Datepart(hour, start_tran_time)start_tran_hour,
Sum(tran_qty) AS 'Total Packed'
FROM t_tran_log WITH(nolock)
WHERE tran_type = '315'
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY ( Datepart(hour, start_tran_time) ))J
ON A.start_tran_hour = J.start_tran_hour
ORDER BY A.start_tran_hour
解决方案
我假设您有几个小时的时间，列中的每个度量值都为空（或者根本没有）所以SUM(all_null_values)= NULL，您希望它为 0。
这将处理空值并添加总行。
SELECT A.[start_tran_hour],
, sum(coalesce([singles picked], 0)) as 'singles picked'
, sum(coalesce([single packed], 0)) as 'single packed'
, sum(coalesce([multis picked], 0)) as 'multis picked'
, sum(coalesce([units sorted], 0)) as 'units sorted'
, sum(coalesce([multis packed], 0)) as 'multis packed'
, sum(coalesce([sa packed], 0)) as 'sa packed'
, sum(coalesce([sa picked], 0)) as 'sa picked'
, sum(coalesce([total picked], 0)) as 'total picked'
, sum(coalesce([total packed], 0)) as 'total packed'
, sum(coalesce([total shipped], 0)) as 'total shipped'
FROM (
SELECT Datepart(hour, start_tran_time) as start_tran_hour
, Sum(tran_qty) AS 'Total Shipped'
FROM t_tran_log WITH(nolock)
WHERE tran_type IN ( '340', '341' )
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time)
)A
LEFT JOIN (
SELECT Datepart(hour, start_tran_time) as start_tran_hour
, Sum(tran_qty) AS 'Total Picked'
FROM t_tran_log WITH(nolock)
WHERE tran_type = '301'
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time)
)B ON A.start_tran_hour = B.start_tran_hour
LEFT JOIN (
SELECT Datepart(hour, start_tran_time) as start_tran_hour
, Sum(tran_qty) AS 'Single Packed'
FROM t_tran_log WITH(nolock)
WHERE tran_type = '315'
AND description = 'Single Packing'
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time)
)C ON A.start_tran_hour = C.start_tran_hour
LEFT JOIN (
SELECT Datepart(hour, start_tran_time) as start_tran_hour
, Sum(tran_qty) AS 'Singles Picked'
FROM t_tran_log WITH(nolock)
LEFT JOIN t_order WITH(nolock) ON t_tran_log.control_number = t_order.order_number
WHERE tran_type = '301'
AND t_order.route = 'SINGLE'
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time)
)D ON A.start_tran_hour = D.start_tran_hour
LEFT JOIN (
SELECT Datepart(hour, start_tran_time) as start_tran_hour
, Sum(tran_qty) AS 'Multis Picked'
FROM t_tran_log WITH(nolock)
LEFT JOIN t_order WITH(nolock) ON t_tran_log.control_number = t_order.order_number
WHERE tran_type = '301'
AND t_order.route = 'MULTI'
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time)
)E ON A.start_tran_hour = E.start_tran_hour
LEFT JOIN (
SELECT Datepart(hour, start_tran_time) as start_tran_hour
, Sum(tran_qty) AS 'Multis Packed'
FROM t_tran_log WITH(nolock)
WHERE tran_type = '315'
AND description = 'Multi Packing'
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time)
)F ON A.start_tran_hour = F.start_tran_hour
LEFT JOIN (
SELECT Datepart(hour, start_tran_time) as start_tran_hour
, Sum(tran_qty) AS 'SA Picked'
FROM t_tran_log WITH(nolock)
WHERE tran_type = '301'
AND ( location_id LIKE 'PR%'
OR location_id LIKE 'SA%' )
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time)
) G ON A.start_tran_hour = G.start_tran_hour
LEFT JOIN (
SELECT Datepart(hour, start_tran_time) as start_tran_hour
, Sum(tran_qty) AS 'SA Packed'
FROM t_tran_log WITH(nolock)
WHERE tran_type = '315'
AND ( location_id LIKE 'PACKSA%' )
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time)
) H ON A.start_tran_hour = H.start_tran_hour
LEFT JOIN (
SELECT Datepart(hour, start_tran_time) as start_tran_hour
, Sum(tran_qty) AS 'Units Sorted'
FROM t_tran_log WITH(nolock)
WHERE tran_type = '311'
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY Datepart(hour, start_tran_time)
) I ON A.start_tran_hour = I.start_tran_hour
LEFT JOIN (
SELECT Datepart(hour, start_tran_time) as start_tran_hour
, Sum(tran_qty) AS 'Total Packed'
FROM t_tran_log WITH(nolock)
WHERE tran_type = '315'
AND Cast(start_tran_date AS DATE) = '2021-07-06'
GROUP BY ( Datepart(hour, start_tran_time) )
) J ON A.start_tran_hour = J.start_tran_hour
group by rollup (A.start_tran_hour)
ORDER BY A.start_tran_hour
但是，它仍然包含LEFT INNER JOIN原始查询中的（不是真实的东西。我编造了那个词。）。
FROM t_tran_log
LEFT JOIN t_order ON t_tran_log.control_number = t_order.order_number
WHERE tran_type = '301'
AND t_order.route = 'MULTI'
包含t_order.route = 'MULTI'在WHERE子句中会导致LEFT JOIN成为INNER JOIN. 所以我们要解决这个问题。
但约翰卡佩莱蒂的建议也不错。让我们尝试在FROM子句中引用一次表并计算子句中的每一列SELECT。
SELECT Datepart(hour, start_tran_time) as start_tran_hour,
, sum(
case
when tran_type = '301'
AND t_order.route = 'SINGLE'
then [singles picked]
else 0
end) as 'singles picked'
, sum(
case
when tran_type = '315'
AND description = 'Single Packing'
then [single packed]
else 0
end) as 'single packed'
, sum(
case
when tran_type = '301'
AND t_order.route = 'MULTI'
then [multis picked]
else 0
end) as 'multis picked'
, sum(
case
when tran_type = '311'
then [units sorted]
else 0
end) as 'units sorted'
, sum(
case
when tran_type = '315'
AND description = 'Multi Packing'
then [multis packed]
else 0
end) as 'multis packed'
, sum(
case
when tran_type = '315'
AND location_id LIKE 'PACKSA%'
then [sa packed]
else 0
end) as 'sa packed'
, sum(
case
when tran_type = '301'
AND ( location_id LIKE 'PR%'
OR location_id LIKE 'SA%' )
then [sa picked]
else 0
end) as 'sa picked'
, sum(
case
when tran_type = '301'
then [total picked]
else 0
end) as 'total picked'
, sum(
case
when tran_type = '315'
then [total packed]
else 0
end) as 'total packed'
, sum(
case
when tran_type IN ( '340', '341' )
then [total shipped]
else 0
end) as 'total shipped'
from t_tran_log WITH(nolock)
LEFT JOIN t_order WITH(nolock) ON t_tran_log.control_number = t_order.order_number
AND t_order.route in ('SINGLE', 'MULTI')
where Cast(start_tran_date AS DATE) = '2021-07-06'
and tran_type IN ('340', '341', '301', '315', '311')
group by rollup (Datepart(hour, start_tran_time))
ORDER BY Datepart(hour, start_tran_time)
如果没有数据库架构和示例数据，我无法确定其中任何一个都行得通。下一次，创建并引用一个 dbfiddle。
推荐阅读
- constraints - Pyomo 约束 > 100000 或 0
- python - 如何将字符串列表向右对齐？
- swift5.1 - Swit 5.1 将 CoreData 添加到现有项目但我收到此错误
- c# - Xamarin：构建时出错 -> 文件路径无效 'obj\Debug\90\res\views\layouttest.xml'
- python - NumPy 数组赋值期间无限的 __getitem__ 调用
- python - sqlite3.IntegrityError：数据类型不匹配
- google-analytics - 客户 ID 不正确
- java - 如果适用，使用子类的方法
- php - php 表值显示不正确
- matlab - 在气候数据运算符中使用 timeselavg 时出错