sql - 添加另一个表ORACLE SQL时Sum值太大

Question

select NVL((select name from supplier where id = t.id_supplier), 'All suppliers') as suppliers,  
        NVL((select name from stadium where id = m.id_stadium), 'All stadium') as stadium,   
        NVL((select name from league where id = m.id_league), 'All league') as league, 
        (NVL(sum(b.price), 0) + sum(t.cost)) as incomes
FROM match m
inner join ticket b on m.id = b.id_match
inner join stadion s on s.id = m.id_stadium
inner join league l on l.id = m.id_league
inner join transmission t on t.id = m.id_transmission
inner join supplier d on d.id = t.id_supplier
group by cube(d.id, s.id, l.id);

当我想将两个不同表格中的两个值相加时——比赛中售罄的门票和电视传输我得到的总和值太大了。我发现单场比赛的传输成本太大了 X 倍，其中 X 是这场比赛的门票数量，只有当我加入门票表时。我该如何修复这个？

我在这里找到了类似问题的解决方案：SQL：加入表后 SUM() 函数返回错误值 ，但它不适用于多维数据集。

例如，匹配 id 1 的 bilets 总和为 410.5：

select sum(b.price) from match m
inner join ticket b on m.id = b.id_match
where m.id = 1;

给出的结果 410.5 如此正确。传输的总和是 300 000：

select sum(t.cost) from match m
inner join transmission t on m.id_transmission = t.id
where m.id = 1;

它也返回正确的值。但是当我添加一张票表时，我得到了 1200410,5 值，这是不正确的：

select sum(t.cost) + sum(b.price) from match m
inner join transmission t on m.id_transmission = t.id
inner join ticket b on b.id_match= m.id
where m.id = 1;

正确的值应该是 300410.5。

标签： sqloracle

Answer 1

阅读您的问题后，以下查询给出的结果 410.5 非常正确，但计数返回 4（本场比赛的门票数）

   select sum(b.price), count(*) as cnt from match m
   inner join ticket b on m.id = b.id_match
   where m.id = 1;

第二个查询返回 300 000 并且计数返回 1（仅传输一次）

select sum(t.cost), count(*) cnt from match m
inner join transmission t on m.id_transmission = t.id
where m.id = 1;

当您加入 3 个表时，您现在有 4 行用于最后一个查询（传输），而不仅仅是预期的 1 行。这就是为什么你发现单场比赛的传输成本太大了 X 倍，其中 X 是这场比赛的门票数量。运行以下查询以验证

select m.id_transmission, cost  from match m
inner join transmission t on m.id_transmission = t.id
inner join ticket b on b.id_match= m.id
where m.id = 1;

ID_TRANSMISSION	成本
5	300000
5	300000
5	300000
5	300000

因此，解决方案是加入聚合查询而不是单个表。下面是一个使用 SQL WITH 语法构建子查询的示例

with agg_ticket as (select id_match, sum(b.price) as price from ticket b group by id_match) 
select sum(t.cost) + sum(b.price)  from match m
inner join transmission t on m.id_transmission = t.id
inner join agg_ticket  b on b.id_match= m.id
where m.id = 1;

如果传输查询的计数返回大于 1（计数>1），您还需要创建一个子查询

with agg_ticket as (select id_match, sum(b.price) as price from ticket b group by id_match) ,
     agg_transmission as (select id, sum(t.cost) as cost from transmission t group by id)
select sum(t.cost) + sum(b.price)  from match m
inner join agg_transmission t on m.id_transmission = t.id
inner join agg_ticket  b on b.id_match= m.id
where m.id = 1;

这将返回正确的值 300410.5

然后，你可以添加多维数据集语法，我没有所有的表和数据来测试

with agg_ticket as (select id_match, sum(b.price) as price from ticket b group by id_match) ,
     agg_transmission as (select id, sum(t.cost) as cost from transmission t group by id)
select nvl(CAST (m.id AS VARCHAR2(2000)), 'ALL MATCHS') as idMatch,
m.id,
sum(t.cost) + sum(b.price)  from match m
inner join agg_transmission t on m.id_transmission = t.id
inner join agg_ticket  b on b.id_match= m.id
group by cube (m.id);

希望它可以帮助

顺便说一句，如果您遇到以下错误： ORA-00979: not a GROUP BY expression when running this query

select NVL((select name from supplier where id = t.id_supplier), 'All suppliers') as suppliers,  
        NVL((select name from stadium where id = m.id_stadium), 'All stadium') as stadium,   
        NVL((select name from league where id = m.id_league), 'All league') as league, 
        (NVL(sum(b.price), 0) + sum(t.cost)) as incomes
FROM match m
inner join ticket b on m.id = b.id_match
inner join stadion s on s.id = m.id_stadium
inner join league l on l.id = m.id_league
inner join transmission t on t.id = m.id_transmission
inner join supplier d on d.id = t.id_supplier
group by cube(d.id, s.id, l.id);

这是因为，您必须在 SELECT 中使用group by cube中使用的相同列。因为您正在使用 SELECT 表达式执行 NVL，所以请注意 NVL 表达式中的 WHERE 子句

NVL((select name from league where id = l.id), 'All league') as league

并不是

NVL((select name from league where id = m.id_league), 'All league') as league

即使l.id = m.id_league你正在做 group by cube(d.id, s.id, l.id );

在您的情况下，我建议修改 GROUP BY 并保留 SELECT ：

group by cube(t.id_supplier, m.id_stadium, m.id_league);