create table 检查类型( A_NO int, A_title varchar(20), A_tag varchar(20) )insert into 检查类型 values(1 ,'胃' ,'1-04') insert into 检查类型 values(2 ,'鼻' ,'1-04') insert into 检查类型 values(3 ,'其它1' ,'1-04') insert into 检查类型 values(4 ,'其它2' ,'1-04') insert into 检查类型 values(5 ,'其它3' ,'1-04') insert into 检查类型 values(6 ,'其它4' ,'1-04') insert into 检查类型 values(1 ,'标题1' ,'2-04') insert into 检查类型 values(2 ,'标题2' ,'2-04') insert into 检查类型 values(3 ,'其它3' ,'2-04') insert into 检查类型 values(4 ,'其它4' ,'2-04') insert into 检查类型 values(5 ,'其它5' ,'2-04') insert into 检查类型 values(6 ,'其它6' ,'2-04')create table 基本信息( B_info varchar(20), B_qydm varchar(20), B_MC varchar(20) )insert into 基本信息 values('X-001','4-002','企业名称1') insert into 基本信息 values('X-341','3-223','企业名称2') create table 检查结果( B_info varchar(20), A_NO int, C_true int, A_tag varchar(20) )insert into 检查结果 values('X-001' ,1 ,3 ,'1-04') insert into 检查结果 values('X-001' ,3 ,16 ,'1-04') insert into 检查结果 values('X-001' ,5 ,1 ,'1-04') insert into 检查结果 values('X-001' ,6 ,6 ,'1-04') insert into 检查结果 values('X-341' ,1 ,7 ,'1-04') insert into 检查结果 values('X-341' ,2 ,8 ,'1-04') insert into 检查结果 values('X-341' ,3 ,9 ,'1-04') insert into 检查结果 values('X-341' ,4 ,23 ,'1-04') 查询期望的结果: /****************************************************/ select d.A_title, d.counts, d.sums, cast(round(cast(d.counts as numeric(5,1))/c.count_all,3)*100 as numeric(4,1)) as 百分比1, cast(round(cast(d.sums as numeric(5,1))/c.sum_all,3)*100 as numeric(4,1)) as 百分比2 from (select isnull(b.A_title ,'合计') as A_title, count(a.C_true) as counts, sum(a.C_true) as sums from 检查结果 a inner join 检查类型 b on a.A_NO = b.A_NO and a.A_tag = b.A_tag where a.A_NO in(1,3) and a.A_tag = '1-04' group by b.A_title WITH ROLLUP) d inner join (select count(C_true) as count_all, sum(C_true) as sum_all from 检查结果 where ----如果要统计在全部内容中的百分比 A_NO in(1,3) ----则该条件语句可以不要 ) c on 1 = 1 order by d.A_title desc
2、 查询期望的结果: /****************************************************/ declare @a int declare @b intselect @a = count(C_true), @b = sum(C_true) from 检查结果 where A_NO in(1,3)select isnull(b.A_title ,'合计') as A_title, sum(case when a.C_true >3 then 1 else 0 end) as 数量大于3总数, sum(case when cast(a.C_true as numeric(5,1))/@a > 0.4 then 1 else 0 end) as 百分数大于40总数, count(a.C_true) as counts, sum(a.C_true) as sums, cast(round(cast(count(a.C_true) as numeric(5,1))/@a,3)*100 as numeric(4,1)) as 百分比1, cast(round(cast(sum(a.C_true) as numeric(5,1))/@b,3)*100 as numeric(4,1)) as 百分比2 from 检查结果 a inner join 检查类型 b on a.A_NO = b.A_NO and a.A_tag = b.A_tag where a.A_NO in(1,3) and a.A_tag = '1-04' group by b.A_title WITH ROLLUP 查询输出的结果: /****************************************************/ A_title 数量大于3 百分数大于40 counts sums 百分比1 百分比2 ----------------------------------------------------------------------- 其它1 2 2 2 25 50.0 71.4 胃 1 2 2 10 50.0 28.6 合计 3 4 4 35 100.0 100.0
to: long0104() 观察你很久了,尊重别人的劳动成果,尊重帖主,也尊重你自己 不要老是在帖子中复制别人的回复
select d.modtitle, d.counts, d.sums, cast(round(cast(d.counts as numeric(5,1))/c.count_all,3)*100 as numeric(4,1)) as 百分比1, cast(round(cast(d.sums as numeric(5,1))/c.sum_all,3)*100 as numeric(4,1)) as 百分比2 from (select isnull(b.modtitle ,'合计') as modtitle, count(CAST(a.resultinfo AS int) ) as counts, sum(CAST(a.resultinfo AS int)) as sums --count(a.C_true) as counts, --sum(a.C_true) as sums from S_RepairInforesult a inner join S_Repairprojectmod b on a.ZXFAModID = b.ZXFAModID and a.ZXFAID = b.ZXFAID where a.ZXFAModID in(1,2,3) and a.ZXFAID ='JDGL-20041229111209' group by b.modtitle WITH ROLLUP) d inner join (select count(CAST(resultinfo AS int)) as count_all, sum(CAST(resultinfo AS int)) as sum_all from S_RepairInforesult where ----如果要统计在全部内容中的百分比 ZXFAModID in(1,2,3) ----则该条件语句可以不要 ) c on 1 = 1 order by d.modtitle desc---------------------------- 那么 1 1 5.9 9.1 没有了 1 1 5.9 9.1 好的 1 1 5.9 9.1 合计 3 3 17.6 27.3
还是没明白你期望得到的数据是怎么样的关系 5.9%是当前一个A_NO与表中所有A_NO的数目的比例?select d.modtitle, d.counts, d.sums, cast(round(cast(d.counts as numeric(5,1))/c.count_all,3)*100 as numeric(4,1)) as 百分比1, cast(round(cast(d.sums as numeric(5,1))/c.sum_all,3)*100 as numeric(4,1)) as 百分比2 from (select isnull(b.modtitle ,'合计') as modtitle, count(CAST(a.resultinfo AS int) ) as counts, sum(CAST(a.resultinfo AS int)) as sums --count(a.C_true) as counts, --sum(a.C_true) as sums from S_RepairInforesult a inner join S_Repairprojectmod b on a.ZXFAModID = b.ZXFAModID and a.ZXFAID = b.ZXFAID where a.ZXFAModID in(1,2,3) and a.ZXFAID ='JDGL-20041229111209' group by b.modtitle WITH ROLLUP) d inner join (select count(CAST(resultinfo AS int)) as count_all, sum(CAST(resultinfo AS int)) as sum_all from S_RepairInforesult --where ZXFAID ='JDGL-20041229111209' --如果需要,把这一列加上 ) c on 1 = 1 order by d.modtitle desc
2、 查询期望的结果: /****************************************************/ declare @a int declare @b intselect @a = count(C_true), @b = sum(C_true) from 检查结果 where A_NO in(1,3)select isnull(b.A_title ,'合计') as A_title, sum(case when a.C_true >3 then 1 else 0 end) as 数量大于3总数, sum(case when cast(a.C_true as numeric(5,1))/@a > 0.4 then 1 else 0 end) as 百分数大于40总数, count(a.C_true) as counts, sum(a.C_true) as sums, cast(round(cast(count(a.C_true) as numeric(5,1))/@a,3)*100 as numeric(4,1)) as 百分比1, cast(round(cast(sum(a.C_true) as numeric(5,1))/@b,3)*100 as numeric(4,1)) as 百分比2 from 检查结果 a inner join 检查类型 b on a.A_NO = b.A_NO and a.A_tag = b.A_tag where a.A_tag = '1-04' -----酌情可以去掉或增加新的约束 group by b.A_title WITH ROLLUP
上面回错了:2、 查询期望的结果: /****************************************************/ declare @a int declare @b intselect @a = count(C_true), @b = sum(C_true) from 检查结果 --where A_NO in(1,3) --酌情可以去掉或增加新的条件限制select isnull(b.A_title ,'合计') as A_title, sum(case when a.C_true >3 then 1 else 0 end) as 数量大于3总数, sum(case when cast(a.C_true as numeric(5,1))/@a > 0.4 then 1 else 0 end) as 百分数大于40总数, count(a.C_true) as counts, sum(a.C_true) as sums, cast(round(cast(count(a.C_true) as numeric(5,1))/@a,3)*100 as numeric(4,1)) as 百分比1, cast(round(cast(sum(a.C_true) as numeric(5,1))/@b,3)*100 as numeric(4,1)) as 百分比2 from 检查结果 a inner join 检查类型 b on a.A_NO = b.A_NO and a.A_tag = b.A_tag where a.A_NO in(1,3) and a.A_tag = '1-04' group by b.A_title WITH ROLLUP
生成测试数据:
/****************************************************/
create table 检查类型(
A_NO int,
A_title varchar(20),
A_tag varchar(20)
)insert into 检查类型 values(1 ,'胃' ,'1-04')
insert into 检查类型 values(2 ,'鼻' ,'1-04')
insert into 检查类型 values(3 ,'其它1' ,'1-04')
insert into 检查类型 values(4 ,'其它2' ,'1-04')
insert into 检查类型 values(5 ,'其它3' ,'1-04')
insert into 检查类型 values(6 ,'其它4' ,'1-04')
insert into 检查类型 values(1 ,'标题1' ,'2-04')
insert into 检查类型 values(2 ,'标题2' ,'2-04')
insert into 检查类型 values(3 ,'其它3' ,'2-04')
insert into 检查类型 values(4 ,'其它4' ,'2-04')
insert into 检查类型 values(5 ,'其它5' ,'2-04')
insert into 检查类型 values(6 ,'其它6' ,'2-04')create table 基本信息(
B_info varchar(20),
B_qydm varchar(20),
B_MC varchar(20)
)insert into 基本信息 values('X-001','4-002','企业名称1')
insert into 基本信息 values('X-341','3-223','企业名称2')
create table 检查结果(
B_info varchar(20),
A_NO int,
C_true int,
A_tag varchar(20)
)insert into 检查结果 values('X-001' ,1 ,3 ,'1-04')
insert into 检查结果 values('X-001' ,3 ,16 ,'1-04')
insert into 检查结果 values('X-001' ,5 ,1 ,'1-04')
insert into 检查结果 values('X-001' ,6 ,6 ,'1-04')
insert into 检查结果 values('X-341' ,1 ,7 ,'1-04')
insert into 检查结果 values('X-341' ,2 ,8 ,'1-04')
insert into 检查结果 values('X-341' ,3 ,9 ,'1-04')
insert into 检查结果 values('X-341' ,4 ,23 ,'1-04') 查询期望的结果:
/****************************************************/
select
d.A_title,
d.counts,
d.sums,
cast(round(cast(d.counts as numeric(5,1))/c.count_all,3)*100 as numeric(4,1)) as 百分比1,
cast(round(cast(d.sums as numeric(5,1))/c.sum_all,3)*100 as numeric(4,1)) as 百分比2
from
(select
isnull(b.A_title ,'合计') as A_title,
count(a.C_true) as counts,
sum(a.C_true) as sums
from
检查结果 a
inner join
检查类型 b
on
a.A_NO = b.A_NO and a.A_tag = b.A_tag
where
a.A_NO in(1,3) and a.A_tag = '1-04'
group by
b.A_title WITH ROLLUP) d
inner join
(select
count(C_true) as count_all,
sum(C_true) as sum_all
from
检查结果
where ----如果要统计在全部内容中的百分比
A_NO in(1,3) ----则该条件语句可以不要
) c
on
1 = 1
order by d.A_title desc查询输出的结果:
/****************************************************/
A_title counts sums 百分比1 百分比2
--------------------------------------------
胃 2 10 50.0 28.6
其它1 2 25 50.0 71.4
合计 4 35 100.0 100.0
A_NO int,
A_title varchar(20),
A_tag varchar(20)
)insert into 检查类型 values(1 ,'胃' ,'1-04')
insert into 检查类型 values(2 ,'鼻' ,'1-04')
insert into 检查类型 values(3 ,'其它1' ,'1-04')
insert into 检查类型 values(4 ,'其它2' ,'1-04')
insert into 检查类型 values(5 ,'其它3' ,'1-04')
insert into 检查类型 values(6 ,'其它4' ,'1-04')
insert into 检查类型 values(1 ,'标题1' ,'2-04')
insert into 检查类型 values(2 ,'标题2' ,'2-04')
insert into 检查类型 values(3 ,'其它3' ,'2-04')
insert into 检查类型 values(4 ,'其它4' ,'2-04')
insert into 检查类型 values(5 ,'其它5' ,'2-04')
insert into 检查类型 values(6 ,'其它6' ,'2-04')create table 基本信息(
B_info varchar(20),
B_qydm varchar(20),
B_MC varchar(20)
)insert into 基本信息 values('X-001','4-002','企业名称1')
insert into 基本信息 values('X-341','3-223','企业名称2')
create table 检查结果(
B_info varchar(20),
A_NO int,
C_true int,
A_tag varchar(20)
)insert into 检查结果 values('X-001' ,1 ,3 ,'1-04')
insert into 检查结果 values('X-001' ,3 ,16 ,'1-04')
insert into 检查结果 values('X-001' ,5 ,1 ,'1-04')
insert into 检查结果 values('X-001' ,6 ,6 ,'1-04')
insert into 检查结果 values('X-341' ,1 ,7 ,'1-04')
insert into 检查结果 values('X-341' ,2 ,8 ,'1-04')
insert into 检查结果 values('X-341' ,3 ,9 ,'1-04')
insert into 检查结果 values('X-341' ,4 ,23 ,'1-04') 查询期望的结果:
/****************************************************/
select
d.A_title,
d.counts,
d.sums,
cast(round(cast(d.counts as numeric(5,1))/c.count_all,3)*100 as numeric(4,1)) as 百分比1,
cast(round(cast(d.sums as numeric(5,1))/c.sum_all,3)*100 as numeric(4,1)) as 百分比2
from
(select
isnull(b.A_title ,'合计') as A_title,
count(a.C_true) as counts,
sum(a.C_true) as sums
from
检查结果 a
inner join
检查类型 b
on
a.A_NO = b.A_NO and a.A_tag = b.A_tag
where
a.A_NO in(1,3) and a.A_tag = '1-04'
group by
b.A_title WITH ROLLUP) d
inner join
(select
count(C_true) as count_all,
sum(C_true) as sum_all
from
检查结果
where ----如果要统计在全部内容中的百分比
A_NO in(1,3) ----则该条件语句可以不要
) c
on
1 = 1
order by d.A_title desc
查询期望的结果:
/****************************************************/
declare @a int
declare @b intselect
@a = count(C_true),
@b = sum(C_true)
from
检查结果
where
A_NO in(1,3)select
isnull(b.A_title ,'合计') as A_title,
sum(case when a.C_true >3 then 1 else 0 end) as 数量大于3总数,
sum(case when cast(a.C_true as numeric(5,1))/@a > 0.4 then 1 else 0 end) as 百分数大于40总数,
count(a.C_true) as counts,
sum(a.C_true) as sums,
cast(round(cast(count(a.C_true) as numeric(5,1))/@a,3)*100 as numeric(4,1)) as 百分比1,
cast(round(cast(sum(a.C_true) as numeric(5,1))/@b,3)*100 as numeric(4,1)) as 百分比2
from
检查结果 a
inner join
检查类型 b
on
a.A_NO = b.A_NO and a.A_tag = b.A_tag
where
a.A_NO in(1,3) and a.A_tag = '1-04'
group by
b.A_title WITH ROLLUP
查询输出的结果:
/****************************************************/
A_title 数量大于3 百分数大于40 counts sums 百分比1 百分比2
-----------------------------------------------------------------------
其它1 2 2 2 25 50.0 71.4
胃 1 2 2 10 50.0 28.6
合计 3 4 4 35 100.0 100.0
不要老是在帖子中复制别人的回复
条件是这样选择的:
1。首先选择类型表中 A_tag 的一类 出现 类型A_NO[统计内容是可以自由选择]
2。首先选择类型表中 A_tag 的一类 出现 类型A_NO[统计内容是可以自由选择],总分>40 或者 类型A_NO出现的数量>3
除以上条件,增加两个统计内容条件 总分>40 或者 “胃”出现的数量>3 这两个条件是提供输入的,即动态的。
==============》》》改正如下
结果2
除以上条件,增加两个统计内容条件 其中统计的类型中分数>40的数量 , 统计的类型中其中类型数量>3的数量 这两个条件是提供输入的,即动态的,
d.modtitle,
d.counts,
d.sums,
cast(round(cast(d.counts as numeric(5,1))/c.count_all,3)*100 as numeric(4,1)) as 百分比1,
cast(round(cast(d.sums as numeric(5,1))/c.sum_all,3)*100 as numeric(4,1)) as 百分比2
from
(select
isnull(b.modtitle ,'合计') as modtitle,
count(CAST(a.resultinfo AS int) ) as counts,
sum(CAST(a.resultinfo AS int)) as sums --count(a.C_true) as counts,
--sum(a.C_true) as sums
from
S_RepairInforesult a
inner join
S_Repairprojectmod b
on
a.ZXFAModID = b.ZXFAModID and a.ZXFAID = b.ZXFAID
where
a.ZXFAModID in(1,2,3) and a.ZXFAID ='JDGL-20041229111209'
group by
b.modtitle WITH ROLLUP) d
inner join
(select
count(CAST(resultinfo AS int)) as count_all,
sum(CAST(resultinfo AS int)) as sum_all
from
S_RepairInforesult
where ----如果要统计在全部内容中的百分比
ZXFAModID in(1,2,3) ----则该条件语句可以不要
) c
on
1 = 1
order by d.modtitle desc----------------------------
那么 1 1 5.9 9.1
没有了 1 1 5.9 9.1
好的 1 1 5.9 9.1
合计 3 3 17.6 27.3
表 S_Repairprojectmod-----------------------------------------
ZXFAModID ZXFAID ModTitle
1 JDGL-20041229101256 3
2 JDGL-20041229101256 4
3 JDGL-20041229101256 5
1 JDGL-20041229111252 1
2 JDGL-20041229111252 2
3 JDGL-20041229111252 3
4 JDGL-20041229111252 4
5 JDGL-20041229111252 5
1 JDGL-20041229131216 e
1 JDGL-20041229111209 好的
2 JDGL-20041229111209 那么
3 JDGL-20041229111209 没有了
表 S_RepairInforesult
-----------------------------------
ZXcodeID ZXFAModID resultinfo ZXFAID
XZJC-QYDJ-200412291512i28 1 1 JDGL-20041229101256
XZJC-QYDJ-200412291512i28 2 1 JDGL-20041229101256
XZJC-QYDJ-200412291512i28 3 1 JDGL-20041229101256
XZJC-QYDJ-200412291512i18 1 1 JDGL-20041229111209
XZJC-QYDJ-200412291512i18 2 1 JDGL-20041229111209
XZJC-QYDJ-200412291512i18 3 1 JDGL-20041229111209
5.9%是当前一个A_NO与表中所有A_NO的数目的比例?select
d.modtitle,
d.counts,
d.sums,
cast(round(cast(d.counts as numeric(5,1))/c.count_all,3)*100 as numeric(4,1)) as 百分比1,
cast(round(cast(d.sums as numeric(5,1))/c.sum_all,3)*100 as numeric(4,1)) as 百分比2
from
(select
isnull(b.modtitle ,'合计') as modtitle,
count(CAST(a.resultinfo AS int) ) as counts,
sum(CAST(a.resultinfo AS int)) as sums --count(a.C_true) as counts,
--sum(a.C_true) as sums
from
S_RepairInforesult a
inner join
S_Repairprojectmod b
on
a.ZXFAModID = b.ZXFAModID and a.ZXFAID = b.ZXFAID
where
a.ZXFAModID in(1,2,3) and a.ZXFAID ='JDGL-20041229111209'
group by
b.modtitle WITH ROLLUP) d
inner join
(select
count(CAST(resultinfo AS int)) as count_all,
sum(CAST(resultinfo AS int)) as sum_all
from
S_RepairInforesult
--where ZXFAID ='JDGL-20041229111209' --如果需要,把这一列加上
) c
on
1 = 1
order by d.modtitle desc
查询期望的结果:
/****************************************************/
declare @a int
declare @b intselect
@a = count(C_true),
@b = sum(C_true)
from
检查结果
where
A_NO in(1,3)select
isnull(b.A_title ,'合计') as A_title,
sum(case when a.C_true >3 then 1 else 0 end) as 数量大于3总数,
sum(case when cast(a.C_true as numeric(5,1))/@a > 0.4 then 1 else 0 end) as 百分数大于40总数,
count(a.C_true) as counts,
sum(a.C_true) as sums,
cast(round(cast(count(a.C_true) as numeric(5,1))/@a,3)*100 as numeric(4,1)) as 百分比1,
cast(round(cast(sum(a.C_true) as numeric(5,1))/@b,3)*100 as numeric(4,1)) as 百分比2
from
检查结果 a
inner join
检查类型 b
on
a.A_NO = b.A_NO and a.A_tag = b.A_tag
where
a.A_tag = '1-04' -----酌情可以去掉或增加新的约束
group by
b.A_title WITH ROLLUP
查询期望的结果:
/****************************************************/
declare @a int
declare @b intselect
@a = count(C_true),
@b = sum(C_true)
from
检查结果
--where A_NO in(1,3) --酌情可以去掉或增加新的条件限制select
isnull(b.A_title ,'合计') as A_title,
sum(case when a.C_true >3 then 1 else 0 end) as 数量大于3总数,
sum(case when cast(a.C_true as numeric(5,1))/@a > 0.4 then 1 else 0 end) as 百分数大于40总数,
count(a.C_true) as counts,
sum(a.C_true) as sums,
cast(round(cast(count(a.C_true) as numeric(5,1))/@a,3)*100 as numeric(4,1)) as 百分比1,
cast(round(cast(sum(a.C_true) as numeric(5,1))/@b,3)*100 as numeric(4,1)) as 百分比2
from
检查结果 a
inner join
检查类型 b
on
a.A_NO = b.A_NO and a.A_tag = b.A_tag
where
a.A_NO in(1,3) and a.A_tag = '1-04'
group by
b.A_title WITH ROLLUP
比例方式是 随选择的类型某一类 / '1-04' 条件的所有被选择的类型数量
@a = count(C_true),
@b = sum(C_true)
from
检查结果
where
A_tag = '1-04'