ds3422222222
2018-04-26 01:44
浏览 65

如何将此SQL转换为Laravel雄辩?

I have the following SQL

SELECT C.CUOCODE, C.NAME, COUNT(*) TOTAL_PAYMENT, SUM(P.AMOUNT) TOTAL_AMOUNT
FROM TAX_PAYMENT P
INNER JOIN TAX_CHECKPOINT C ON C.CUOCODE = REGEXP_SUBSTR(P.INVOICEID, 'R....')
WHERE DELETED = 0 AND TO_CHAR(TXTIME,'YYYY-MM-DD') = '2018-04-24'
GROUP BY  C.CUOCODE, C.NAME
ORDER BY TOTAL_AMOUNT DESC;

How can i convert to laravel eloquent, i have "Payment" model (table TAX_PAYMENT) with "paymentid" as primary key.

图片转代码服务由CSDN问答提供 功能建议

我有以下SQL

  SELECT C.CUOCODE,  C.NAME,COUNT(*)TOTAL_PAYMENT,SUM(P.AMOUNT)TOTAL_AMOUNT 
FROM TAX_PAYMENT P 
INNER JOIN TAX_CHECKPOINT C ON C.CUOCODE = REGEXP_SUBSTR(P.INVOICEID,'R ....')
WHERE DELETED = 0  AND TO_CHAR(TXTIME,'YYYY-MM-DD')='2018-04-24'
GROUP由C.CUOCODE,C.NAME 
ORDER BY TOTAL_AMOUNT DESC; 
   
 \  n 

如何转换为laravel eloquent,我将“付款”模式(表TAX_PAYMENT)与“paymentid”作为主键。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dsxsou8465 2018-04-26 01:58
    已采纳
    DB::table('TAX_PAYMENT as P')
        ->select([
            'C.CUOCODE',
            'C.NAME',
            DB::raw('COUNT(*) AS TOTAL_PAYMENT'),
            DB::raw('SUM(P.AMOUNT) AS TOTAL_AMOUNT'),
        ])->Join('TAX_CHECKPOINT C', 'C.CUOCODE', '=', DB::raw('REGEXP_SUBSTR(P.INVOICEID,'R....')'))
        ->where('DELETED', 0)
        ->where(DB::raw("TO_CHAR(TXTIME,'YYYY-MM-DD')"), '2018-04-24')
        ->groupBy('C.CUOCODE')
        ->groupBy('C.NAME')
        ->orderBy('TOTAL_AMOUNT', 'desc')
        ->toSql();
    

    output

    SELECT
        `C`.`CUOCODE`,
        `C`.`NAME`,
        COUNT(*) AS TOTAL_PAYMENT,
        SUM(P.AMOUNT) AS TOTAL_AMOUNT
    FROM
        `TAX_PAYMENT` AS `P`
    INNER JOIN `TAX_CHECKPOINT C` ON `C`.`CUOCODE` = REGEXP_SUBSTR (P.INVOICEID,"R....")
    WHERE
        `DELETED` = ?
    AND TO_CHAR (TXTIME, 'YYYY-MM-DD') = ?
    GROUP BY
        `C`.`CUOCODE`,
        `C`.`NAME`
    ORDER BY
        `TOTAL_AMOUNT` DESC
    
    点赞 打赏 评论
  • doqw89029 2018-04-26 01:48

    A quick and dirty way would be to use Eloquents select raw like:

     $result = DB::select( DB::raw("SELECT C.CUOCODE, C.NAME, COUNT(*) 
    TOTAL_PAYMENT, SUM(P.AMOUNT) TOTAL_AMOUNT FROM TAX_PAYMENT INNER JOIN 
    TAX_CHECKPOINT C ON C.CUOCODE
    = REGEXP_SUBSTR(P.INVOICEID, 'R....') WHERE DELETED = 0 AND 
    TO_CHAR(TXTIME,'YYYY-MM-DD') = '2018-04-24' GROUP BY  C.CUOCODE,
    C.NAME ORDER BY TOTAL_AMOUNT DESC"));
    
    点赞 打赏 评论

相关推荐 更多相似问题