我会非常感谢你们中任何一位出色的编码员可以帮助我.我在mysql / php中的编码专业知识有限,但我很固执.
至今:
下面这个成功的查询给出了名为’zmon’的业务只有一列“rsmed”中“严重”的员工数量,我现在需要从业务’zmon’的多个列中计算’严重’:
$host="localhost";$username="user"; $password="pass";$db_name="dbase";mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB");$query = "SELECT COUNT(*) FROM forearm WHERE business='zmon' AND rsmed = 'severe' "; $result = mysql_query($query) or die(mysql_error());while($row = mysql_fetch_array($result)){echo "There are ". $row['COUNT(*)'] ." employees severe in rsmed.";}
我被困在这里:
对于名为zmon的企业,我需要在名为“forearm”的表中计算多列(rslat,rsmed,rscentral,rselbow)中的“severes”数量.
因此,列业务包含业务名称.
同一个企业可以有多行,每行对应他们的不同员工.
其他列(rslat,rsmed,rscentral,rselbow)包含4个变量中的任何一个:不显着,低,中,高和严重.
我希望这对你来说足够了.
谢谢,保罗
解决方法:
您可以操纵查询以使用SUM(条件)或SUM(IF(条件,1,0))来单独计算每列.
SELECT SUM(rslat = 'severe') as rslat_count, SUM(rselbow = 'severe') as rselbow_count, SUM(rsmed = 'severe') as rsmed_count, SUM(rscentral = 'severe') as rscentral_countFROM forearmWHERE business='zmon'
数据:
| id | business | rslat | rselbow | rsmed | rscentral ||----|----------|--------|---------|--------|-----------|| 1 | zmon | severe | severe | severe | good || 2 | zmon | severe | severe | good | good || 3 | zmon | good | severe | good | good || 4 | zmon | severe | severe | good | good |
结果:http://sqlfiddle.com/#!9/093bd/2
| rslat_count | rselbow_count | rsmed_count | rscentral_count ||-------------|---------------|-------------|-----------------|| 3 | 4 | 1 | 0 |
然后你可以使用php显示结果
$sentence = 'There are %d employees severe in %s';while ($row = mysql_fetch_assoc($result)) { printf($sentence, $row['rslat_count'], 'rslat'); printf($sentence, $row['rselbow_count'], 'rselbow'); printf($sentence, $row['rsmed_count'], 'rsmed'); printf($sentence, $row['rscentral_count'], 'rscentral');}
更新
要获得各列的派生总计,您只需将它们相加即可.
SELECT SUM(counts.rslat_count counts.rselbow_count counts.rsmed_count counts.rscentral_count) as severe_total, counts.rslat_count, counts.rselbow_count, counts.rsmed_count, counts.rscentral_countFROM ( SELECT SUM(rslat = 'severe') as rslat_count, SUM(rselbow = 'severe') as rselbow_count, SUM(rsmed = 'severe') as rsmed_count, SUM(rscentral = 'severe') as rscentral_count FROM forearm WHERE business='zmon') AS counts
结果http://sqlfiddle.com/#!9/093bd/10
| severe_total | rslat_count | rselbow_count | rsmed_count | rscentral_count ||--------------|-------------|---------------|-------------|-----------------|| 8 | 3 | 4 | 1 | 0 |
然后显示严重的总数
$sentence = 'There are %d employees severe in %s';while ($row = mysql_fetch_assoc($result)) { printf($sentence, $row['rslat_count'], 'rslat'); printf($sentence, $row['rselbow_count'], 'rselbow'); printf($sentence, $row['rsmed_count'], 'rsmed'); printf($sentence, $row['rscentral_count'], 'rscentral'); echo 'business in ' . $row['severe_total'] . ' severe conditions';}
来源:https://www.icode9.com/content-1-419151.html
联系客服