cteP是父表,字段v是值,需要求和
cteC是子表,因为实际过滤中需要用到cteC中的字段,所以做了连接。 with cteP(id,v) as
(
select 1,6
),
cteC (id,parentID) as
(
select 1,1
union
select 2,1
)
select sum(cteP.v)
from cteP left join cteC
on cteP.id=cteC.parentID以上连接后的结果集是2行,我想求cteP表字段v的合计.期望输出是:6
但以上输出的结果为:12
问题的原因我已经明白了,想知道在连接的情况下可否求cteP表字段v的合计呢?
谢谢。
cteC是子表,因为实际过滤中需要用到cteC中的字段,所以做了连接。 with cteP(id,v) as
(
select 1,6
),
cteC (id,parentID) as
(
select 1,1
union
select 2,1
)
select sum(cteP.v)
from cteP left join cteC
on cteP.id=cteC.parentID以上连接后的结果集是2行,我想求cteP表字段v的合计.期望输出是:6
但以上输出的结果为:12
问题的原因我已经明白了,想知道在连接的情况下可否求cteP表字段v的合计呢?
谢谢。
解决方案 »
- group by 时间段 要怎么写?一个月的,比较麻烦
- 调查一下,这里用sql server做后端数据库的,前端都在用什么工具做界面?
- 求教一条SQL语句。
- 跪求AdventureWorks2008示例资料库
- 文本导入问题
- 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x80004d01b
- 为什么我在已经存在记录的表里面添加int格式的字段并设置默认值为0时,有时候这个字段的值显示为0,有时候显示为null呢??
- 在ASP页面自动生成带有日期和数字的序列号
- 唤醒存储过程
- 一个SQL数据转换问题
- 存储过程和触发器
- 求问:这种情况如何处理
(
select 1,6
),
cteC (id,parentID) as
(
select 1,1
union
select 2,1
)
select sum(cteP.v)
from cteP left join cteC
on cteP.id=cteC.parentID
with cteP(id,v) as
(
select 1,6
),
cteC (id,parentID) as
(
select 1,1
union
select 2,1
)
select sum(cteP.v)
from cteP inner join cteC
on cteP.id=cteC.parentID
with cteP(id,v) as
(
select 1,6
),
cteC (id,parentID) as
(
select 1,1
union
select 2,1
)
select sum(cteP.v)
from cteP inner join cteC
on cteP.id=cteC.id
;with cteP(id,v) as
(
select 1,6
),
cteC (id,parentID) as
(
select 1,1
union
select 2,1
)
select sum(cteP.v)
from cteP inner join cteC
on cteP.id=cteC.id
(
select 1,6
),
cteC (id,parentID) as
(
select 1,1
union
select 2,1
)
select sum(cteP.v)
from cteP inner join cteC
on cteP.id=cteC.id/*
-----------
6(1 行受影响)
*/
insert into cteP values(1,6)
create table cteC (id int,parentID int)
insert into cteC values(1,1 )
insert into cteC values(2,1 )
goselect m.id,m.v from cteP m where exists(select 1 from cteC where parentID = m.id)/*id v
----------- -----------
1 6(所影响的行数为 1 行)
*/select m.id,sum(m.v) v from cteP m where exists(select 1 from cteC where parentID = m.id) group by m.id
/*id v
----------- -----------
1 6(所影响的行数为 1 行)
*/drop table cteP , ctec
只要是 P 和 C 是一对多 联查出来就是多条的和 翻倍的
(
select 1,6
),
cteC (id,parentID) as
(
select 1,1
union
select 2,1
)
select sum(distinct cteP.v)
from cteP left join cteC
on cteP.id=cteC.parentID
insert into cteP values(1,6)
create table cteC (id int,parentID int)
insert into cteC values(1,1 )
insert into cteC values(2,1 )
goselect m.id,m.v from cteP m where exists(select 1 from cteC where parentID = m.id)/*id v
----------- -----------
1 6(所影响的行数为 1 行)
*/select m.id,sum(m.v) v from cteP m where exists(select 1 from cteC where parentID = m.id) group by m.id
/*id v
----------- -----------
1 6(所影响的行数为 1 行)
*/select m.id,sum(m.v) v from cteP m where id in (select distinct parentID from cteC ) group by m.id
/*
id v
----------- -----------
1 6(所影响的行数为 1 行)
*/drop table cteP , ctec
(
select 1,6
),
cteC (id,parentID) as
(
select 1,1
union
select 2,1
)
select distinct sum(cteP.v)
from cteP inner join cteC
on cteP.id=cteC.parentid
group by ctec.id/*
6
*/