sql查询中的条件

yyhrrdl8 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(329)

我想创建一个查询，如果datediff（so\u date，actual\u delivery）=3，则显示单词“passed”，否则显示“failed”。有人能帮我吗？

<?php
$hostname="localhost"; 
$username="root";
$password="";
$db = "csl_otd"; 
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT so_date,
       actual_delivery,
       case when DATEDIFF(so_date, actual_delivery) = 3 
            then "Passed"
            else "Failed"
       end as status
FROM sales_order
LEFT JOIN dar ON sales_order.dar_numberr = dar.dar_number
WHERE DATEDIFF(so_date, actual_delivery) < 10') as $row) {
echo "<tr>";
echo "<td>" . $row['so_date'] . "</td>";
echo "<td>" . $row['actual_delivery'] . "</td>"; 
echo "<td>" . $row['DATEDIFF(so_date,actual_delivery)'] . "</td>";
echo "<td>" . $row['status'] . "</td>";
echo "</tr>";  
}
?>
</tbody></table>

输出

Date Difference  | Status
2                | Passed
4                | Failed

sql mysql php

来源：https://stackoverflow.com/questions/53236715/condition-inside-a-sql-query

1条答案

按热度按时间

sczxawaw1#

使用案例

SELECT so_date,actual_delivery,
     DATEDIFF(so_date,actual_delivery) Date_Difference,
    case when  DATEDIFF(so_date,actual_delivery) = 3 
    then "Passed"
    else "Failed" end Status
    FROM sales_order
    LEFT JOIN dar
    ON sales_order.dar_numberr=dar.dar_number
    WHERE DATEDIFF(so_date,actual_delivery)<10

或者你也可以用，如果你用过的话（但是很少有修正）

SELECT so_date,actual_delivery,DATEDIFF(so_date,actual_delivery),
if ( DATEDIFF(so_date,actual_delivery) = 3 
,'Passed','Failed') status
FROM sales_order
LEFT JOIN dar
ON sales_order.dar_numberr=dar.dar_number
WHERE DATEDIFF(so_date,actual_delivery)<10

赞(0）回复(0）举报 2021-06-18

我来回答

sql查询中的条件

1条答案

相关问题

热门标签

最新问答