打开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'));SET @has_precio_detalle_mayor = (SELECT MAX(CASE WHEN p2.PrecioDetalle IS NOT NULL AND p2.PrecioDetalle != 0 AND p2.PrecioMayor IS NOT NULL AND p2.PrecioMayor != 0 THEN 1 ELSE 0 END) FROM articulo p2 WHERE p2.ArticuloID != '0');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 THEN NULL WHEN @has_precio_detalle_mayor = 0 THEN NULL WHEN p.PrecioDetalle IS NOT NULL AND p.PrecioDetalle != 0 AND p.PrecioMayor IS NOT NULL AND p.PrecioMayor != 0 THEN p.PrecioMayor ELSE 0 END AS 售价2, CASE WHEN @has_descuento = 0 THEN NULL WHEN p.Descuento IS NULL OR p.Descuento = 0 THEN 0 WHEN p.Descuento > 0 THEN p.Descuento ELSE NULL 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';
注意：如果有商品备注 请在快速录入中显示列中勾选备注 然后才能导入备注 否则不能导入
