时间戳

首页 > 解决方案 > 如何在sql查询中匹配两个不同的表？

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

问题描述

我在下面有这两种表格：

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

我想做的是当orderqty大于stockqty我想显示一条消息说库存不足，当orderqty小于时stockqty它完成了。

这里主要关注的是识别orderqty是小于还是大于stockqty。

标签： mysqlsql

解决方案

请查看此 DB Fiddle 并让我知道它是否有帮助。

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;

推荐阅读