SQL Server中的IIf是MySQL中的If

SELECT SaleDetail.cTaxiNo, Category.cCarName, qryDriverExpenses.nAmount,
   Sale.nInvoiceNo, Sale.cCustomerName, 
   IF(cPackageType ='GENERAL', nProviderRate, nProviderRate/nProviderKM) AS nARate, 
   SaleDetail.nKM, SaleDetail.nAmount, Sale.dInvoiceDate, qryFuel.nAmount, 
    SaleDetail.nProviderKM, SaleDetail.nProviderRate
FROM qryFuel RIGHT JOIN 
(((Sale INNER JOIN SaleDetail ON Sale.nSalePk =            
  SaleDetail.nSalePk) INNER JOIN (CarMaster INNER JOIN Category ON 
 CarMaster.nCategoryPk = Category.nCategoryPK) ON SaleDetail.cTaxiNo = 
 CarMaster.cCarNo) LEFT JOIN qryDriverExpenses ON SaleDetail.nTourBookingpk 
 = qryDriverExpenses.nTourBookingPK) 
 ON qryFuel.nTourBookingPK = SaleDetail.nTourBookingpk    
ORDER BY Sale.nInvoiceNo, Sale.dInvoiceDate;

字符串

SELECT SaleDetail.cTaxiNo, Cate;ry.cCarName, qryDriverExpenses.nAmount,
   Sale.nInvoiceNo, Sale.cCustomerName, If(`cPackageType` ='GENERAL', 
`nProviderRate`,`nProviderRate`/`nProviderKM`) AS nARate, SaleDetail.nKM, 
SaleDetail.nAmount, Sale.dInvoiceDate, qryFuel.nAmount, 
SaleDetail.nProviderKM, SaleDetail.nProviderRate

  FROM qryFuel RIGHT JOIN (((Sale INNER JOIN SaleDetail ON Sale.nSalePk =            
  SaleDetail.nSalePk) INNER JOIN (CarMaster INNER JOIN Cate;ry ON 
 CarMaster.nCate;ryPk = Cate;ry.nCate;ryPK) ON SaleDetail.cTaxiNo = 
 CarMaster.cCarNo) LEFT JOIN qryDriverExpenses ON SaleDetail.nTourBookingpk 
 = qryDriverExpenses.nTourBookingPK) ON qryFuel.nTourBookingPK = 
 SaleDetail.nTourBookingpk

   ORDER BY Sale.nInvoiceNo, Sale.dInvoiceDate;

字符串
带引号的列名应该用引号括起来，而不是方括号。

试试这个。MySQL使用IF而不是IIF，也不太喜欢方括号：

SELECT SaleDetail.cTaxiNo, Category.cCarName, qryDriverExpenses.nAmount,
   Sale.nInvoiceNo, Sale.cCustomerName, If(cPackageType ='GENERAL', 
nProviderRate,nProviderRate/nProviderKM) AS nARate, SaleDetail.nKM, 
SaleDetail.nAmount, Sale.dInvoiceDate, qryFuel.nAmount, 
SaleDetail.nProviderKM, SaleDetail.nProviderRate

  FROM qryFuel RIGHT JOIN (((Sale INNER JOIN SaleDetail ON Sale.nSalePk =            
  SaleDetail.nSalePk) INNER JOIN (CarMaster INNER JOIN Category ON 
 CarMaster.nCategoryPk = Category.nCategoryPK) ON SaleDetail.cTaxiNo = 
 CarMaster.cCarNo) LEFT JOIN qryDriverExpenses ON SaleDetail.nTourBookingpk 
 = qryDriverExpenses.nTourBookingPK) ON qryFuel.nTourBookingPK = 
 SaleDetail.nTourBookingpk

   ORDER BY Sale.nInvoiceNo, Sale.dInvoiceDate;

字符串

使用这些连接只是自找麻烦。请学习正确的连接语法，https://technet.microsoft.com/en-us/library/ms191517%28v=sql.105%29.aspx。这里是格式化查询：

SELECT SaleDetail.cTaxiNo
,Category.cCarName
,qryDriverExpenses.nAmount
,Sale.nInvoiceNo
,Sale.cCustomerName
,CASE WHEN `cPackageType` ='GENERAL' THEN 
        `nProviderRate`
    ELSE
        `nProviderRate`/`nProviderKM`
    END AS nARate
,SaleDetail.nKM
,SaleDetail.nAmount
,Sale.dInvoiceDate
,qryFuel.nAmount
,SaleDetail.nProviderKM
,SaleDetail.nProviderRate
FROM qryFuel 
INNER JOIN SaleDetail
    ON qryFuel.nTourBookingPK = SaleDetail.nTourBookingpk
RIGHT OUTER JOIN Sale
    ON Sale.nSalePk = SaleDetail.nSalePk
INNER JOIN CarMaster 
    ON SaleDetail.cTaxiNo = CarMaster.cCarNo
INNER JOIN Category 
    ON CarMaster.nCategoryPk = Category.nCategoryPK
LEFT OUTER JOIN qryDriverExpenses 
    ON SaleDetail.nTourBookingpk = qryDriverExpenses.nTourBookingPK
ORDER BY Sale.nInvoiceNo, Sale.dInvoiceDate;

字符串

IIF在MySQL中无效
变化

IIf([cPackageType] ='GENERAL', 
       [nProviderRate],[nProviderRate]/[nProviderKM]) AS nARate

字符串
到

if [cPackageType] = 'GENERAL' then nProviderRate 
 else nProviderRate/nProviderKM
end as nARate

型

共享不同的方法，因为在MAC上使用SQL Server有一些限制。
将MS SQL Server转换为MySQL。这些简单的命令行命令将使您获得90%的免费和保存时间。

#Remove the '[' character
sed -i '' -e 's/\]//g' *.sql
#Remove the ']' character
sed -i '' -e 's/\[//g' *.sql
#Add a semicolon to the end of each line (INSERT commands)
sed -i '' -e '/[^;] *$/s/$/;/' *.sql
#Replace the dbo. before each table name
sed -i '' -e 's/INSERT dbo./INSERT INTO /g' *.sql
#Replace remove any remaining dbo.
sed -i '' -e 's/dbo.//g' *.sql

字符串
场景：你需要一个简单的本地数据库来模拟Mac上的SQL Server。避免在Mac上安装Docker SQL Server和Microsoft管理控制台。
找到一台MS Windows PC并使用Microsoft管理控制台导出数据库表和数据。这些将为每个表创建.sql文件。
对于非常大的文件，您可能希望使用Python脚本将它们“分块”。如果不熟悉Python，请使用chatGPT为您编写脚本。MySQL MySQL MySQL将开始抓取大文件集。
然后我们所要做的就是调整CREATE TABLE，无论你手动做什么，你都可以派生另一个sed命令行来自动化它。
更进一步，如果你有多个.sql文件目录，那么你可以使用'find'命令，后面跟着'sed'命令来递归地执行操作。