计算出数据记录的参数的最大值与最小值，比如最小值1100 最大值1300 ，将1100到1300分为

计算出数据记录的参数的最大值与最小值，比如最小值1100 最大值1300 ，将1100到1300分为10段 1100-1120,1120-1140等等然后统计出落在各个区间的记录的个数。

select    cast(MIN(a.fParaValue)as varchar(20))+'-'+cast((MIN(a.fParaValue)+(MAX(a.fParaValue)-MIN(a.fParaValue))/10 )as varchar(20)) as 分段 ,COUNT(b.sParaName) as number
from dbo.Para_erecord a inner join dbo.Para_type  b ON a.fkiParaID = b.ID
INNER JOIN
                dbo.workstation_type AS c ON b.fkiWorkstationID = c.ID INNER JOIN
                dbo.zone_type AS d ON c.sZoneName = d.sZoneName
where sParaName = '电泳膜耐盐雾性'
group by cast(MIN(a.fParaValue)as varchar(20))+'-'+cast((MIN(a.fParaValue)+(MAX(a.fParaValue)-MIN(a.fParaValue))/10 )as varchar(20))
having a.fParaValue between MIN(a.fParaValue) and (MIN(a.fParaValue)+(MAX(a.fParaValue)-MIN(a.fParaValue))/10)

sql语句这样写的，但是 group by 后面不能跟我写的那些含有聚合函数的语句，应该怎么修改呢

FineReport zhaozhaode 发布于 2018-12-24 14:13

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：4 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共3回答

最佳回答

axingLv6专家互助
发布于2018-12-24 14:48（编辑于 2018-12-24 15:37）

参考这个来写

with t as (
select 'A' a,1100 as b 
union all 
select 'A' a,1150 as b
union all 
select 'A' a,1200 as b 
union all 
select 'A' a,1290 as b
union all 
select 'A' a,1300 as b)

select cast(t3.x1 as varchar(20))+'~'+cast(t3.x2 as varchar(20)) 分段,count(a) 数量 from t,
(select minb+(maxb-minb)/10*number x1,maxb-(maxb-minb)/10*(9-number) x2 from 
(select min(b) minb,max(b) maxb from t) t1,
(select number from master..spt_values WHERE type = 'p' AND number <10) t2)t3
where t.b between t3.x1 and t3.x2
group by cast(t3.x1 as varchar(20))+'~'+cast(t3.x2 as varchar(20))

No1-王子阳Lv5中级互助
发布于2018-12-24 14:30

兄弟你先把指标计算完，然后group by 维度就好了。

KerydiaLv5中级互助
发布于2018-12-24 14:31（编辑于 2018-12-24 14:32）

select 分段,sum(number) from (

select    a.fParaValue,cast(MIN(a.fParaValue)as varchar(20))+'-'+cast((MIN(a.fParaValue)+(MAX(a.fParaValue)-MIN(a.fParaValue))/10 )as varchar(20)) as 分段 ,COUNT(b.sParaName) as number,
from dbo.Para_erecord a inner join dbo.Para_type  b ON a.fkiParaID = b.ID
INNER JOIN
                dbo.workstation_type AS c ON b.fkiWorkstationID = c.ID INNER JOIN
                dbo.zone_type AS d ON c.sZoneName = d.sZoneName
where sParaName = '电泳膜耐盐雾性'

group by 分段

having a.fParaValue between MIN(a.fParaValue) and (MIN(a.fParaValue)+(MAX(a.fParaValue)-MIN(a.fParaValue))/10)

举报收起评论(2)

zhaozhaode(提问者) 不行吧这样，having那提示选择列表中的列 \'dbo.Para_erecord.fParaValue\' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

2018-12-24 14:39

Kerydia 回复 zhaozhaode(提问者) select 分段,COUNT(number) number from ( select cast(MIN(a.fParaValue)as varchar(20))+\'-\'+cast((MIN(a.fParaValue)+(MAX(a.fParaValue)-MIN(a.fParaValue))/10 )as varchar(20)) as 分段 ,COUNT(b.sParaName) as number from dbo.Para_erecord a inner join dbo.Para_type b ON a.fkiParaID = b.ID INNER JOIN dbo.workstation_type AS c ON b.fkiWorkstationID = c.ID INNER JOIN dbo.zone_type AS d ON c.sZoneName = d.sZoneName where sParaName = \'电泳膜耐盐雾性\' and a.fParaValue between (select MIN(a.fParaValue) minValue from dbo.Para_erecord a inner join dbo.Para_type b ON a.fkiParaID = b.ID INNER JOIN dbo.workstation_type AS c ON b.fkiWorkstationID = c.ID INNER JOIN dbo.zone_type AS d ON c.sZoneName = d.sZoneName where sParaName = \'电泳膜耐盐雾性\') and (select (MIN(a.fParaValue)+(MAX(a.fParaValue)-MIN(a.fParaValue))/10) maxValue from dbo.Para_erecord a inner join dbo.Para_type b ON a.fkiParaID = b.ID INNER JOIN dbo.workstation_type AS c ON b.fkiWorkstationID = c.ID INNER JOIN dbo.zone_type AS d ON c.sZoneName = d.sZoneName where sParaName = \'电泳膜耐盐雾性\') ) group by 分段

2018-12-24 14:55