34,590
社区成员
发帖
与我相关
我的任务
分享
SELECT *,
SUM(要求和的列) OVER() AS 和
FROM (select DISTINCT ...
ORDER BY o.id DESC
) t
select DISTINCT o.id,os.cst,c.co,ctd.coame,t.na,t.tel
from
t_or_c toc
LEFT JOIN t_o o on o.id=toc.order_id
LEFT JOIN t_cou c ON o.course_id = c.id
LEFT JOIN t_tea t on c.user_id = t.uid
LEFT JOIN t_ortate os on o.state_id = os.id
LEFT JOIN t_coype_dic ctd ON ctd.id=c.course_type_id
LEFT JOIN t_code cm on cm.id=o.class_mode
where 1=1
and toc.sid = 0 and o.stid = 2 ORDER BY o.id DESC
-- 使用 sum , 加 over 关键
create table test(id int , num int )
go
insert into test values(1,5),(1,6),(1,7),(2,10),(2,11),(2,10)
go
select id , num , sum(num) over(partition by id) newcol from test
go
drop table test
go
(6 行受影响)
id num newcol
----------- ----------- -----------
1 5 18
1 6 18
1 7 18
2 10 31
2 11 31
2 10 31
(6 行受影响)
create table test
(
a int ,
b int ,
c int ,
sum as a+b+c
)
/* 测试数据
WITH table1(a,b)AS(
SELECT 1,5 UNION ALL
SELECT 2,6 UNION ALL
SELECT 3,7
) */
SELECT *,
SUM(b) OVER() AS c
FROM table1
a b c
----------- ----------- -----------
1 5 18
2 6 18
3 7 18