有没有办法在SQL查询中满足特定条件时只返回某些值？
有没有办法在SQL查询中满足特定条件时只返回某些值？
提问于 2020-08-20 00:23:48
我在试着解决一个商业问题。数据库具有由仓库和物料组分配的佣金费率。但是，有时我们会按仓库、物料组和客户分配佣金。在大多数情况下，我只需要返回仓库和物料组的佣金，但当有客户分配到特定的佣金率时，我希望返回该值，而不是标准(仓库和物料组)。现在，我的查询返回两个佣金值，一个用于(仓库和物料组)，另一个用于(仓库、物料组和客户)。理想情况下，我希望客户组覆盖仓库/物料组。
下面是我得到的输出示例：
| BizProp | WhsCode | ItmsGrpCod | ItemCode | CardCode | U_CardCode | Qty | TotSale | CommRate | Commission |
1 | 18 | 01 | 101 | 10034 | C10187 | NULL | 856 | 1964.61 | 0.0300 | 25.68 |
2 | 18 | 01 | 101 | 10034 | C10291 | NULL | 900 | 2088.00 | 0.0300 | 27.00 |
3 | 18 | 01 | 101 | 10034 | C10541 | NULL | 405 | 991.85 | 0.0300 | 12.15 |
4 | 18 | 01 | 101 | 10034 | C10541 | C10541 | 405 | 991.85 | 0.0050 | 2.025 |
5 | 18 | 01 | 102 | 10001 | C10187 | NULL | 335 | 852.44 | 0.0300 | 10.05 |
6 | 18 | 01 | 104 | 10052 | C10291 | NULL | 5 | 51.25 | 0.2500 | 1.25 |我希望查询只返回第4行，因为它对该客户有特殊的佣金率，而不是第3行和第4行都有。这在SQL中是可能的吗？或者需要在excel或其他工具中进行一些数据转换？
下面是我用来从临时表中提取数据、运行计算并对数据进行分组的CTE代码。我可以添加什么逻辑来实现上述结果吗？
;WITH SalesCommission_CTE AS
(
SELECT
BizProp,
WhsCode,
ItmsGrpCod,
ItemCode,
CardCode,
U_CardCode,
SUM(Quantity) AS Quantity,
SUM(Price) AS Price,
MIN(U_CommRate) AS CommRate,
COALESCE(SUM(Quantity * Price),NULL) AS TotSales, --Calc total sale amount per whs,itmgrp,cardcode
COALESCE(SUM(Quantity * U_CommRate),NULL) AS Commission --Calc commission rate per qty sold
FROM #TmpCustSaleRpt AS tcsr
GROUP BY
BizProp,
WhsCode,
ItmsGrpCod,
ItemCode,
CardCode
,U_CardCode
)
SELECT
BizProp,
WhsCode,
ItmsGrpCod,
ItemCode,
CardCode,
U_CardCode,
Quantity,
TotSales,
CommRate,
Commission
FROM SalesCommission_CTE
WHERE ItmsGrpCod NOT IN (109, 110, 111, 112, 114, 115, 116, 117, 118)
ORDER BY
BizProp,
WhsCode,
ItmsGrpCod,
ItemCode,
CardCode
,U_CardCode回答 1
Stack Overflow用户
发布于 2020-08-20 00:53:42
我认为它应该可以与密集的排名在Oracle.If中可能，您可以提供样本数据以及。
;WITH SalesCommission_CTE AS
(
SELECT
BizProp,
WhsCode,
ItmsGrpCod,
ItemCode,
CardCode,
U_CardCode,
SUM(Quantity) AS Quantity,
SUM(Price) AS Price,
MIN(U_CommRate) AS CommRate,
COALESCE(SUM(Quantity * Price),NULL) AS TotSales, --Calc total sale amount per whs,itmgrp,cardcode
COALESCE(SUM(Quantity * U_CommRate),NULL) AS Commission --Calc commission rate per qty sold
FROM #TmpCustSaleRpt AS tcsr
GROUP BY
BizProp,
WhsCode,
ItmsGrpCod,
ItemCode,
CardCode
,U_CardCode
)
SELECT
BizProp,
WhsCode,
ItmsGrpCod,
ItemCode,
CardCode,
U_CardCode,
Quantity,
TotSales,
CommRate,
max(Commission) keep (dense_rank first order by U_CardCode desc null last) over (partition by CardCode ) commission
FROM SalesCommission_CTE
WHERE ItmsGrpCod NOT IN (109, 110, 111, 112, 114, 115, 116, 117, 118)
ORDER BY
BizProp,
WhsCode,
ItmsGrpCod,
ItemCode,
CardCode
,U_CardCode页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/63491190
复制相关文章
相似问题
腾讯云开发者
