laravel查询生成器原始查询,带有两个from语句

6mw9ycah  于 2021-07-26  发布在  Java
关注(0)|答案(0)|浏览(221)

很抱歉,如果这个问题听起来有点无聊,我真的转向这里,因为我已经试图解决我的问题,在不同的方式,我没有能够获得良好的结果。
我想把一个 MySQL 查询到 Laravel Query Builder ,我向您显示查询:

SELECT DAY(AAA.fecha_hora_entrada) as DAY, IFNULL(BBB.SALES, 0) SALES, IFNULL(BBB.NET, 0) NET
FROM (
         SELECT fecha_hora_entrada
         FROM (
                  SELECT MAKEDATE(YEAR(NOW()), 1) +
                         INTERVAL (MONTH(NOW()) - 1) MONTH +
                         INTERVAL daynum DAY fecha_hora_entrada
                  FROM (
                           SELECT t * 10 + u daynum
                           FROM (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
                                (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) B
                           ORDER BY daynum
                       ) AS AA
              ) AS AA
         WHERE MONTH(fecha_hora_entrada) = MONTH(NOW())
     ) AS AAA
         LEFT JOIN (SELECT (DATE(fecha_hora_entrada)) AS fecha_hora_entrada, (SUM(neto)) AS NET, (COUNT(neto)) AS SALES
                    FROM t_derivados
                    GROUP BY DATE(fecha_hora_entrada)) BBB
                   ON AAA.fecha_hora_entrada = BBB.fecha_hora_entrada;

到目前为止,我尝试了以下几点。到目前为止,我已经通过了部分考试 MySQL 查询到 Query Builder ```
public function salesYear()
{
return DB::table(DB::table(DB::table(DB::table(DB::table(null, null)
->selectRaw('0 t UNION SELECT 2 UNION SELECT 3'),'A')
->orderBy('daynum')
->selectRaw('t * 10 + u daynum'), 'AA')
->selectRaw('MAKEDATE(YEAR(NOW()), 1) + INTERVAL (MONTH(NOW()) - 1) MONTH + INTERVAL daynum DAY fecha_hora_entrada'), 'AA')
->addSelect('fecha_hora_entrada')
->whereRaw('MONTH(fecha_hora_entrada) = MONTH(NOW())'), 'AAA')
->selectSub('DAY(AAA.fecha_hora_entrada)', 'DAY')
->selectSub('IFNULL(BBB.SALES, 0)', 'SALES')
->selectSub('IFNULL(BBB.NET, 0)', 'NET')
->leftJoinSub(DB::table('t_derivados')
->selectSub('DATE(fecha_hora_entrada)', 'fecha_hora_entrada')
->selectSub('SUM(neto)', 'NET')
->selectSub('COUNT(neto)', 'SALES')
->groupBy(DB::raw('DATE(fecha_hora_entrada)')), 'BBB', 'AAA.fecha_hora_entrada', 'BBB.fecha_hora_entrada')
->toSql();
}

我知道什么都看有点无聊。。但让我们关注下一句话:

SELECT t * 10 + u daynum
FROM (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
(SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) B
ORDER BY daynum
...

事实上,我还没有找到一种方法来添加第二个 `FROM` 至 `Query Builder` ```
SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) B
...

请找个这方面的Maven来帮我。事先非常感谢

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题