打开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, NULL, i.IVA/100 AS 税率, NULL, NULL, NULLIF(CAST(CAST(p.NombreCN AS BINARY) AS CHAR CHARACTER SET gbk), '') AS 名称2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 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 *