这是我的MySQL表结构的一部分:
questionRecID value periodMonth periodYear practiceID
16 70 11 2010 475
28 33 11 2010 475
14 226 11 2010 475
问题编号14是用于计算百分比的分母。所以，像这样:
percent = (70 / 226) * 100。这给了我2010年11月rec ID 16问题的百分比。
我需要查询整个表以获得所有年份和所有月份存储的前10个执行实践。php或SQL对我来说都很好。
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table (questionRecID INT NOT NULL PRIMARY KEY,value INT NOT NULL,periodMonth INT NOT NULL, periodYear INT NOT NULL, practiceID INT NOT NULL);
INSERT INTO my_table VALUES
(16 ,70 ,11 ,2010 ,475 ),
(28 ,33 ,11 ,2010 ,475),
(14 ,226 ,11 ,2010 ,475);
SELECT * FROM my_table;
+---------------+-------+-------------+------------+------------+
| questionRecID | value | periodMonth | periodYear | practiceID |
+---------------+-------+-------------+------------+------------+
| 14 | 226 | 11 | 2010 | 475 |
| 16 | 70 | 11 | 2010 | 475 |
| 28 | 33 | 11 | 2010 | 475 |
+---------------+-------+-------------+------------+------------+
SELECT x.*
, x.value/y.value pct
FROM my_table x
JOIN my_table y
ON y.practiceid = x.practiceid
AND y.questionrecid = 14;
+---------------+-------+-------------+------------+------------+--------+
| questionRecID | value | periodMonth | periodYear | practiceID | pct |
+---------------+-------+-------------+------------+------------+--------+
| 14 | 226 | 11 | 2010 | 475 | 1.0000 |
| 16 | 70 | 11 | 2010 | 475 | 0.3097 |
| 28 | 33 | 11 | 2010 | 475 | 0.1460 |
+---------------+-------+-------------+------------+------------+--------+