来自同一MySQL表上两个字段的前10条记录

Here's a part of my MySQL table structure:

这是我的MySQL表结构的一部分:

questionRecID       value   periodMonth     periodYear      practiceID
           16          70            11           2010             475  
           28          33            11           2010             475
           14         226            11           2010             475

Question Rec ID 14 is the denominator used to calculate percentages. So, goes like this:

问题ID 14是用于计算百分比的分母。所以,像这样:

percent = (70 / 226) * 100. This gives me the percentage for question of rec ID 16 for Nov 2010.

百分比=(70/226)* 100.这给出了2010年11月的rec ID 16问题的百分比。

I need to query the entire table to get the top 10 performing practices across all years and all months stored. Either php or SQL is fine with me. I'm really stuck on how to get the data out of this table.

我需要查询整个表格,以获得所有年份和所有月份存储的前10个表演实践。 php或SQL都可以。我真的很想知道如何从这个表中获取数据。

1 个解决方案

#1

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 |
+---------------+-------+-------------+------------+------------+--------+