php - 如何在SQL查询中匹配两个不同的表？

我有以下两种桌子：

 Stocks Table          Order Table
-------------         -------------
| stockid   |         | orderid   |
| stockname |         | orderqty  |
| stockqty  |         | stockid   |
-------------         -------------

我想做的是当orderqty大于stockqty时，我想显示一条消息，说明库存不足，当orderqty小于stockqty时，就完成了。
这里的主要问题是确定orderqty是否小于或大于stockqty。

最佳答案

请看这个DB小提琴，如果有帮助请告诉我。
https://www.db-fiddle.com/f/hgEdnyeTTuxamXna9ELL3P/2

CREATE TABLE t_stock (
  stockid INT,
  stockname VARCHAR(50),
  stockqty BIGINT
);
INSERT INTO t_stock (stockid, stockname, stockqty) VALUES (1, 'salmon', 150);
INSERT INTO t_stock (stockid, stockname, stockqty) VALUES (2, 'asparagus', 275);
INSERT INTO t_stock (stockid, stockname, stockqty) VALUES (3, 'lemon', 300);


CREATE TABLE t_order (
  orderid INT,
  orderqty BIGINT,
  stockid INT
);
INSERT INTO t_order (orderid, orderqty, stockid) VALUES (13, 150, 3);
INSERT INTO t_order (orderid, orderqty, stockid) VALUES (14, 275, 2);
INSERT INTO t_order (orderid, orderqty, stockid) VALUES (15, 300, 1);


SELECT o.orderid
      ,o.stockid
      ,o.orderqty
      ,s.stockqty
      ,CONCAT(CASE WHEN o.orderqty > s.stockqty
                   THEN 'stocks are not enough for '
                   WHEN o.orderqty = s.stockqty
                   THEN 'stocks are just enough for '
                   ELSE 'we have plenty of stock for '
                   END, s.stockname) as stockqty_analysis
  FROM t_stock s INNER JOIN t_order o
    ON s.stockid = o.stockid;