Laravel输出不同的输出比MySQL查询(Laravel Outputs Different O

2019-09-30 01:39发布

站内文章 / 后端开发
35 0 0

我生成的MySQL查询其给出正确的输出我需要（上MySQL工作台测试）。 但是，当我申请使用Laravel DB ::选择相同的SQL查询（DB ::原始（））我收到一个完全不同的输出。 真的很感激，就如何克服这个一些建议。

我一直在用代码修修补补弄清楚什么错误。 当查询从Laravel运行显然我在SQL生成的数字序列会失控。 但原始查询在MySQL工作完全正常。 我已经加入了SET在Laravel代码语句。 但是，当我通过My​​SQL的（工作台或PHPMyAdmin中）运行查询是没有必要的。

我的要求：

  • 要删除的重复导致由一组值与ROLLUP

SQL查询： 

SELECT hodname, year, month, ptype, pcode, rname, sah, sae, sbe
FROM
    (SELECT
         (CASE tblhod.hodname
             WHEN @curHOD THEN @curHODRow:=@curHODRow+1
             ELSE @curHODRow:=1
          END) AS seqhodname,
         @curHOD:=tblhod.hodname AS hodnamedata,
         (CASE 
             WHEN @curHODRow = 1 THEN tblhod.hodname
             ELSE ''
          END) AS hodname,
         (CASE tblhod.hodname
             WHEN @curHOD 
                THEN CASE tblperiod.year
                        WHEN @curYear THEN @curYearRow:=@curYearRow+1
                        ELSE @curYearRow:=1
                     END
          END) AS seqyear,
         @curYear:=tblperiod.year AS yeardata, 
         (CASE 
             WHEN @curYearRow = 1 THEN tblperiod.year
             ELSE ''
          END) AS year,
         (CASE tblhod.hodname
             WHEN @curHOD 
                THEN CASE tblperiod.year
                        WHEN @curYear 
                           THEN CASE tblperiod.month
                                   WHEN @curMonth THEN @curMonthRow:=@curMonthRow + 1
                                   ELSE @curMonthRow:=1
                                END
                     END
          END) AS seqmonth,
         @curMonth:=tblperiod.month AS monthdata,
        (CASE WHEN @curMonthRow=1 THEN tblperiod.month
ELSE ''
END) AS month,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN CASE tblperiod.month
WHEN @curMonth THEN CASE tblprotype.ptype
WHEN @curPtype THEN @curPtypeRow:=@curPtypeRow+1
ELSE @curPtypeRow:=1
END
END
END
END) AS seqptype,
@curPtype:=tblprotype.ptype AS ptypedata,
(CASE WHEN @curPtypeRow=1 THEN tblprotype.ptype
ELSE ''
END) AS ptype,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN CASE tblperiod.month
WHEN @curMonth THEN CASE tblprotype.ptype
WHEN @curPtype THEN CASE tblproject.pcode
WHEN @curPcode THEN @curPcodeRow:=@curPcodeRow+1
ELSE @curPcodeRow:=1
END
END
END
END
END) AS seqpcode,
@curPcode:=tblproject.pcode AS pcodedata,
(CASE WHEN @curPcodeRow=1 THEN tblproject.pcode
ELSE ''
END) AS pcode,
tblresource.rname AS rname, SUM(ah) AS sah, SUM(ae) AS sae, SUM(be) AS sbe
FROM `tblallocation`
JOIN tblhod ON tblallocation.hodid = tblhod.hodid
JOIN tblperiod ON tblallocation.periodid = tblperiod.periodid
JOIN tblprotype ON tblallocation.ptid = tblprotype.ptid
JOIN tblproject ON tblallocation.pid = tblproject.pid
JOIN tblresource ON tblallocation.rid = tblresource.rid
JOIN(SELECT @curHODRow:=0, @curHOD:=0,@curYearRow:=0, @curYear:=0, @curMonthRow:=0, @curMonth:=0, @curPtypeRow:=0, @curPtype:=0, @curPcodeRow:=0, @curPcode:=0) v
GROUP BY tblhod.hodname, tblperiod.year, tblperiod.month, tblprotype.ptype, tblproject.pcode, tblresource.rname WITH ROLLUP) AS final

所需的输出： 

COL1     COL2     COL3     COL4        COL5     COL6
-----------------------------------------------------
GRP1     GRP1.1   GRP1.1.1 GRP1.1.1.1   15       32
                           GRP1.1.1.2   26        5
                           GRP1.1.1.3   10       17
                                        51       54
                  GRP1.1.2 GRP1.1.2.1    2        1  
                           GRP1.1.2.2   31       24
                           GRP1.1.2.3    7       13
                           GRP1.1.2.4   17       15
                                        57       53
                                       108      107
         GRP1.2   GRP1.2.1 GRP1.2.1.1    9        3
                           GRP1.2.1.2    6        6
                                        15        9
                  GRP1.2.2 GRP1.2.2.1   10       10
                                        10       10   
                  GRP1.2.3 GRP1.2.3.1    4        3
                           GRP1.2.3.2    8        2
                           GRP1.2.3.3    1        1
                                        13        6
                                        38       25
                                       146      132
GRP2     GRP2.1   GRP2.1.1 GRP2.1.1.1   22       34
                           GRP2.1.1.2   11       30
                                        33       64
                  GRP2.1.2 GRP2.1.2.1   32       23
                                        32       23
                                        65       87
         GRP2.2   GRP2.2.1 GRP2.2.1.1    2        2
                                         2        2
                                         2        2
                                        67       89
                                       213      222

Laravel输出： 

COL1     COL2     COL3     COL4        COL5     COL6
-----------------------------------------------------
                           GRP1.1.1.2   15       32
GRP1                       GRP1.1.1.2   26        5
                           GRP1.1.1.3   10       17
                                        51       54
                           GRP1.1.2.1    2        1  
                           GRP1.1.2.2   31       24
                           GRP1.1.2.3    7       13
                           GRP1.1.2.4   17       15
                                        57       53
                                       108      107
                           GRP1.2.1.1    9        3
                           GRP1.2.1.2    6        6
                                        15        9
                           GRP1.2.2.1   10       10
                                        10       10   
                           GRP1.2.3.1    4        3
                           GRP1.2.3.2    8        2
                           GRP1.2.3.3    1        1
                                        13        6
                                        38       25
                                       146      132
                           GRP2.1.1.1   22       34
                           GRP2.1.1.2   11       30
                                        33       64
                           GRP2.1.2.1   32       23
                                        32       23
                                        65       87
                           GRP2.2.1.1    2        2
                                         2        2
                                         2        2
                                        67       89
                                       213      222

Laravel代码： 

DB::statement(DB::raw('SET @curHODRow=0, @curHOD=0,@curYearRow=0, @curYear=0, @curMonthRow=0, @curMonth=0, @curPtypeRow=0, @curPtype=0, @curPcodeRow=0, @curPcode=0'));
DB::select(DB::raw("SELECT hodname, year, month, ptype, pcode, rname, sah, sae, sbe
FROM
(SELECT
(CASE tblhod.hodname
WHEN @curHOD THEN @curHODRow:=@curHODRow+1
ELSE @curHODRow:=1
END) AS seqhodname,
@curHOD:=tblhod.hodname AS hodnamedata,
(CASE WHEN @curHODRow=1 THEN tblhod.hodname
ELSE ''
END) AS hodname,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN @curYearRow:=@curYearRow+1
ELSE @curYearRow:=1
END
END) AS seqyear,
@curYear:=tblperiod.year AS yeardata, 
(CASE WHEN @curYearRow=1 THEN tblperiod.year
ELSE ''
END) AS year,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN CASE tblperiod.month
WHEN @curMonth THEN @curMonthRow:=@curMonthRow+1
ELSE @curMonthRow:=1
END
END
END) AS seqmonth,
@curMonth:=tblperiod.month AS monthdata,
(CASE WHEN @curMonthRow=1 THEN tblperiod.month
ELSE ''
END) AS month,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN CASE tblperiod.month
WHEN @curMonth THEN CASE tblprotype.ptype
WHEN @curPtype THEN @curPtypeRow:=@curPtypeRow+1
ELSE @curPtypeRow:=1
END
END
END
END) AS seqptype,
@curPtype:=tblprotype.ptype AS ptypedata,
(CASE WHEN @curPtypeRow=1 THEN tblprotype.ptype
ELSE ''
END) AS ptype,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN CASE tblperiod.month
WHEN @curMonth THEN CASE tblprotype.ptype
WHEN @curPtype THEN CASE tblproject.pcode
WHEN @curPcode THEN @curPcodeRow:=@curPcodeRow+1
ELSE @curPcodeRow:=1
END
END
END
END
END) AS seqpcode,
@curPcode:=tblproject.pcode AS pcodedata,
(CASE WHEN @curPcodeRow:=1 THEN tblproject.pcode
ELSE ''
END) AS pcode,
tblresource.rname AS rname, SUM(ah) AS sah, SUM(ae) AS sae, SUM(be) AS sbe
FROM `tblallocation`
JOIN tblhod ON tblallocation.hodid = tblhod.hodid
JOIN tblperiod ON tblallocation.periodid = tblperiod.periodid
JOIN tblprotype ON tblallocation.ptid = tblprotype.ptid
JOIN tblproject ON tblallocation.pid = tblproject.pid
JOIN tblresource ON tblallocation.rid = tblresource.rid
JOIN(SELECT @curHODRow:=0, @curHOD:=0,@curYearRow:=0, @curYear:=0, @curMonthRow:=0, @curMonth:=0, @curPtypeRow:=0, @curPtype:=0, @curPcodeRow:=0, @curPcode:=0) v
GROUP BY tblhod.hodname, tblperiod.year, tblperiod.month, tblprotype.ptype, tblproject.pcode, tblresource.rname WITH ROLLUP) AS final"));

Answer 1:

我设法解决我的问题，但没有使用Laravel ORM。 我真的不知道为什么没有与laravel工作，但与PHP的MySQLi函数工作。 我认为这个问题是，当我使用的ORM方法MySQL的变量没有得到初始化。

我只是用PHP MySQL的内置方法和瞧一切都很好。

解： 

$query = SELECT hodname, year, month, ptype, pcode, rname, sah, sae, sbe
FROM
(SELECT
(CASE tblhod.hodname
WHEN @curHOD THEN @curHODRow:=@curHODRow+1
ELSE @curHODRow:=1
END) AS seqhodname,
@curHOD:=tblhod.hodname AS hodnamedata,
(CASE WHEN @curHODRow=1 THEN tblhod.hodname
ELSE ''
END) AS hodname,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN @curYearRow:=@curYearRow+1
ELSE @curYearRow:=1
END
END) AS seqyear,
@curYear:=tblperiod.year AS yeardata, 
(CASE WHEN @curYearRow=1 THEN tblperiod.year
ELSE ''
END) AS year,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN CASE tblperiod.month
WHEN @curMonth THEN @curMonthRow:=@curMonthRow+1
ELSE @curMonthRow:=1
END
END
END) AS seqmonth,
@curMonth:=tblperiod.month AS monthdata,
(CASE WHEN @curMonthRow=1 THEN tblperiod.month
ELSE ''
END) AS month,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN CASE tblperiod.month
WHEN @curMonth THEN CASE tblprotype.ptype
WHEN @curPtype THEN @curPtypeRow:=@curPtypeRow+1
ELSE @curPtypeRow:=1
END
END
END
END) AS seqptype,
@curPtype:=tblprotype.ptype AS ptypedata,
(CASE WHEN @curPtypeRow=1 THEN tblprotype.ptype
ELSE ''
END) AS ptype,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN CASE tblperiod.month
WHEN @curMonth THEN CASE tblprotype.ptype
WHEN @curPtype THEN CASE tblproject.pcode
WHEN @curPcode THEN @curPcodeRow:=@curPcodeRow+1
ELSE @curPcodeRow:=1
END
END
END
END
END) AS seqpcode,
@curPcode:=tblproject.pcode AS pcodedata,
(CASE WHEN @curPcodeRow=1 THEN tblproject.pcode
ELSE ''
END) AS pcode,
tblresource.rname AS rname, SUM(ah) AS sah, SUM(ae) AS sae, SUM(be) AS sbe
FROM `tblallocation`
JOIN tblhod ON tblallocation.hodid = tblhod.hodid
JOIN tblperiod ON tblallocation.periodid = tblperiod.periodid
JOIN tblprotype ON tblallocation.ptid = tblprotype.ptid
JOIN tblproject ON tblallocation.pid = tblproject.pid
JOIN tblresource ON tblallocation.rid = tblresource.rid
JOIN(SELECT @curHODRow:=0, @curHOD:=0,@curYearRow:=0, @curYear:=0, @curMonthRow:=0, @curMonth:=0, @curPtypeRow:=0, @curPtype:=0, @curPcodeRow:=0, @curPcode:=0) v
GROUP BY tblhod.hodname, tblperiod.year, tblperiod.month, tblprotype.ptype, tblproject.pcode, tblresource.rname WITH ROLLUP) AS final;

$con = mysqli_connect('host', 'username', 'password', 'database');
$result = mysqli_query($con, $query);
$data = mysqli_fetch_all($result);

如果任何人都可以点我对这个的ORM解决方案将是巨大的！



文章来源: Laravel Outputs Different Output Than MySQL Query
标签： php mysql laravel laravel-5 case
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~