打开Navicat👉查询👉粘贴SQL语句👉运行👉导出👉Excel
博浩商贸通
商品表
SET @has_precio_mayor = (SELECT COUNT(*) FROM articulo WHERE PrecioMayor > 0); SET @has_descuento = (SELECT COUNT(*) FROM articulo WHERE Descuento > 0); SET @has_cantidad_unidad2 = (SELECT COUNT(*) FROM articulo WHERE CantidadPorUnidad2 > 1); SET @has_cantidad_unidad = (SELECT COUNT(*) FROM articulo WHERE CantidadPorUnidad > 1); SET @has_minstock = (SELECT COUNT(*) FROM articulo WHERE MiniStock > 0); SET @has_observacion = (SELECT COUNT(*) FROM articulo WHERE Observacion IS NOT NULL AND TRIM(Observacion) != '' AND TRIM(Observacion) NOT IN ('0', '-1', 'N/A')); SELECT p.ArticuloID AS 编号, p.CodigoBarra AS 条码, CASE WHEN p.DescuentoCambioProhibido = -1 THEN CONCAT('*', CAST(CAST(p.NombreES AS BINARY) AS CHAR CHARACTER SET gbk)) ELSE CAST(CAST(p.NombreES AS BINARY) AS CHAR CHARACTER SET gbk) END AS 商品名称, p.PrecioCoste AS 进货价, CASE WHEN p.PrecioDetalle != 0 THEN p.PrecioDetalle WHEN p.PrecioDetalle = 0 AND p.PrecioMayor != 0 THEN p.PrecioMayor WHEN p.PrecioDetalle = 0 AND p.PrecioMayor = 0 THEN NULL END AS 售价, CASE WHEN @has_cantidad_unidad2 = 0 OR p.CantidadPorUnidad2 IS NULL OR p.CantidadPorUnidad2 = 0 THEN NULL ELSE p.CantidadPorUnidad2 END AS 箱, CASE WHEN @has_cantidad_unidad = 0 OR p.CantidadPorUnidad IS NULL OR p.CantidadPorUnidad = 0 THEN NULL ELSE p.CantidadPorUnidad END AS 包, NULLIF(COALESCE(s.Stock, 0), 0) AS 数量, CASE WHEN @has_precio_mayor = 0 OR p.PrecioMayor IS NULL OR p.PrecioMayor = 0 THEN NULL WHEN p.PrecioMayor > 0 THEN p.PrecioMayor END AS 售价2, CASE WHEN @has_descuento = 0 OR p.Descuento IS NULL OR p.Descuento = 0 THEN NULL WHEN p.Descuento > 0 THEN p.Descuento END AS 折扣, CASE WHEN @has_observacion = 0 OR p.Observacion IS NULL OR TRIM(p.Observacion) = '' OR TRIM(p.Observacion) IN ('0', '-1', 'N/A') THEN NULL ELSE REPLACE(REPLACE(REPLACE(REPLACE(CAST(CAST(p.Observacion AS BINARY) AS CHAR CHARACTER SET gbk), CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' '), '\n', ' ') END AS 备注, NULL AS 售价3, NULL AS 售价4, i.IVA/100 AS 税率, NULL AS 类别ID, NULL as 供应商ID, NULLIF(CAST(CAST(p.NombreCN AS BINARY) AS CHAR CHARACTER SET gbk), '') AS 名称2, NULL AS 父类, NULL AS 存放位置, NULL AS 图片路径, NULL AS 库位, NULL AS 长, NULL AS 宽, NULL AS 高, NULL AS 重, NULL AS 过期日, NULL AS 活动, NULL AS 产地标签, NULL AS 品质标签, NULL AS 重量标签, NULL AS 预留, CASE WHEN @has_minstock = 0 OR p.MiniStock IS NULL OR p.MiniStock = 0 THEN NULL ELSE p.MiniStock END AS 库存下限 FROM articulo p LEFT JOIN articuloclase c ON p.ClaseID = c.ClaseID LEFT JOIN (SELECT ArticuloID, SUM(Stock) AS Stock FROM stock WHERE ArticuloID != 0 GROUP BY ArticuloID HAVING SUM(Stock) != 0) s ON p.ArticuloID = s.ArticuloID LEFT JOIN ivatipo i ON c.IVAID = i.IVAID WHERE p.ArticuloID != '0';
注意：如果有商品备注 请在快速录入中显示列中勾选备注 然后才能导入备注 否则不能导入
客户表
SELECT c.NombreES, c.PersonaContacto,REPLACE(c.CIF, '-', '') AS CIF, c.Domicilio, c.Poblacion, c.Provincia, c.CodigoPostal, COALESCE(NULLIF(CONCAT_WS(' ', NULLIF(TRIM(c.Telefono), ''), NULLIF(TRIM(c.Telefono2), '')), ''), c.ClienteID) AS Telefono, C.Fax as 传真, C.Descuento as 折扣, null as VIP号码, c.Observacion as 备注, null as 价格方式, null as 业务员, null as 税号2, CASE WHEN c.PrecioConIVA = -1 THEN 2 WHEN c.PrecioConIVA = 0 THEN IF(c.SinIVA = -1, 3, 1) END AS 计税方式, CASE WHEN c.REQ = -1 THEN 1 ELSE c.REQ END AS 附加税, c.Email FROM `cliente` c;
供应商
SELECT NombreES,PersonaContacto,CONCAT_WS(' ', NULLIF(TRIM(Email), ''), NULLIF(TRIM(Web), '')) AS `Email/Web`,Domicilio,Poblacion,Provincia,CodigoPostal,COALESCE(NULLIF(CONCAT_WS(' ', NULLIF(TRIM(Telefono), ''), NULLIF(TRIM(Telefono2), '')), ''), ProveedorID) AS Telefono,Fax,Descuento,Observacion FROM proveedor;
———————————————————————————————-
同创软件商贸通
商品表
SELECT p.tm, NULL, p.na, p.jj, p.lsj, NULL, NULL, s.yl, NULL, NULL, NULL, NULL, NULL, p.tr / 100 FROM [dbo].[t_item_info] AS p LEFT JOIN [dbo].[t_item_storage] AS s ON p.sx# = s.ks# WHERE p.sx# != 10010001;
客户表
SELECT c.mc, c.lxr, c.dz, NULL AS 城市, NULL AS 省份, c.yb, c.ltfb, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CASE WHEN c.is_settax = 1 AND c.cus_tax = 0 THEN 3 WHEN c.is_tax = 0 AND c.cus_tax != 0 THEN 1 WHEN c.is_tax = 1 THEN 2 END AS 计税方式, CASE WHEN c.per_taxrate = 1.00 THEN 1 ELSE 0 END AS 附加税 FROM [dbo].[t_sys_sup_info] AS c WHERE c.lb = 101;
供应商
SELECT c.mc, c.lxr,NULL, c.dz, NULL AS 城市, NULL AS 省份, c.yb, c.ltfb,c.cz FROM [dbo].[t_sys_sup_info] AS c WHERE c.lb = 1;
注意：同创软件客人和供应商地址包含城市和省份 没有规则 没有办法分开到独立列中
叶小站商贸
商品表
SELECT *
客户表
SELECT *
供应商
SELECT *