帆软用户PoN24uZLlw(提问者)SELECT ProductionNO, 国家, 销售数量, 销售金额
FROM (
SELECT
d.ProductionNO,
CASE
WHEN LEN(b.国家) > 0 THEN ISNULL(a.目的地类型, '') + '-' + ISNULL(a.目的地国家, '') + '-' + ISNULL(a.目的地代码, '') + '-' + ISNULL(b.国家, '')
ELSE ISNULL(a.目的地类型, '') + '-' + ISNULL(a.目的地国家, '') + '-' + ISNULL(a.目的地代码, '') + '-' + ISNULL(c.中文仓库, '')
END AS 国家,
SUM(a.销售数量) AS 销售数量,
SUM(ISNULL(CASE WHEN ${rfq} = 0 THEN d.Price WHEN ${rfq} = 1 THEN d.oldprice END, 0) / ${huilv} * a.销售数量) AS 销售金额,
ROW_NUMBER() OVER (PARTITION BY d.ProductionNO ORDER BY SUM(a.销售数量) DESC) AS rn
FROM dbo.tb_spi_sales a
LEFT JOIN tb_Spi_countryshoudan b ON a.目的地国家 = b.R_Cty
LEFT JOIN tb_Spi_country c ON a.目的地代码 = c.英文仓库
LEFT JOIN dbo.tb_spi_productionlist d ON a.宜家货号 = d.IkeaNo
WHERE
a.目的地类型 <> 'Summary'
AND a.销售周期 = (
SELECT TOP 1 销售周期 FROM dbo.tb_spi_sales ORDER BY 销售周期 DESC
)
AND (
(NULLIF('${productionno}','') IS NOT NULL AND d.ProductionNO = '${productionno}') OR
(NULLIF('${productionname}','') IS NOT NULL AND d.ProductionName LIKE '%${productionname}%') OR
(NULLIF('${category}','') IS NOT NULL AND d.Category = '${category}')
)
GROUP BY d.ProductionNO,
CASE
WHEN LEN(b.国家) > 0 THEN ISNULL(a.目的地类型, '') + '-' + ISNULL(a.目的地国家, '') + '-' + ISNULL(a.目的地代码, '') + '-' + ISNULL(b.国家, '')
ELSE ISNULL(a.目的地类型, '') + '-' + ISNULL(a.目的地国家, '') + '-' + ISNULL(a.目的地代码, '') + '-' + ISNULL(c.中文仓库, '')
END
) t
WHERE rn = 1
1min目标场景问卷
立即参与
