如何用cte使php handel mander my qu as quot as' sql？

Question

我将SQL查询添加到PHP代码中，但它选择回声“ 0结果”。 我在Sublime文本编辑器中注意到所有SQL代码都以相同的颜色显示（不正确），但是当我删除第一个单词“ with cte as（”（'功能失败。 简而言之，如何使PHP处理我的SQL？谢谢。

$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}


$sql = " 
    WITH CTE as  (  SELECT *
       , LEAD(total_weight, 1) OVER(
           ORDER BY weight_date DESC
       ) AS prev_total_weight
       
       , MIN(total_weight) OVER() AS lowest_weight
       , MAX(total_weight) OVER() AS highest_weight
       , FROM_UNIXTIME(weight_date, '%u') AS weight_week
           
             
           , ROW_NUMBER() OVER(
              ORDER BY weight_date DESC
           ) AS RowNum
           

   FROM   (
              SELECT *, weight_start_week + weight_end_week AS total_weight
              FROM YourTable
           ) t
        
          
)
SELECT
    `_ID`, `weight_date`, `weight_start_week`, `weight_end_week`
     weight_end_week 
    ,total_weight
    ,prev_total_weight
    ,lowest_weight
    ,highest_weight
    ,weight_week
    
    
    ,CASE
             WHEN total_weight > prev_total_weight THEN 'greater'
             WHEN total_weight = prev_total_weight THEN 'equal'
             ELSE 'less'
             END AS comparison
   ,RowNum
FROM CTE

";
$result = $conn->query($sql);

if ($result->num_rows > 0) {


  while($row = $result->fetch_assoc()) {
    echo "<table><tbody><tr><td>Latest:</td><td>" . $row["total_weight"]. "</td><td>Percent</td><td>up/down</td></td></tr><tr><td>Lowest:</td><td>" . $row["lowest_weight"].  "</td><td>" . $row["weight_week"]. "</td><td>Date:</td></tr><tr><td>Most:</td><td>" . $row["highest_weight"]. "</td><td>"  . $row["weight_week"]. "</td><td>Date:</td></tr></tbody></table>";

  }
} else {
  echo "0 results";
}
$conn->close();
?>

Answer 1

您可以将CTE插入主要查询，并将其用作子查询：

SELECT `_ID`, `weight_date`, `weight_start_week`, `weight_end_week`
       total_weight, prev_total_weight, lowest_weight, highest_weight,
       weight_week,
       CASE ...
FROM
(
    -- place CTE definition here
    SELECT *, ...
           ROW_NUMBER() OVER( ORDER BY weight_date DESC) AS RowNum
    FROM ...
) t;