我有 20 列命名为 qa1, qa2, qa3 up to qa20
,它们将保存 YES 或 NO的值。我将如何获得保存 YES 值的列数和保存 NO 值的列数。
加法:表说明如下:
examid int,
firstname varchar(20),
middlename varchar(20),
lastname varchar(20),
q1 varchar(1),
q2 varchar(1), .... up to q20(1)
例如。测试数据将是
q1 = y
q2 = n
q3 = y
q4 = n
q5 = y
q6 = n
q7 = y
q8 = n
q9 = y
q10 = y
我想得到:
y = 6
的答案回答n = 4
我想通了,使用 php 和 mysql 我得出了:
$yescounter = 0;
$nocounter = 0;
for($x1 = 1; $x1<= 20; $x1++){
$trial1 = mysql_query("SELECT qanswer$x1 FROM tb_erm_exam_results WHERE qanswer$x1='y'");
$t1 = mysql_num_rows($trial1);
if($t1 == 1) { $yescounter++; } else { $nocounter++; }
}
echo "YES is: ".$yescounter." while NO is:".$nocounter;
它只是给出相同的结果,但没有复杂的 MySQL 查询。
SELECT id, (qa1 = 'Y' + qa2 = 'Y' + ... + qa20 = 'Y') AS yes_count,
(qa1 = 'N' + qa2 = 'N' + ... + qa20 = 'N') AS no_count
FROM yourtable
如您所见,以这种方式构建表会使此类查询变得复杂。如果您使用每个答案位于不同行中的表格会更好。然后你可以做
SELCT id, SUM(qa = 'Y') AS yes_count, SUM(qa = 'N') AS no_count
FROM yourTable
GROUP BY id
此表的结构如下:
CREATE TABLE yourTable (
id INTEGER NOT NULL, -- Survey ID
question_num INTEGER NOT NULL, -- 1 to 20
qa CHAR(1), -- Y or N
UNIQUE KEY (id, question_num)
)
试试这个它给了我结果。
select count(*) from sample where 'yes' IN (q1,q2,q3,q4,q5,q6,q7,q8,q9......)
经过一个小时的试用,我得到了这个解决方案,它无需使用太多查询即可产生结果。
$yescounter = 0;
$nocounter = 0;
for($x1 = 1; $x1<= 50; $x1++){
$answercounter1 = mysql_query("SELECT qanswer$x1 FROM tb_erm_exam_results WHERE qanswer$x1='y'");
$ac1 = mysql_num_rows($answercounter1);
if($ac1 == 1) { $yescounter++; } else { $nocounter++; }
}