SQL小提琴和MySQL服务器/PhpMyadmin中的结果不同 [英] Different result in SQL fiddle and in MySQL server/PhpMyadmin

问题描述

这是sql小提琴: http://sqlfiddle.com/#!2/e6acc/4

它显示了正确的结果，因为我希望对重复的条目进行枚举.

And it shows the right result since I want the duplicate entries to be enumerated.

但是当我在mysql/phpmyadmin上运行时，结果是这样的:

But when I run in on mysql/phpmyadmin, the result is this:

1515    Abdominal
1100    Hep B Inj Fee
40  1-Ligation
40  1-Ligation
900 1-Suturing Fee
900 1-Suturing Fee
900 1-Suturing Fee
900 1-Suturing Fee

全部为1.我已经重新启动了我的电脑.和它仍然一样.这是为什么?我只是从mysql到小提琴中检索了csv表.

It's all 1. I already restarted my pc. and its still the same. Why is that? I just retrieved the csv tables from my mysql to the fiddle.

好吧，这越来越奇怪了.在我的前端，当我查看其他ID时，它可以正常工作:

edit: Ok this is getting weirder. On my front-end, its working when I view other ID's:

但是，当我更改查询的ID并在phpmyadmin上输入时，它仍显示1.

But still, when I change the id on my query and enter in on phpmyadmin, it stil shows 1.

推荐答案

我不知道为什么SQL Fiddle与实际结果不同，但是我设法使其在PHP中起作用.使用以下查询:

I don't have any idea why the SQL Fiddle is different than the actual result, but I managed to make it work for me in PHP. Use the following query:

SELECT
  c.procno,
  CONCAT(CASE WHEN cnt > 1 THEN CONCAT(RN,'-') ELSE '' END, t.Proc) Proc
FROM
  (
    SELECT
      @curRow:=CASE WHEN @prevRow = a.Proc THEN @curRow+1 ELSE 1 END AS rn,
      a.Proc,
      a.Procno,
      @prevRow:=Proc grp
    FROM (    
          SELECT
            `incurredcharges`.`procedure_no` procno,
            `c`.`procedure` proc
          FROM
            incurredcharges
            INNER JOIN (
              SELECT `procedure`, `procedure_no` FROM `charges`
              UNION ALL
              SELECT `confinement`, `procedure_no` FROM `confinement`
              UNION ALL
              SELECT `service`, `procedure_no` FROM `ultrasound`
            ) c ON `incurredcharges`.`procedure_no` = c.`procedure_no`
          WHERE `incurredcharges`.`patient_no` = '34'
          ORDER BY `c`.`procedure`
      ) a 
          JOIN (SELECT @curRow:=0, @prevRow:= '') r
  ) t JOIN
  (
          SELECT
            `incurredcharges`.`procedure_no` procno,
            `c`.`procedure` proc, Count(*) cnt
          FROM
            incurredcharges
            INNER JOIN (
              SELECT `procedure`, `procedure_no` FROM `charges`
              UNION ALL
              SELECT `confinement`, `procedure_no` FROM `confinement`
              UNION ALL
              SELECT `service`, `procedure_no` FROM `ultrasound`
            ) c ON `incurredcharges`.`procedure_no` = c.`procedure_no`
          WHERE `incurredcharges`.`patient_no` = '34'
          GROUP BY `incurredcharges`.`procedure_no`,
            `c`.`procedure`
  ) c ON t.proc = c.proc

我唯一更改的是从JOIN (SELECT @curRow:=0) r到JOIN (SELECT @curRow:=0, @prevRow:= '') r

这篇关于SQL小提琴和MySQL服务器/PhpMyadmin中的结果不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！