|
最佳回答 |
|
0
|
zsh331Lv8专家互助发布于2019-6-3 16:59
|
SELECT
*, count(1) AS allcount,
sum(CASE WHEN y_n = 'y' THEN 1 ELSE 0 END) AS ycount,
sum(CASE WHEN y_n = 'n' THEN 1 ELSE 0 END) AS ncount,
CONCAT(
ROUND(
sum(CASE WHEN y_n = 'y' THEN 1 ELSE 0 END) / count(1) * 100,
2
),
'%'
)
FROM
yh_type;
|
|
|
|
|
最佳回答 |
|
0
|
虾米、木木Lv2初级互助发布于2019-6-3 16:59
|
select *, count(1) as allcount,sum(case when y_n='y' then 1 else 0 end) as ycount,sum(case when y_n='n' then 1 else 0 end) as ncount,CONCAT(ROUND(sum(case when y_n='y' then 1 else 0 end)/sum(case when y_n='n' then 1 else 0 end)*100,2),'%') from yh_type; |
ycount 和ncount并不存在表中 一种方法是如上所示，一种方法是 select *,,CONCAT(ROUND(yconut/allcount*100,2),'%') from(select *, count(1) as allcount,sum(case when y_n='y' then 1 else 0 end) as ycount,sum(case when y_n='n' then 1 else 0 end) as ncount from yh_type)
|
|
|
|
|
最佳回答 |
|
0
|
风云无忌Lv0见习互助发布于2019-6-3 17:00
|
with t1 as ( select *, count(1) as allcount,sum(case when y_n='y' then 1 else 0 end) as ycount,sum(case when y_n='n' then 1 else 0 end) as ncount from yh_type) select ycount,ncount, CONCAT(ROUND(yconut/allcount*100,2),'%') from t1
|
|
|
|
|
最佳回答 |
|
0
|
firegunzxLv6高级互助发布于2019-6-3 17:01
|
在同一级查询里不能直接用as 后面的重命名作为字段名，要么把yconut替换成sum(case when y_n='y' then 1 else 0 end) ，要么在外面加一层查询。- |
|
|
|
|
最佳回答 |
|
0
|
张洪威Lv6高级互助发布于2019-6-3 17:01
|
你别用别名，直接把前面计算的式子放到里面。
CONCAT(ROUND(sum(case when y_n='y' then 1 else 0 end)/count(1)*100,2),'%')
或者在上面嵌套一个。 select aa.* , CONCAT(ROUND(aa.yconut/aa.allcount*100,2),'%') from
(select *, count(1) as allcount,sum(case when y_n='y' then 1 else 0 end) as ycount,sum(case when y_n='n' then 1 else 0 end) as ncount from yh_type) aa
|
|
|
|
1min目标场景问卷
立即参与
