php - 编辑: getting null value in a join statement if the value does not exist

Question

编辑:非常感谢您的帮助，但我可能没有大胆地讨论有关 NULL 值的问题。实际上我希望它也考虑空值。因为无法对空值进行计算。如果它不退出，我希望它在价格列之前添加空值。有没有办法这样做？谢谢你，再次抱歉。不知道我可能会遇到这个问题。

╔══════════╤════════════╤═══════════╤══════════════╗
║ store_id │ product_id │ now_price │ before_price ║
╠══════════╪════════════╪═══════════╪══════════════╣
║ 1        │ 2          │ 20000     │ NULL         ║
╟──────────┼────────────┼───────────┼──────────────╢
║ 2        │ 2          │ 18000     │ 21000        ║
╟──────────┼────────────┼───────────┼──────────────╢
║ 3        │ 2          │ 10000     │ NULL         ║
╚══════════╧════════════╧═══════════╧══════════════╝

I've got two tables for prices one of them is for now and one of them is for before. the table structures are like this:

now :

╔══════════╤════════════╤═══════╗
║ store_id │ product_id │ price ║
╠══════════╪════════════╪═══════╣
║ 1        │ 2          │ 20000 ║
╟──────────┼────────────┼───────╢
║ 2        │ 2          │ 30000 ║
╟──────────┼────────────┼───────╢
║ 3        │ 2          │ 25000 ║
╚══════════╧════════════╧═══════╝

before :

╔══════════╤════════════╤═══════╗
║ store_id │ product_id │ price ║
╠══════════╪════════════╪═══════╣
║ 1        │ 2          │ 19800 ║
╟──────────┼────────────┼───────╢
║ 2        │ 2          │ 28000 ║
╟──────────┼────────────┼───────╢
║ 3        │ 2          │ 24300 ║
╚══════════╧════════════╧═══════╝

now I want to have the the difference between the data in a table like this :

╔══════════╤════════════╤════════════╗
║ store_id │ product_id │ difference ║
╠══════════╪════════════╪════════════╣
║ 1        │ 2          │ -200       ║
╟──────────┼────────────┼────────────╢
║ 2        │ 2          │ -2000      ║
╟──────────┼────────────┼────────────╢
║ 3        │ 2          │ -700       ║
╚══════════╧════════════╧════════════╝

How can I do that? Thank you. and btw there maybe a product unavailable in a store is it possible to maybe get a null value for them?

Answer 1

内连接会很方便。

SELECT T1.`store_id`,
       T2.`product_id`,
      (T1.`price`-T2.`price`) AS Difference
    FROM
    price_now T2
    INNER JOIN 
    price_before T1 ON T1.`store_id` = T2.`store_id` AND T1.`product_id` = T2.`product_id`

希望这有帮助。