时间戳

首页 > 解决方案 > 表在小提琴上不存在，但实际上已经制作

mysql - 表在小提琴上不存在，但实际上已经制作

问题描述

这是我的小提琴：

CREATE TABLE order_match(ID int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
quantity decimal(10,2), createdAt date NOT NULL, order_status_id int(10) NOT NULL,
createdby int(11), code_order varchar(20) NOT NULL);

CREATE TABLE air_way_bills (id int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
code_order varchar(30) NOT NULL PRIMARY KEY, customer_regency varchar(30) NOT NULL);

我做了这个查询

SELECT COALESCE(customer_regency, 'Total') AS `Kabupaten/Kota`, 
       SUM(quantity) AS `Qty(kg)`,
       round(SUM(quantity) / any_value(totalsum) * 100, 1) AS `Qty(%)`, 
       COUNT(order_match.id) AS `Jumlah Order`,
       round(COUNT(order_match.id) / any_value(totalcount) * 100, 1) AS `Jumlah Order(%)`
FROM order_match a

/* 1 */ INNER JOIN air_way_bills b
/* 1 */ ON a.code_order = b.code_order
/* 2 */ INNER JOIN ( SELECT s1.createdby
               FROM order_match s1
               WHERE s1.order_status_Id in (4, 5, 6, 8)
               GROUP BY s1.createdby
               HAVING SUM(s1.createdAt BETWEEN '2020-02-01' AND '2020-02-28')
                  AND SUM(s1.createdAt <= '2020-02-28') = 1 ) clients 
/* 2 */ ON order_match.createdby = clients.createdby
JOIN ( SELECT SUM(quantity) totalsum, 
              COUNT(order_buyer_id) totalcount 
       FROM order_match
/* 3 */  INNER JOIN ( SELECT s2.createdby
                      FROM order_match s2
                      WHERE s2.order_status_id in (4, 5, 6, 8)
                      GROUP BY s2.createdby
                      HAVING SUM(s2.createdAt BETWEEN '2020-02-01' AND '2020-02-28')
                         AND SUM(s2.createdAt <= '2020-02-28') = 1 ) clients
/* 3 */ ON order_match.createdby = clients.createdby
       WHERE order_status_Id in (4, 5, 6, 8)) totals
WHERE order_match.order_status_Id in (4, 5, 6, 8)
GROUP BY customer_regency WITH ROLLUP;

而是出现了结果，小提琴带有此通知

Table 'db_712942043.air_way_bills' doesn't exist

不知道该怎么办

标签： mysql

解决方案

不，该表不存在，因为您定义了 2 个主键，这是不允许的，但小提琴中没有显示错误。
如果您在 2 个单独的空间中有 2 个创建语句，那么您会看到错误。从的定义中
删除，将创建表。 或者，如果您想要一个复合主键，您可以像这样定义它：PRIMARY KEYcode_order

CREATE TABLE air_way_bills (
  id int(10) NOT NULL AUTO_INCREMENT,
  code_order varchar(30) NOT NULL, 
  customer_regency varchar(30) NOT NULL,
  PRIMARY KEY (id, code_order)
);

请参阅演示。

推荐阅读