mysql - 如何查询 2 个 SQL 表并使用 1 个查询更改数据

Question

我在查询 2 个表并更改这 2 个表上的数据时遇到困难。

这就是我想做的。

获取表B“最大数量”-表A(“实际数量”-“在制品数量”)=表B“充值数量”

然后将“Signal”值更改为 NULL 或基于 ((“实际数量” - “WIP 数量”)/“最大数量”) x 100% 的 1-4 图例

图例

NULL = 大于或等于 60%

1 = 小于或等于 60%

2 = 小于或等于 50%

3 = 小于或等于 30%

4 = 小于或等于 0%

从此:

TABLE A
- Products        Actual Qty    WIP QTY
- Product A        5            0
- Product B        5            0
- Product C        733          72
- Product D        532          90
- Product E        510          360


TABLE B     
- MIN QTY   MAX QTY TOPUP QTY   SIGNAL
- 100       1000    NULL        NULL
- 329       857     NULL        NULL
- 393       1025    NULL        NULL
- 262       683     NULL        NULL
- 319       832     NULL        NULL

变成这样:

TABLE A
- Products        Actual Qty    WIP QTY
- Product A        5            0
- Product B        5            0
- Product C        733          72
- Product D        532          90
- Product E        510          360


TABLE B     
- MIN QTY   MAX QTY TOPUP QTY   SIGNAL
- 100       1000    995         4
- 329       857     852         4
- 393       1025    220         NULL
- 262       683     61          NULL
- 319       832     680         4

Answer 1

我假设 TableB 与 TableA 有某种关系

如果它们之间没有办法关联，这是不可能的。

DECLARE @tableA TABLE(Products VARCHAR(50), Actual_Qty INT, WIP_Qty INT)
DECLARE @tableB TABLE(Products VARCHAR(50), Min_Qty INT, Max_Qty INT, Topup_Qty INT, Signal INT)


INSERT INTO @tableA VALUES 
('Product A',5, 0),
('Product B', 5, 0),
('Product C', 733 , 72),
('Product D', 532 , 90),
('Product E', 510 , 360)

INSERT INTO @tableB VALUES
('Product A',100, 1000,995, 4),
('Product B',329, 857 ,852, 4),
('Product C',393, 1025,220, NULL),
('Product D',262, 683 ,61 , NULL),
('Product E',319, 832 ,680, 4)


SELECT A.Products,A.Actual_Qty,A.WIP_Qty,A.Actual_Qty-A.WIP_Qty Topup,'>' [ ],B.Products,B.Min_Qty,B.Max_Qty,B.Topup_Qty,B.Signal, 'now calculate > ' [ ]
, CONVERT(MONEY,(A.Actual_Qty-A.WIP_Qty )*100)/B.Max_Qty SignalTest
, CASE 
    WHEN CONVERT(MONEY,(A.Actual_Qty-A.WIP_Qty )*100)/B.Max_Qty >= 60 THEN 1
    WHEN CONVERT(MONEY,(A.Actual_Qty-A.WIP_Qty )*100)/B.Max_Qty BETWEEN 50 AND 60 THEN 2
    WHEN CONVERT(MONEY,(A.Actual_Qty-A.WIP_Qty )*100)/B.Max_Qty BETWEEN 30 AND 50 THEN 3
    WHEN CONVERT(MONEY,(A.Actual_Qty-A.WIP_Qty )*100)/B.Max_Qty < 30 THEN 4 
    END Signal

FROM @tableA A
INNER JOIN @tableB B ON B.Products = A.Products

/*Here is the update but, you can switch the tables for your tables*/
UPDATE B
SET B.Signal = (SELECT CASE 
    WHEN CONVERT(MONEY,(A.Actual_Qty-A.WIP_Qty )*100)/B.Max_Qty >= 60 THEN 1
    WHEN CONVERT(MONEY,(A.Actual_Qty-A.WIP_Qty )*100)/B.Max_Qty BETWEEN 50 AND 60 THEN 2
    WHEN CONVERT(MONEY,(A.Actual_Qty-A.WIP_Qty )*100)/B.Max_Qty BETWEEN 30 AND 50 THEN 3
    WHEN CONVERT(MONEY,(A.Actual_Qty-A.WIP_Qty )*100)/B.Max_Qty < 30 THEN 4 
    END Signal)

FROM @tableA A
INNER JOIN @tableB B ON B.Products = A.Products

SELECT
*
FROM @tableB