在 MYSQL 查询中使用变量进行计算 [英] Calculating using Variables in MYSQL query
本文介绍了在 MYSQL 查询中使用变量进行计算的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
希望你能帮上忙,这让我很烦
hope you can help, this is driving me up the wall
我需要计算一个问题失败的次数百分比,但这需要按地理区域缩小范围,以及这些问题所针对的产品.
I need to calculate the percentage of times a question has been failed, but this needs to be narrowed down by the geographical area, and product these questions are being asked against.
我有:
$CA002 = "( SELECT ROUND(100 * (SELECT count(CA002Result) from Data_Table where (CA002Result='Fail'))/count(CA002Result),2) from Data_Table) AS 'CA002 %'";
哪个有效"但只是将整个记录集作为整体"进行计算
Which 'works' but just calculates against the whole set of records as an 'overall'
我正在尝试:
$CA001 = "( SELECT ROUND(100 * (SELECT count(CA001Result) from Data_Table where (CA001Result='Fail' AND Area ='$Area'))/count(CA001Result) from Data_Table WHERE (Area='$Area'),2) AS 'CA001 %'";
还有:
$CA001 = "( SELECT ROUND(100 * (SELECT count(CA001Result ) from Data_Table where (CA001Result='Fail' AND Product='$product' AND Area='$Area'))
/ count(CA001Result WHERE Product = '$product' AND Area='$Area'),2) from Data_Table) AS 'CA001 %'";
无论我尝试什么,都只会出错,我似乎无法弄清楚我需要放在哪里.
and am just getting errors no matter what I try, I just can't seem to work out what I need to put where.
任何伟大的伟大应用,谢谢.
Any great GREATLY apprteciated, thankyou.
推荐答案
试试这个
//按区域过滤
create table t( id int, answer varchar(10),Area varchar(10));
insert into t select 1 , 'pass' , 'Area1';
insert into t select 2 , 'pass' , 'Area1';
insert into t select 3 , 'fail' , 'Area1';
insert into t select 4 , 'fail' , 'Area1';
insert into t select 5 , 'fail' , 'Area1';
insert into t select 6 , 'fail' , 'Area2';
SELECT
(x.TotalFailedAnswerRecord * 100) /y.TotalRecord AS Fail_percent
FROM
( SELECT Area,TotalFailedAnswerRecord = COUNT(answer)
FROM t
WHERE answer='fail' AND Area = 'Area1'
GROUP BY Area
)x
INNER JOIN
( SELECT Area,TotalRecord = COUNT(answer)
FROM t
WHERE Area = 'Area1'
GROUP BY Area
)y ON x.Area =y.Area
//Result
Fail_percent
-------------
60
//按区域、产品过滤
create table t( id int, answer varchar(10),Area varchar(10),Product varchar(10));
insert into t select 1 , 'pass' , 'Area1' ,'Product1';
insert into t select 2 , 'fail' , 'Area1' ,'Product1';
insert into t select 3 , 'fail' , 'Area1' ,'Product1';
insert into t select 4 , 'fail' , 'Area1' ,'Product1';
insert into t select 5 , 'fail' , 'Area1' ,'Product2';
insert into t select 6 , 'fail' , 'Area2' ,'Product2';
SELECT
(x.TotalFailedAnswerRecord * 100) /y.TotalRecord AS Fail_percent
FROM
( SELECT Area,Product,TotalFailedAnswerRecord = COUNT(answer)
FROM t
WHERE answer='fail' AND Area = 'Area1' AND Product = 'Product1'
GROUP BY Area,Product
)x
INNER JOIN
( SELECT Area,Product,TotalRecord = COUNT(answer)
FROM t
WHERE Area = 'Area1' AND Product = 'Product1'
GROUP BY Area,Product
)y ON x.Area =y.Area AND x.Product = y.Product
//Result
Fail_percent
-------------
75
希望能帮到你
这篇关于在 MYSQL 查询中使用变量进行计算的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文