sql计算满意度,怎么整啊,求帮助啊,高分!
发布网友
发布时间：2024-09-27 07:35
共4个回答
热心网友
时间：2024-11-21 23:29
录入数据及建表
create table test(id int,
name varchar(10),
manyi varchar(10))
insert into test values (1,'张三','不满意')
insert into test values (2,'李四','满意')
insert into test values (3,'张三','满意')
insert into test values (4,'李四','不满意')
insert into test values (5,'张三','满意')
执行
select a.name,cast((b.count1+0.0)/a.count1 as numeric(19,2)) bumanyi,cast((c.count1+0.0)/a.count1 as numeric(19,2)) manyifrom
(select name,COUNT(*) count1 from test group by name) a,
(select name,COUNT(*) count1 from test where manyi='不满意' group by name) b,
(select name,COUNT(*) count1 from test where manyi='满意' group by name) c
where a.name=b.name
and a.name=c.name
结果
满意结果我显示的两位小数，你要是想显示一位，把numeric(19,2) 改成numeric(19,1)
说明一下，里边之所以加了+0.0，是因为sqlserver如果不加的话会显示成整数，所以+0.0是为了让他们显示成整数
追问你这个有个小问题！这个更简单select Name,convert(decimal(18,2),AVG(case Status when '满意'then 1.0 else 0 end)),CONVERT(decimal(18,2),avg(case status when '不满意' then 1.0 else 0 end)) from Article
热心网友
时间：2024-11-21 23:29
dbo.tonumber((select count(*)
from testTable
where name = t.name
and manyi = '满意')) /
dbo.tonumber((select count(*) from testTable where name = t.name))
from testTable t
group by name
我只写了个满意的，不满意的了是一样的。就不写了
热心网友
时间：2024-11-21 23:30
(select name,count(manyi) tt from 表 group by name) as tt,
(select name,count(manyi) tt from 表 where manyi='不满意' group by name) as t1,
(select name,count(manyi) tt from 表 where manyi='满意' group by name) as t2
where tt.name=t1.name and tt.name=t2.name
热心网友
时间：2024-11-21 23:30
='满意')/(select count (t3.id) from table t3 where t3.name=t1.name) manyi,(select count(t4.id) from table t4 where t4.name=t1.name and t4.manyi
='不满意')/(select count (t5.id) from table t5 where t5.name=t1.name) bumanyi from table t1;
