mariadb子查询

bqucvtff 于 2021-06-20 发布在 Mysql

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

很好的一天！
我有这个sql，但不是在托管工作。在局域网上一切正常。我找不到问题所在。laravel项目sql：

$data = DB::select('
            SELECT 
                DATE(o.created_at) as day,
                count(id) as count_all,
                count((SELECT id WHERE status=3)) as success,
                count((SELECT id WHERE status=5)) as return_order,
                count((SELECT id WHERE status=0 or status=4 or status=8)) as call_order,

                count((SELECT id WHERE status=7 or status=6 or status=1)) as otkaz,
                count((SELECT id WHERE status=2)) as nado_dostavit,

                SUM((SELECT offer_price)) as all_price,
                SUM((SELECT offer_price WHERE status=3)) as success_price,
                SUM((SELECT offer_price WHERE status=5)) as return_order_price,
                SUM((SELECT offer_price WHERE status=0 or status=4 or status=8)) as call_order_price,
                SUM((SELECT offer_price WHERE status=7 or status=6 or status=1)) as otkaz_price,
                SUM((SELECT offer_price WHERE status=2 )) as nado_dostavit_price,
                (SELECT sum(s.visitors) FROM statistics as s WHERE s.offer_id='.$user_id.') as visitors
            FROM 
                orders as o
            WHERE 
                  offer_id='.$user_id.'
                  AND  created_at between "'.$mother_later.'" AND "'.$today.'"
            GROUP BY day
            ORDER BY day desc
            limit 30
    ');

错误：
（2/2）查询异常
sqlstate[42000]：语法错误或访问冲突：1064您的sql语法有错误；检查与您的mariadb服务器版本相对应的手册，以获得正确的语法，以便在“where status=3”）附近使用success，

mysql mariadb laravel

来源：https://stackoverflow.com/questions/52015130/mariadb-subquery

1条答案

按热度按时间

chhkpiq41#

您的子查询都没有 FROM 子句，这就是为什么会出现语法错误。但是，您实际上不希望在这些位置使用子查询。对于 COUNT ，你应该用例如。

SUM(CASE WHEN status=3 THEN 1 ELSE 0 END)

而对于 SUM s、你应该使用例如。

SUM(CASE WHEN status=3 THEN offer_price ELSE 0 END)

总之，您的查询应该是：

$data = DB::select('
        SELECT 
            DATE(o.created_at) as day,
            count(id) as count_all,
            SUM(CASE WHEN status=3 THEN 1 ELSE 0 END) as success,
            SUM(CASE WHEN status=5 THEN 1 ELSE 0 END) as return_order,
            SUM(CASE WHEN status=0 OR status=4 OR status=8 THEN 1 ELSE 0 END) as call_order,
            SUM(CASE WHEN status=7 OR status=6 OR status=1 THEN 1 ELSE 0 END) as otkaz,
            SUM(CASE WHEN status=2 THEN 1 ELSE 0 END) as nado_dostavit,

            SUM(offer_price) as all_price,
            SUM(CASE WHEN status=3 THEN offer_price ELSE 0 END) as success_price,
            SUM(CASE WHEN status=5 THEN offer_price ELSE 0 END) as return_order_price,
            SUM(CASE WHEN status=0 OR status=4 OR status=8 THEN offer_price ELSE 0 END)  as call_order_price,
            SUM(CASE WHEN status=7 OR status=6 OR status=1 THEN offer_price ELSE 0 END) as otkaz_price,
            SUM(CASE WHEN status=2 THEN offer_price ELSE 0 END) as nado_dostavit_price,
            (SELECT sum(s.visitors) FROM statistics as s WHERE s.offer_id='.$user_id.') as visitors
        FROM 
            orders as o
        WHERE 
              offer_id='.$user_id.'
              AND  created_at between "'.$mother_later.'" AND "'.$today.'"
        GROUP BY day
        ORDER BY day desc
        limit 30
');

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

我来回答

mariadb子查询

1条答案

相关问题

热门标签

最新问答