将超大规模查询从SQL Server转换为MySQL – JOIN问题

MsSQL 前端之家
前端之家收集整理的这篇文章主要介绍了将超大规模查询从SQL Server转换为MySQL – JOIN问题，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
我有一个在sql Server 2008中运行良好的查询但是当我尝试将它与 MySQL一起使用时,它似乎是JOIN的问题.

第一个错误是

You have an error in your sql Syntax; check the manual that corresponds to your MysqL server version for the right Syntax to use near ‘ON faxcont.IDDireccion = dir_cont.RecID AND faxcont.Tipo = 2 AND faxcont.Defecto’ at line 52″

如何纠正？

  1. SELECT     req.RecID,req.FechaEntrega,req.IDUsuario AS RecIDResp,req.NroSucursal,req_dp.NroFila,req.ID,req.ID AS NroReq,emp.Empresa AS NombreEmpresa,req.IDFiscalSistema,req_dp.ImporteUnitario1,req_dp.ImporteUnitario2,req_dp.ImporteUnitario3,req_dp.ImporteUnitario4,req_dp.ImporteUnitario5,req_dp.ImporteUnitario6,req.NroMoneda,req_dp.SobrePrecio,req_dp.Impuesto AS TasaIVA,dir_emp_sist.CodPais AS CodPaisEmpresa,dir_emp_sist.CodCiudad AS CodCiudadEmpresa,tel.numero AS TelefonoEmpresa,fax.numero AS FaxEmpresa,mail_emp.Direccion AS MailEmpresa,emp_sist.SitioWeb AS WebEmpresa,dir_cont.CodPais AS CodPaisCont,dir_cont.CodCiudad AS CodCiudadCont,telcont.numero AS TelefonoCont,faxcont.numero AS FaxCont,req.FechaModificacion AS FechaGrabacion,cont.Apellido AS ApellidoCont,cont.Nombre AS NombreCont,mail_cont.Direccion AS MailCont,emp_sist.Empresa AS NombreEmpSist,fis_sist.RazonSocial AS RazonSocialSist,fis_sist.NroImpuesto1 AS CUIT,dir_fis_sist.Calle AS CalleFisSist,dir_fis_sist.Numero AS NroFisSist,dir_fis_sist.Piso AS PisoFisSist,dir_fis_sist.Dto AS DtoFisSist,dir_fis_sist.CP AS CPFisSist,dir_fis_sist.Localidad AS LocFisSist,dir_fis_sist.Ciudad AS CiudadFisSist,dir_fis_sist.Provincia AS ProvinciaFisSist,dir_fis_sist.Pais AS PaisFisSist,responsable.Nombre AS NombreResp,responsable.Apellido AS ApellidoResp,mail_resp.Direccion AS MailResp,dir_resp.CodPais AS CodPaisResp,dir_resp.CodCiudad AS CodCiudadResp,telresp.numero AS TelefonoResp,com.DireccionEntrega AS DirEntrega,CASE PATINDEX('%.%',req_dp.NroItem) WHEN 0 THEN '0' WHEN 2 THEN '1' END AS Item,req_dp.NroItem) WHEN 0 THEN req_dp.NroItem WHEN 2 THEN LEFT(req_dp.NroItem,1) END AS Item2,req_dp.NroItem,req_dp.Codigo,req_dp.Descripcion,CASE WHEN req_dp.recid IS NULL 
  2. THEN 0 WHEN req_dp.Estado = 9 THEN 0 ELSE (CASE mon.Numero WHEN 1 THEN req_dp.ImporteUnitario1 WHEN 2 THEN req_dp.ImporteUnitario2 WHEN 3 THEN req_dp.ImporteUnitario3
  3. WHEN 4 THEN req_dp.ImporteUnitario4 WHEN 5 THEN req_dp.ImporteUnitario5 WHEN 6 THEN req_dp.ImporteUnitario6 END) * ((100 - req_dp.Descuento) / 100) 
  4. * ((100 + req_dp.SobrePrecio) / 100) END AS PrecIoU,req_dp.Cantidad,(CASE mon.Numero WHEN 1 THEN req_dp.importeprecio1 WHEN 2 THEN req_dp.importeprecio2 WHEN 3 THEN req_dp.importeprecio3 WHEN 4 THEN req_dp.importeprecio4
  5. WHEN 5 THEN req_dp.importeprecio5 WHEN 6 THEN req_dp.importeprecio6 END) AS Importe,req_dp.Descuento AS DescProd,CASE WHEN req_dp.Descuento = 0 THEN 0 ELSE ((CASE mon.Numero WHEN 1 THEN req_dp.importeprecio1 WHEN 2 THEN req_dp.importeprecio2 WHEN 3 THEN req_dp.importeprecio3
  6. WHEN 4 THEN req_dp.importeprecio4 WHEN 5 THEN req_dp.importeprecio5 WHEN 6 THEN req_dp.importeprecio6 END) 
  7. - ((req_dp.Descuento * (CASE mon.Numero WHEN 1 THEN req_dp.importeprecio1 WHEN 2 THEN req_dp.importeprecio2 WHEN 3 THEN req_dp.importeprecio3 WHEN 4
  8. THEN req_dp.importeprecio4 WHEN 5 THEN req_dp.importeprecio5 WHEN 6 THEN req_dp.importeprecio6 END) / 100))) 
  9. - (CASE mon.Numero WHEN 1 THEN req_dp.importeprecio1 WHEN 2 THEN req_dp.importeprecio2 WHEN 3 THEN req_dp.importeprecio3 WHEN 4 THEN req_dp.importeprecio4
  10. WHEN 5 THEN req_dp.importeprecio5 WHEN 6 THEN req_dp.importeprecio6 END) END AS ImpDescXProd,req.SubTotal,req.Descuento AS PorcDescGral,req.SubTotal - req.SubTotal * ((100 - req.Descuento) / 100) AS DescuentoGral,req.SubTotal2 AS SubTotalConDto,req.Impuesto AS IVA,req.Total,mon.Simbolo,mon.Moneda,req.ID AS NroRequerimiento,req.MostrarImpuestos,CASE req.MostrarImpuestos WHEN 1 THEN 'Los valores están expresados en ' + mon.Moneda + '. Incluyen IVA.' WHEN 0 THEN 'Los valores están expresados en ' + mon.Moneda
  11. + '. No incluyen IVA.' END AS LeyendaIVA,com.CondicionesPago AS CondPago,com.FormaPago,com.PlazoEntrega,com.HorarioEntrega,com_t.Tipo AS TipoTransp,com_t.valor AS TranspPropio,emp_t.Empresa AS EmpTransp,fis_t.RazonSocial AS RazSocTransp,com.PrecioEnvio,adi.Campo1 AS Adi1,adi.Campo2 AS Adi2,adi.Campo3 AS Adi3,adi.Campo4 AS Adi4,adi.Campo5 AS Adi5,adi.Campo6 AS Adi6,adi.Campo7 AS Adi7,adi.Campo8 AS Adi8,adi.Campo9 AS Adi9,adi.Campo10 AS Adi10,adi.Campo11 AS Adi11,adi.Campo12 AS Adi12,adi.Campo13 AS Adi13,adi.Campo14 AS Adi14,adi.Campo15 AS Adi15,adi.Campo16 AS Adi16,adi.Campo17 AS Adi17,adi.Campo18 AS Adi18,adi.Campo19 AS Adi19,adi.Campo20 AS Adi20,ped_1.IDRequerimiento AS IDPed,ped2_1.RecID AS IDPed2,ped3.RecID AS IDPed3,req_dp.Notas,req.Descuento AS DesReq,cont.IDContacto AS RecidContReq,usu.RecID AS RecidContResp,direcciones.Calle,direcciones.Numero,direcciones.Piso,direcciones.Dto,direcciones.Torre,direcciones.CP,direcciones.Ciudad,direcciones.Provincia,direcciones.Pais,direcciones.Defecto AS Dir_Cont_Defecto,direcciones.RecID AS Dir_Cont_Recid
  12. FROM         telefonos AS faxcont RIGHT OUTER JOIN
  13. telefonos AS telcont RIGHT OUTER JOIN
  14. direcciones AS dir_cont RIGHT OUTER JOIN
  15. fiscal AS fis_sist INNER JOIN
  16. requerimientos AS req INNER JOIN
  17. requerimientositems AS req_dp ON req.RecID = req_dp.IDRequerimiento INNER JOIN
  18. moneda AS mon ON req.NroMoneda = mon.Numero INNER JOIN
  19. contactos AS cont ON req.IDRef = cont.IDContacto INNER JOIN
  20. empresas AS emp ON cont.IDEmpresa = emp.IDEmpresa ON fis_sist.RecID = req.IDFiscalSistema INNER JOIN
  21. empresas AS emp_sist ON fis_sist.IDref = emp_sist.IDEmpresa LEFT OUTER JOIN
  22. direcciones ON emp.IDEmpresa = direcciones.idref LEFT OUTER JOIN
  23. contactos AS responsable INNER JOIN
  24. usuarios AS usu ON responsable.IDContacto = usu.IDContacto ON req.IDUsuario = usu.RecID LEFT OUTER JOIN
  25. direccionescorreo AS mail_resp ON responsable.IDContacto = mail_resp.IDref AND mail_resp.Defecto = 1 LEFT OUTER JOIN
  26. direcciones AS dir_emp_sist ON emp_sist.IDEmpresa = dir_emp_sist.idref AND dir_emp_sist.Defecto = 1 LEFT OUTER JOIN
  27. direcciones AS dir_fis_sist ON fis_sist.IDDireccion = dir_fis_sist.RecID ON dir_cont.idref = cont.IDContacto ON telcont.IDDireccion = dir_cont.RecID AND 
  28. telcont.Tipo = 3 AND telcont.Defecto = 1 ON faxcont.IDDireccion = dir_cont.RecID AND faxcont.Tipo = 2 AND faxcont.Defecto = 1 LEFT OUTER JOIN
  29. direccionescorreo AS mail_cont ON cont.IDContacto = mail_cont.IDref AND mail_cont.Defecto = 1 LEFT OUTER JOIN
  30. direcciones AS dir_resp ON responsable.IDContacto = dir_resp.idref AND dir_resp.Defecto = 1 LEFT OUTER JOIN
  31. telefonos AS telresp ON responsable.IDContacto = telresp.IDref2 AND telresp.Tipo = 3 AND telresp.Defecto = 1 LEFT OUTER JOIN
  32. comercial AS com ON req.RecID = com.idref LEFT OUTER JOIN
  33. comercialtransportes AS com_t ON com.RecID = com_t.idref LEFT OUTER JOIN
  34. empresas AS emp_t ON com_t.IDEmpresa = emp_t.IDEmpresa LEFT OUTER JOIN
  35. fiscal AS fis_t ON emp_t.IDEmpresa = fis_t.IDref AND fis_t.Defecto = 1 LEFT OUTER JOIN
  36. adicional AS adi ON req.RecID = adi.IDRef LEFT OUTER JOIN
  37. telefonos AS tel ON dir_emp_sist.RecID = tel.IDDireccion AND tel.Tipo = 5 AND tel.Defecto = 1 LEFT OUTER JOIN
  38. telefonos AS fax ON dir_emp_sist.RecID = fax.IDDireccion AND fax.Tipo = 7 AND fax.Defecto = 1 LEFT OUTER JOIN
  39. direccionescorreo AS mail_emp ON emp_sist.IDEmpresa = mail_emp.IDref AND mail_emp.Defecto = 1 LEFT OUTER JOIN
  40. (SELECT     IDRequerimiento
  41. FROM          pedidos AS ped
  42. GROUP BY IDRequerimiento) AS ped_1 ON req.RecID = ped_1.IDRequerimiento LEFT OUTER JOIN
  43. (SELECT     RecID
  44. FROM          pedidos AS ped2
  45. GROUP BY RecID) AS ped2_1 ON req.IDPedido = ped2_1.RecID LEFT OUTER JOIN
  46. requerimientositemsclientes AS req_dp_cli ON req_dp.RecID = req_dp_cli.IDReqProd LEFT OUTER JOIN
  47. pedidositems AS ped_dp ON req_dp_cli.IDPedProd = ped_dp.RecID LEFT OUTER JOIN
  48. pedidos AS ped3 ON ped_dp.IDPedido = ped3.RecID
  49. WHERE     (req.RecID = '{?ID}')
  50. ORDER BY req_dp.NroFila

编辑

附加下面的格式化代码,使用http://sqlformat.appspot.com：

  1. SELECT req.RecID,req_dp.NroItem)
  2.            WHEN 0 THEN '0'
  3.            WHEN 2 THEN '1'
  4.        END AS Item,req_dp.NroItem)
  5.            WHEN 0 THEN req_dp.NroItem
  6.            WHEN 2 THEN LEFT(req_dp.NroItem,1)
  7.        END AS Item2,CASE
  8.            WHEN req_dp.recid IS NULL THEN 0
  9.            WHEN req_dp.Estado = 9 THEN 0
  10.            ELSE (CASE mon.Numero
  11.                      WHEN 1 THEN req_dp.ImporteUnitario1
  12.                      WHEN 2 THEN req_dp.ImporteUnitario2
  13.                      WHEN 3 THEN req_dp.ImporteUnitario3
  14.                      WHEN 4 THEN req_dp.ImporteUnitario4
  15.                      WHEN 5 THEN req_dp.ImporteUnitario5
  16.                      WHEN 6 THEN req_dp.ImporteUnitario6
  17.                  END) * ((100 - req_dp.Descuento) / 100) * ((100 + req_dp.SobrePrecio) / 100)
  18.        END AS PrecIoU,(CASE mon.Numero
  19.             WHEN 1 THEN req_dp.importeprecio1
  20.             WHEN 2 THEN req_dp.importeprecio2
  21.             WHEN 3 THEN req_dp.importeprecio3
  22.             WHEN 4 THEN req_dp.importeprecio4
  23.             WHEN 5 THEN req_dp.importeprecio5
  24.             WHEN 6 THEN req_dp.importeprecio6
  25.         END) AS Importe,CASE
  26.            WHEN req_dp.Descuento = 0 THEN 0
  27.            ELSE ((CASE mon.Numero WHEN 1 THEN req_dp.importeprecio1 WHEN 2 THEN req_dp.importeprecio2 WHEN 3 THEN req_dp.importeprecio3 WHEN 4 THEN req_dp.importeprecio4 WHEN 5 THEN req_dp.importeprecio5 WHEN 6 THEN req_dp.importeprecio6 END) - ((req_dp.Descuento * (CASE mon.Numero WHEN 1 THEN req_dp.importeprecio1 WHEN 2 THEN req_dp.importeprecio2 WHEN 3 THEN req_dp.importeprecio3 WHEN 4 THEN req_dp.importeprecio4 WHEN 5 THEN req_dp.importeprecio5 WHEN 6 THEN req_dp.importeprecio6 END) / 100))) - (CASE mon.Numero
  28.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 1 THEN req_dp.importeprecio1
  29.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 2 THEN req_dp.importeprecio2
  30.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 3 THEN req_dp.importeprecio3
  31.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 4 THEN req_dp.importeprecio4
  32.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 5 THEN req_dp.importeprecio5
  33.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 6 THEN req_dp.importeprecio6
  34.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         END)
  35.        END AS ImpDescXProd,CASE req.MostrarImpuestos
  36.                                          WHEN 1 THEN 'Los valores están expresados en ' + mon.Moneda + '. Incluyen IVA.'
  37.                                          WHEN 0 THEN 'Los valores están expresados en ' + mon.Moneda + '. No incluyen IVA.'
  38.                                      END AS LeyendaIVA,direcciones.RecID AS Dir_Cont_Recid
  39. FROM telefonos AS faxcont
  40. RIGHT OUTER JOIN telefonos AS telcont
  41. RIGHT OUTER JOIN direcciones AS dir_cont
  42. RIGHT OUTER JOIN fiscal AS fis_sist
  43. INNER JOIN requerimientos AS req
  44. INNER JOIN requerimientositems AS req_dp ON req.RecID = req_dp.IDRequerimiento
  45. INNER JOIN moneda AS mon ON req.NroMoneda = mon.Numero
  46. INNER JOIN contactos AS cont ON req.IDRef = cont.IDContacto
  47. INNER JOIN empresas AS emp ON cont.IDEmpresa = emp.IDEmpresa ON fis_sist.RecID = req.IDFiscalSistema
  48. INNER JOIN empresas AS emp_sist ON fis_sist.IDref = emp_sist.IDEmpresa
  49. LEFT OUTER JOIN direcciones ON emp.IDEmpresa = direcciones.idref
  50. LEFT OUTER JOIN contactos AS responsable
  51. INNER JOIN usuarios AS usu ON responsable.IDContacto = usu.IDContacto ON req.IDUsuario = usu.RecID
  52. LEFT OUTER JOIN direccionescorreo AS mail_resp ON responsable.IDContacto = mail_resp.IDref
  53. AND mail_resp.Defecto = 1
  54. LEFT OUTER JOIN direcciones AS dir_emp_sist ON emp_sist.IDEmpresa = dir_emp_sist.idref
  55. AND dir_emp_sist.Defecto = 1
  56. LEFT OUTER JOIN direcciones AS dir_fis_sist ON fis_sist.IDDireccion = dir_fis_sist.RecID ON dir_cont.idref = cont.IDContacto ON telcont.IDDireccion = dir_cont.RecID
  57. AND telcont.Tipo = 3
  58. AND telcont.Defecto = 1 ON faxcont.IDDireccion = dir_cont.RecID
  59. AND faxcont.Tipo = 2
  60. AND faxcont.Defecto = 1
  61. LEFT OUTER JOIN direccionescorreo AS mail_cont ON cont.IDContacto = mail_cont.IDref
  62. AND mail_cont.Defecto = 1
  63. LEFT OUTER JOIN direcciones AS dir_resp ON responsable.IDContacto = dir_resp.idref
  64. AND dir_resp.Defecto = 1
  65. LEFT OUTER JOIN telefonos AS telresp ON responsable.IDContacto = telresp.IDref2
  66. AND telresp.Tipo = 3
  67. AND telresp.Defecto = 1
  68. LEFT OUTER JOIN comercial AS com ON req.RecID = com.idref
  69. LEFT OUTER JOIN comercialtransportes AS com_t ON com.RecID = com_t.idref
  70. LEFT OUTER JOIN empresas AS emp_t ON com_t.IDEmpresa = emp_t.IDEmpresa
  71. LEFT OUTER JOIN fiscal AS fis_t ON emp_t.IDEmpresa = fis_t.IDref
  72. AND fis_t.Defecto = 1
  73. LEFT OUTER JOIN adicional AS adi ON req.RecID = adi.IDRef
  74. LEFT OUTER JOIN telefonos AS tel ON dir_emp_sist.RecID = tel.IDDireccion
  75. AND tel.Tipo = 5
  76. AND tel.Defecto = 1
  77. LEFT OUTER JOIN telefonos AS fax ON dir_emp_sist.RecID = fax.IDDireccion
  78. AND fax.Tipo = 7
  79. AND fax.Defecto = 1
  80. LEFT OUTER JOIN direccionescorreo AS mail_emp ON emp_sist.IDEmpresa = mail_emp.IDref
  81. AND mail_emp.Defecto = 1
  82. LEFT OUTER JOIN
  83.   (SELECT IDRequerimiento
  84.    FROM pedidos AS ped
  85.    GROUP BY IDRequerimiento) AS ped_1 ON req.RecID = ped_1.IDRequerimiento
  86. LEFT OUTER JOIN
  87.   (SELECT RecID
  88.    FROM pedidos AS ped2
  89.    GROUP BY RecID) AS ped2_1 ON req.IDPedido = ped2_1.RecID
  90. LEFT OUTER JOIN requerimientositemsclientes AS req_dp_cli ON req_dp.RecID = req_dp_cli.IDReqProd
  91. LEFT OUTER JOIN pedidositems AS ped_dp ON req_dp_cli.IDPedProd = ped_dp.RecID
  92. LEFT OUTER JOIN pedidos AS ped3 ON ped_dp.IDPedido = ped3.RecID
  93. WHERE (req.RecID = '{?ID}')
  94. ORDER BY req_dp.NroFila

解决方法

从前两个表开始并将它们连接在一起 – 
  1. requerimientos AS req INNER JOIN
  2. requerimientositems AS req_dp ON req.RecID = req_dp.IDRequerimiento'

如果可行的话(写一个涉及两个表的查询[即测试]并确保它没问题)添加下一个 – 

  1. requerimientos AS req INNER JOIN
  2. requerimientositems AS req_dp ON req.RecID = req_dp.IDRequerimiento INNER JOIN 
  3. moneda AS mon ON req.NroMoneda = mon.Numero INNER JOIN
  4. contactos AS cont ON req.IDRef = cont.IDContacto

并重复.

这可能需要一些努力,但它是一种敏捷的方法,将极大地帮助您前进.
‘特别是那种意大利面;)

如果这样做和/或你想尝试更大的跳跃,试着删除那些子选择,看看是否是问题：
去掉：

  1. LEFT OUTER JOIN
  2.   (SELECT IDRequerimiento
  3.    FROM pedidos AS ped
  4.    GROUP BY IDRequerimiento) AS ped_1 ON req.RecID = ped_1.IDRequerimiento
  5. LEFT OUTER JOIN
  6.   (SELECT RecID
  7.    FROM pedidos AS ped2
  8.    GROUP BY RecID) AS ped2_1 ON req.IDPedido = ped2_1.RecID

最后,根据实际的错误通知,请看这里：!!!

  1. AND telcont.Tipo = 3
  2. AND telcont.Defecto = 1 ON faxcont.IDDireccion = dir_cont.RecID
  3. AND faxcont.Tipo = 2

那个ON没有表格？

猜你在找的MsSQL相关文章

SQL Server2012高可用之日志传送测试
（一）日志传送架构 （1.1）相关服务器 主服务器 ：用于生产的服务器，上面运行这生产SQL ...
SQL server事物复制报错：要复制的 LOB 数据的长度(xxxxx)超出了配置的最大值 65536
(一)事故背景 最近在SQL Server 2012生产数据库上配置完事物复制（发布订阅）后，生产数据...
SQL Server2012高可用之事物复制(发布订阅)测试
（一）测试目的 目前公司使用的SQL SERVER 2012高可用环境为主备模式，其中主库可执行读写...
使用zabbix监控sql server的发布订阅
（一）背景个人在使用sql server时，用到了sql server的发布订阅来做主从同步，类似MySQL的...
SQL学习（四）集合运算
UNION和OR谓词 找出 product 和 product2 中售价高于 500 的商品的基本信息. select * fro...
SQL学习（五） 高级处理
5.1 请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。 SEL...
SQL学习(一）初识数据库
datawhale组队学习SQL task01
SQL学习(二）基础查询与排序
datawhale数据库组队学习第二天打卡
SQL Server 2005 返回修改后的数据
今天在公司看到同事写了个SQL2005的新特性的文章，觉得很实用，在这里和大家分享下。原著作...
SQL Server 数据库最小宕机迁移方案
一、目的 在做SQL Server数据库维护的时候，当上司要求我们把几十G的数据文件搬动到其它服...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问