求一SQL - 墨天轮问答

求一SQL

我来答

2023-04-07

求一SQL

with cte as
(
select 1 as id,100 as total 
union all
select 1 as id,100 as total
union all
select 2 as id,100 as total
union all
select 3 as id,33 as total
)
select sum(total) from cte
复制

上述结果为333
但我实际想要的结果为233 当id重复的时候取一行数据就可以了。

我有复杂的实现算法，但由于业务SQL很复杂，不能适用

贴一下复杂的解决方案供交流

with cte as
(
select 1 as id,100 as total 
union all
select 1 as id,100 as total
union all
select 2 as id,100 as total
union all
select 3 as id,33 as total
),
cte1 as
(
select @id,case when id = @id then 0 else total end as calctotal,
@id:= id
 from cte,(select @id := -1) as rr
)
select sum(calctotal) from cte1;
复制

这样操作后结果就为233.

我来答

添加附件

问题补充

4条回答

默认

最新

不期而遇

with cte as
(select 1 as id, 100 as total
from dual
union all
select 1 as id, 100 as total
from dual
union all
select 2 as id, 100 as total
from dual
union all
select 3 as id, 33 as total from dual)
select sum(tmp.total)
from (select id,
total,
row_number() over(partition by id order by id) as line
from cte) tmp
where tmp.line = 1
试试这个

有用 0

打赏 0

刘贵宾

这样试试呢

WITH cte AS (
  SELECT DISTINCT id, total 
  FROM (
    SELECT 1 AS id, 100 AS total UNION ALL
    SELECT 1 AS id, 100 AS total UNION ALL
    SELECT 2 AS id, 100 AS total UNION ALL
    SELECT 3 AS id, 33 AS total
  ) t
)
SELECT SUM(total) FROM cte;复制