mysql vs mariadb查詢

希望我在這裡發帖不會犯任何錯誤:)

大家好，

我在 mysql 上使用以下查詢並且效果很好。但是當我在 mariadb 上使用相同的查詢（相同的數據庫）時，我得到了錯誤的值。

我試圖搜尋但沒有找到任何解決方案。使用 phpmyadmin 等檢查變數。innodb 值…

正確方向的提示會很棒。

mariadb-version：mysql Ver 15.1 Distrib 10.6.4-MariaDB，適用於使用 readline 5.2 的 debian-linux-gnu (x86_64)

mysql-version: mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)

最好的問候拉爾夫

With allresults As (
       Select
           *
       From
           testtesttest.abschlussgrundtracker
   )
Select
   final.crmid As crmid,
   final.changedon As Datum,
   final.fieldname As AbschlussgrundFeld,
   final.prevalue As AbschlussgrundAlt,
   final.postvalue As Abschlussgrund,
   testtesttest.vtiger_account.projektnameacc As Projektname,
   testtesttest.vtiger_account.datenquelleacc As Quelle
From
   (
       Select
           Concat(Date(testtesttest.abschlussgrundtracker.changedon), " 23:59:59") As changedon,
           testtesttest.vtiger_crmentity.deleted,
           allresults.crmid,
           allresults.changedon As changedon1,
           allresults.fieldname,
           allresults.prevalue,
           allresults.postvalue
       From
           testtesttest.abschlussgrundtracker Inner Join
           testtesttest.vtiger_crmentity On testtesttest.vtiger_crmentity.crmid = testtesttest.abschlussgrundtracker.crmid Left Join
           allresults On allresults.changedon <= testtesttest.abschlussgrundtracker.changedon
       where testtesttest.vtiger_crmentity.deleted = 0
       Group By
           allresults.crmid,
           testtesttest.abschlussgrundtracker.changedon
       Order By
           testtesttest.abschlussgrundtracker.changedon Desc,
           changedon1 Desc
   ) As final Inner Join
   testtesttest.vtiger_account On testtesttest.vtiger_account.accountid = final.crmid Inner Join
   testtesttest.vtiger_crmentity On testtesttest.vtiger_account.accountid = testtesttest.vtiger_crmentity.crmid
where testtesttest.vtiger_crmentity.deleted = 0
Group By
   final.crmid,
   final.changedon,
   Projektname,
   Quelle
Order By
   Datum

我的解決方案：

With
   tester As (
       Select
           *
       From
           (
               Select Distinct
                   *
               From
                   (
                       Select
                           Concat(abschlussgrundtracker.crmid, Left(abschlussgrundtracker.changedon, 10)) As crmiddate,
                           abschlussgrundtracker.whodid,
                           Left(abschlussgrundtracker.changedon, 10) As testerdate,
                           abschlussgrundtracker.crmid,
                           Date_Format(abschlussgrundtracker.changedon, "%Y-%m-%d %T") As datim,
                           abschlussgrundtracker.changedon,
                           abschlussgrundtracker.fieldname,
                           abschlussgrundtracker.prevalue,
                           abschlussgrundtracker.postvalue as Abschlussgrund,
                           vtiger_account.accountname,
                           vtiger_account.accountid,
                           vtiger_account.datenquelleacc AS Quelle,
                           vtiger_account.projektnameacc AS Projectname
                       From
                           abschlussgrundtracker Inner join
                           vtiger_account On vtiger_account.accountid = abschlussgrundtracker.crmid Inner Join
                           vtiger_accountscf On vtiger_accountscf.accountid = vtiger_account.accountid
                               Inner Join vtiger_crmentity ON vtiger_account.accountid = vtiger_crmentity.crmid Inner Join
                           vtiger_users On vtiger_users.id = abschlussgrundtracker.whodid
                       Group By
                           abschlussgrundtracker.crmid,
                           abschlussgrundtracker.changedon
                       Having
                           abschlussgrundtracker.changedon = Max(abschlussgrundtracker.changedon) And
                           abschlussgrundtracker.crmid
                   ) As test
               Order By
                   test.datim Desc
           ) As test2
   ),
alldates as (
Select distinct
       concat(left(abschlussgrundtracker.changedon,10)," 23:59:59") as Datum
From
       abschlussgrundtracker
group by
       abschlussgrundtracker.crmid,abschlussgrundtracker.changedon
),
getunique as (
Select
   *
From
   tester
group by tester.crmiddate
order by tester.datim DESC
)

select Datum,crmid,whodid,Abschlussgrund,Quelle,Projectname
from  
(
select *
from
     alldates full join
     getunique
where Datum >= datim
order by Datum
) as bla
group by bla.Datum,bla.crmid

您的查詢不正確 - 它的 GROUP BY（兩者）不完整。這是差異的來源 - 某些輸出列的值是不確定的。而“mysql 上的以下查詢並且工作完美”這一事實只是聲稱您很幸運（但您可能隨時獲得對您不正確的輸出）。

解決這個問題。擴展分組表達式或將聚合函式添加到輸出表達式中。

---

PS。您目前的 CTEallresults顯然是多餘的 - 刪除它並在您的查詢中使用它的源表而不是這個 CTE。

聚苯乙烯。將您的final子查詢轉換為 CTE - 這將使您的查詢更具可讀性。

引用自：https://dba.stackexchange.com/questions/300003