mysql - Laravel Query Builder 带有两个 FROM 语句的原始查询

Question

如果这个问题对于我真正转向这里的人来说听起来有点无聊，我深表歉意，因为我已经尝试以不同的方式解决我的问题，但我未能获得好的结果。

我正在尝试将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
...

这方面的专家可以帮助我。非常感谢您提前

标签： mysqlsqllaraveleloquentlaravel-query-builder