如何将CTE转换为普通查询？

Question

如何将其转换为普通查询？

WITH cte AS (
    SELECT agentID, 
           SUM(bonus > 0) OVER (PARTITION BY agentID 
                                ORDER BY `year` * 12 + `month`
                                RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) flag
    FROM test
)
SELECT agentID
FROM cte
WHERE flag = 3;

我需要转换它，因为我认为Mariadb与CTE不兼容。我也不真的熟悉CTE，我不知道如何将其分解为PHP中的正常SQL查询。

更新：

我尝试这样做以运行CTE

<?php
$servername = "localhost";
$username = "root";
$password = "";
$db = "sample_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $db);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}


$stmt = $conn->query("SELECT agentID, bonus FROM (WITH cte AS (
    SELECT DISTINCT agentID, 
           SUM(bonus > 0) OVER (PARTITION BY agentID 
                                ORDER BY `year` * 12 + `month`
                                RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) flag
    FROM sample_tbl
)) where agentID = '10710' && flag = 3");

    if($stmt->num_rows > 0){
        echo "You are elligible to take a course!";
    } else{
           echo "You are not elligible to take a course!";
        }




?>

，但它不起作用，结果显示

“致命错误：未接收的mysqli_sql_exception：您有错误 SQL语法;检查与您的MariadB服务器相对应的手册 适用于近距离使用的正确语法'）ventID ='10710'＆amp;＆amp; flag = 3'在C：\ xampp \ htdocs \ try \ index.php中的第7行中的第7行：16堆栈跟踪： ＃0 C：\ Xampp \ htdocs \ try \ index.php（16）：mysqli-＆gt; query（'select agentId，...'）＃1 {main}在c：\ xampp \ xampp \ htdocs \ htdocs \ try try try try \ index中。 .php打开 第16行

Answer 1

的确， mariadb与CTES兼容，但是，如果您不想不想出于某种原因处理CTE，您总是可以将其转换为一个子查询：

SELECT agentID
FROM (
    SELECT agentID, 
           SUM(bonus > 0) OVER (PARTITION BY agentID 
                                ORDER BY `year` * 12 + `month`
                                RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) flag
    FROM test ) agents_with_summed_bonus
WHERE flag = 3;

如果此查询代替了使用CTE构建的查询，则对您不起作用，则意味着您的初始查询在关系中存在一些错误你的桌子。

Answer 2

再次更新：

现在对我有用，这是我的最终代码：

<?php
$servername = "localhost";
$username = "root";
$password = "";
$db = "sample_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $db);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}


$stmt = $conn->query("SELECT DISTINCT agentID FROM (SELECT DISTINCT agentID, 
SUM(bonus > 0) OVER (PARTITION BY agentID 
                     ORDER BY `year` * 12 + `month`
                     RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) flag from sample_tbl) as cte where agentID = '61599' && flag = 3");

    if($stmt->num_rows > 0){
        echo "You are elligible to take a course!";
    } else{
           echo "You are not elligible to take a course!";
        }




?>