SELECT
sum([amount])
FROM [Ex06]
where [qyid]='111049'
and
[classcode] in
(SELECT
[code]
FROM calss1
where id='c325' or id='c321'
)
and [calss1] in ('1','5')根据次语句 聚合出来的数据 仅是id='c325' 的聚合 没有id='c321' 的聚合如何把两个情况全部加上
id='c325' or id='c321'
的总聚合
???
sum([amount])
FROM [Ex06]
where [qyid]='111049'
and
[classcode] in
(SELECT
[code]
FROM calss1
where id='c325' or id='c321'
)
and [calss1] in ('1','5')根据次语句 聚合出来的数据 仅是id='c325' 的聚合 没有id='c321' 的聚合如何把两个情况全部加上
id='c325' or id='c321'
的总聚合
???
解决方案 »
- sqlserver2005 远程链接sql2000的数据库报了如下错误,请高手支招!
- SQL求助
- Microsoft SQL Server 2008 R2 SP2 - Express Edition安装问题
- 请教高手:将excel导入数据库时出现问题
- 这个sql脚本应该怎么写呢?
- ACCESS窗体文本框(字段)与插入对象EXCEL图表单元格绑定问题!
- 更改BOM表
- MS sqlserver 2000怎么批量导入导出图片数据(jpg)?
- 数据类型问题
- SQLserver 中 decimal(18,8)类型的奇怪现象
- middlegenide连接sqlserver数据库为什么在mssqlserver.jar包中找不到驱动?
- 数据不出来,帮帮忙
SELECT
sum([amount])
FROM [Ex06]
where [qyid]='111049'
and
[classcode] in
(SELECT
[code]
FROM calss1
where id='c325' or id='c321'
)
and [calss2] in ('1','5')
[code]
FROM calss1
where id='c325' or id='c321' 这样查出来包含id='c321'的数据吗?是否是其他条件限制了
SELECT
sum([amount])
FROM [Ex06]
where [qyid]='111049'
and
[classcode] in
(SELECT
[code]
FROM calss1
where id='c321'
)
and [calss1] in ('1','5')
union all
SELECT
sum([amount])
FROM [Ex06]
where [qyid]='111049'
and
[classcode] in
(SELECT
[code]
FROM calss1
where id='c325'
)
and [calss1] in ('1','5')
[code]
FROM calss1
where id='c325' or id='c321'
可以出来 包括 id='c325' or id='c321' 的相关类别
可以得到两个列国
但要将结果sum 呢?
sum([amount])
FROM [Ex06]
where [qyid]='111049'
and
[classcode] in
(SELECT
[code]
FROM calss1
where id='c321'
)
and [calss1] in ('1','5')
union all
SELECT
sum([amount])
FROM [Ex06]
where [qyid]='111049'
and
[classcode] in
(SELECT
[code]
FROM calss1
where id='c325'
)
and [calss1] in ('1','5')可以得到两个结果 而且准确
但需求是是 sum 两个结果
??????????
select sum(a.amount) from
(SELECT
sum([amount]) as amount
FROM [Ex06]
where [qyid]='111049'
and
[classcode] in
(SELECT
[code]
FROM calss1
where id='c321'
)
and [calss1] in ('1','5')
union all
SELECT
sum([amount]) as amount
FROM [Ex06]
where [qyid]='111049'
and
[classcode] in
(SELECT
[code]
FROM calss1
where id='c325'
)
and [calss1] in ('1','5') ) a
from (
SELECT
sum([amount]) as sumamount
FROM [Ex06]
where [qyid]='111049'
and
[classcode] in
(SELECT
[code]
FROM calss1
where id='c321'
)
and [calss1] in ('1','5')
union all
SELECT
sum([amount])
FROM [Ex06]
where [qyid]='111049'
and
[classcode] in
(SELECT
[code]
FROM calss1
where id='c325'
)
and [calss1] in ('1','5')
) t
select sum(a.amount) from
(SELECT
sum([amount]) as amount
FROM [Ex06]
where [qyid]='111049'
and
[classcode] in
(SELECT
[code]
FROM calss1
where id='c321'
)
and [calss1] in ('1','5')
union all
SELECT
sum([amount]) as amount
FROM [Ex06]
where [qyid]='111049'
and
[classcode] in
(SELECT
[code]
FROM calss1
where id='c325'
)
and [calss1] in ('1','5') ) a是得到 某一条 即 where [qyid]='111049' 的企业汇总数据以上代码的确可以实现但 我需要的是
按企业代码 qyid 汇总 该企业金额
1个企业 在该表中存在多条记录统计初 所有企业 按条件 的汇总金额按条件
即classcode 分类编码等于 另一个表的结果集 'c321' or 'c325' calss1 in ('1','5')
qyid 企业ID
amount 金额
classcode 区域代码
calss1 经营方式代码-------------------
calss1 区域代码表
code 分类代码
id 区域代码Ex06 企业经营业绩表
该表 存在 多条重复企业信息
统计初 所有企业 按条件 的汇总金额 按条件指
classcode 区域代码
=SELECT
[code]
FROM calss1
where id='c321' or id='c325'
并且
calss1 经营方式代码 =1 or =5
from ex06 join calss1 on classcode=code
where id='c321' or id='c325' and qyid.calss1 in ('1','5')
group by