php - 比较两个表，从表中获取值并将其相乘 - PHP MySQL

Question

我是新手，需要有关 PHP MySQL 的帮助。

我有两个表:

表1:

Home        | Work    | Sport    |Buy YES|  BUY NO
_____________________
Television | Laptop  | shoes   |             |
DVD          |desk      |ball        |             |

---

Table2:

Items         | Buy    | Budget
_____________________
Television | YES   | 500
Television | NO     | 5
Laptop      | YES   | 400
Laptop      | NO     | 3
shoes       | YES   | 80
shoes       | NO     | 1
DVD         | YES   | 60
DVD         | NO     | 2
desk        | YES   | 700
desk        | NO     | 1
ball          | YES   | 20
ball          | NO     | 1

如何从“Table2”中获取值并将每个值相乘并将其存储在“Table1”的下一列中？

例如:

Buy YES = Television * Laptop * shoes = 500*400*80

Buy NO = Television * Laptop * shoes = 5 * 3 * 1

并对下一行执行同样的操作。我希望你明白我的意思。

感谢您的帮助。

Answer 1

你的数据库模式是一场噩梦，但你可以这样做

UPDATE table1 t LEFT JOIN table2 h_yes
    ON t.home = h_yes.items 
   AND h_yes.buy = 'YES' LEFT JOIN table2 h_no
    ON t.home = h_no.items 
   AND h_no.buy = 'NO' LEFT JOIN table2 w_yes
    ON t.work = w_yes.items 
   AND w_yes.buy = 'YES' LEFT JOIN table2 w_no
    ON t.work = w_no.items 
   AND w_no.buy = 'NO' LEFT JOIN table2 s_yes
    ON t.sport = s_yes.items 
   AND s_yes.buy = 'YES' LEFT JOIN table2 s_no
    ON t.sport = s_no.items 
   AND s_no.buy = 'NO'
   SET t.buy_yes = COALESCE(h_yes.budget, 1) 
                 * COALESCE(w_yes.budget, 1) 
                 * COALESCE(s_yes.budget, 1),
       t.buy_no = COALESCE(h_no.budget, 1) 
                 * COALESCE(w_no.budget, 1) 
                 * COALESCE(s_no.budget, 1);

结果:

|       HOME |   WORK | SPORT |  BUY_YES | BUY_NO ||------------|--------|-------|----------|--------|| Television | Laptop | shoes | 16000000 |     15 ||        DVD |   desk |  ball |   840000 |      2 |

这里是SQLFiddle 演示