我试图通过将原始表中的值与另一个表中的最小值和最大值进行比较来获取指定点的列。我有一个主表,其中包含几列值和要插入的相应点列。这是示例:
Main table
--------------------------------------------------------------------------------
| User | Department | BU | Revenue | Revenuepoint | Profit | Profitpoint |
--------------------------------------------------------------------------------
| A | 1000000 | 101 | 400 | | 200 | |
| B | 1000001 | 101 | 300 | | 100 | |
| C | 1000000 | 102 | 350 | | 150 | |
--------------------------------------------------------------------------------
Point table
----------------------------------------------------------------
| Category| Department | BU | Fromvalue | Tovalue | Point |
----------------------------------------------------------------
| Revenue | 1000000 | 101 | 0 | 200 | 1 |
| Revenue | 1000000 | 101 | 201 | 400 | 2 |
| Revenue | 1000000 | 102 | 0 | 300 | 1 |
| Revenue | 1000000 | 102 | 301 | 400 | 2 |
| Revenue | 1000001 | 101 | 0 | 200 | 1 |
| Revenue | 1000001 | 101 | 201 | 400 | 2 |
| Profit | 1000000 | 101 | 0 | 100 | 1 |
| Profit | 1000000 | 101 | 101 | 300 | 2 |
| Profit | 1000000 | 102 | 0 | 50 | 1 |
| Profit | 1000000 | 102 | 51 | 200 | 2 |
| Profit | 1000001 | 101 | 0 | 50 | 1 |
| Profit | 1000001 | 101 | 51 | 200 | 2 |
----------------------------------------------------------------
Expected table
--------------------------------------------------------------------------------
| User | Department | BU | Revenue | Revenuepoint | Profit | Profitpoint |
--------------------------------------------------------------------------------
| A | 1000000 | 101 | 400 | 2 | 200 | 2 |
| B | 1000001 | 101 | 300 | 2 | 40 | 1 |
| C | 1000000 | 102 | 350 | 2 | 150 | 2 |
--------------------------------------------------------------------------------
部门和 BU 需要匹配,RevenuePoint 需要获取类别 = Revenue 的记录,与 ProfitPoint 相同。
我尝试了在这里找到的几种方法,但大多数方法不像我的那么复杂,所以我不知道如何从这里转移。
这是我尝试过的代码
SELECT
a.User,
a.Department,
a.BU,
a.Revenue,
(SELECT MAX(b.Point) from Point b WHERE a.Revenue BETWEEN b.fromvalue and b.tovalue) as Revenuepoint,
b.Point as Revenuepoint,
a.Profit,
(SELECT MAX(b.Point) from Point b WHERE a.Profit BETWEEN b.fromvalue and b.tovalue) as Profitpoint
FROM Sales a