<p>请参见SQLFIDLE:<a href="http://sqlfiddle.com/#!9/accca6/2/6" rel="noreferrer">http://sqlfiddle.com/#!9/accca6/2/6</a>
注意:对于SQLFIDLE,我已经生成了100行,1到100之间的每个整数都有一行,但它是一个随机顺序(在excel中完成)</p>
<p>代码如下:</p>
<pre><code>SET @number_of_rows := (SELECT COUNT(*) FROM LuxLog);
SET @quartile := (ROUND(@number_of_rows*0.25));
SET @sql_q1 := (CONCAT('(SELECT "Q1" AS quartile_name , Lux, Sensor FROM LuxLog ORDER BY Lux DESC LIMIT 1 OFFSET ', @quartile,')'));
SET @sql_q3 := (CONCAT('( SELECT "Q3" AS quartile_name , Lux, Sensor FROM LuxLog ORDER BY Lux ASC LIMIT 1 OFFSET ', @quartile,');'));
SET @sql := (CONCAT(@sql_q1,' UNION ',@sql_q3));
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
</code></pre>
<p><strong>编辑:</strong></p>
<pre><code>SET @current_sensor := 101;
SET @quartile := (ROUND((SELECT COUNT(*) FROM LuxLog WHERE Sensor = @current_sensor)*0.25));
SET @sql_q1 := (CONCAT('(SELECT "Q1" AS quartile_name , Lux, Sensor FROM LuxLog WHERE Sensor=', @current_sensor,' ORDER BY Lux DESC LIMIT 1 OFFSET ', @quartile,')'));
SET @sql_q3 := (CONCAT('( SELECT "Q3" AS quartile_name , Lux, Sensor FROM LuxLog WHERE Sensor=', @current_sensor,' ORDER BY Lux ASC LIMIT 1 OFFSET ', @quartile,');'));
SET @sql := (CONCAT(@sql_q1,' UNION ',@sql_q3));
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
</code></pre>
<p>基本推理如下:
对于四分位1,我们希望从顶部得到25%,因此我们希望知道有多少行,即:</p>
<pre><code>SET @number_of_rows := (SELECT COUNT(*) FROM LuxLog);
</code></pre>
<p>现在我们知道了行数,我们想知道其中25%是多少,这是这一行:</p>
<pre><code>SET @quartile := (ROUND(@number_of_rows*0.25));
</code></pre>
<p>然后,为了找到一个四分位数,我们要按Lux对LuxLog表进行排序,然后获得行号“@quartile”,为了做到这一点,我们将偏移量设置为@quartile,表示我们要从行号@quartile开始选择,我们说limit 1表示我们只想检索一行。那就是:</p>
<pre><code>SET @sql_q1 := (CONCAT('(SELECT "Q1" AS quartile_name , Lux, Sensor FROM LuxLog ORDER BY Lux DESC LIMIT 1 OFFSET ', @quartile,')'));
</code></pre>
<p>对于另一个四分位数,我们的做法(几乎)相同,但我们不是从顶部开始(从较高的值到较低的值),而是从底部开始(这解释了ASC)</p>
<p>但是现在我们只在变量@sql_q1和@sql_q3中存储了字符串,所以将它们连接起来,我们合并查询结果,准备查询并执行它</p>