sql - 使用 SQL 查找客户订购产品的总次数

问题描述

我需要修改示例数据库的结构，以便在修改后可以存储有关客户订购每种产品的总次数的信息。

请注意，有些产品可能根本没有订购。找到最好的设计很重要。我需要强制执行适当的一致性约束。

此后，脚本将有关客户订购每种产品的总次数的信息保存在示例数据库中。

我正在使用 oracle SQL 开发人员。

这是我到目前为止所尝试的：

CREATE TABLE TOTAL_NUM(
CUSTOMER_CODE   VARCHAR(30) NOT NULL, 
PRODUCT_NAME    VARCHAR(30) NOT NULL,
TOTAL_ORDERED   DECIMAL(2)  NOT NULL,
CONSTRAINT TOTALO_PKEY1 PRIMARY KEY(CUSTOMER_CODE),
CONSTRAINT TOTALO_FKEY1 FOREIGN KEY(CUSTOMER_CODE) REFERENCES CUSTOMER(CUSTOMER_CODE),
CONSTRAINT TOTALO_UNIQUE_NAME UNIQUE(PRODUCT_NAME), 
CONSTRAINT TOTALO_FKEY2 FOREIGN KEY(PRODUCT_NAME) REFERENCES PRODUCT(PRODUCT_NAME),
CONSTRAINT TOTALO_CHECK CHECK(TOTAL_ORDERED > 0));

INSERT INTO TOTAL_NUM(
SELECT ORDERS.CUSTOMER_CODE, COUNT(*) AS TOTAL_ORDERED
FROM ORDERS
LEFT JOIN ORDER_DETAIL ON ORDERS.ORDER_ID = ORDER_DETAIL.ORDER_ID);

我创建了一个单独的表来存储此信息，但是当我尝试将值插入表中时，我收到一个错误“没有足够的值”。

以下是数据库中的其他表：

CREATE TABLE CATEGORY
(
CATEGORY_NAME   VARCHAR(30) NOT NULL,
DESCRIPTION     VARCHAR(2000)   NOT NULL,
PICTURE         VARCHAR(255)    NOT NULL,
CONSTRAINT PK_CATEGORY PRIMARY KEY (CATEGORY_NAME)
);

CREATE TABLE CUSTOMER
(
CUSTOMER_CODE   VARCHAR(5)  NOT NULL,
COMPANY_NAME    VARCHAR(40) NOT NULL,
CONTACT_NAME    VARCHAR(30)     NOT NULL,      
CONTACT_TITLE   VARCHAR(30)     NOT NULL,
ADDRESS         VARCHAR(60)     NOT NULL,
CITY        VARCHAR(15)     NOT NULL,
REGION      VARCHAR(15)         NULL,
POSTAL_CODE     VARCHAR(10)         NULL,
COUNTRY         VARCHAR(15)     NOT NULL,
PHONE       VARCHAR(24)     NOT NULL,
FAX         VARCHAR(24)         NULL,
CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUSTOMER_CODE)
);
        
CREATE TABLE EMPLOYEE
(
EMPLOYEE_ID     NUMBER(9)   NOT NULL,
LASTNAME        VARCHAR(20)     NOT NULL,
FIRSTNAME       VARCHAR(10)     NOT NULL,
TITLE       VARCHAR(30)     NOT NULL,
TITLE_OF_COURTESY   VARCHAR(25)     NOT NULL,
BIRTHDATE       DATE            NOT NULL,
HIREDATE        DATE            NOT NULL,
ADDRESS         VARCHAR(60)     NOT NULL,
CITY        VARCHAR(15)     NOT NULL,
REGION      VARCHAR(15)         NULL,
POSTAL_CODE     VARCHAR(10)     NOT NULL,
COUNTRY         VARCHAR(15)     NOT NULL,
HOME_PHONE      VARCHAR(24)     NOT NULL,
EXTENSION       VARCHAR(4)      NOT NULL,
PHOTO       VARCHAR(255)    NOT NULL,
NOTES       VARCHAR(2000)   NOT NULL,
REPORTS_TO      NUMBER(9)           NULL,
CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEE_ID)
);

CREATE TABLE SUPPLIER
(
COMPANY_NAME    VARCHAR(40) NOT NULL,
CONTACT_NAME    VARCHAR(30)     NOT NULL,
CONTACT_TITLE   VARCHAR(30)     NOT NULL,
ADDRESS         VARCHAR(60)     NOT NULL,
CITY        VARCHAR(15)     NOT NULL,
REGION      VARCHAR(15)         NULL,
POSTAL_CODE     VARCHAR(10)     NOT NULL,
COUNTRY         VARCHAR(15)     NOT NULL,
PHONE       VARCHAR(24)     NOT NULL,
FAX         VARCHAR(24)         NULL,
HOME_PAGE       VARCHAR(500)        NULL,
CONSTRAINT PK_SUPPLIER PRIMARY KEY (COMPANY_NAME)  
);

CREATE TABLE SHIPPER
(
COMPANY_NAME    VARCHAR(40) NOT NULL,
PHONE       VARCHAR(24)         NULL,
CONSTRAINT PK_SHIPPER PRIMARY KEY (COMPANY_NAME),
CONSTRAINT CK_SHIPPER UNIQUE (PHONE)
);

CREATE TABLE PRODUCT
(
PRODUCT_NAME    VARCHAR(40)     NOT NULL,
SUPPLIER_NAME   VARCHAR(40)     NOT NULL,
CATEGORY_NAME   VARCHAR(30) NOT NULL,
QUANTITY_PER_UNIT   VARCHAR(20)         NULL,
UNIT_PRICE      NUMBER(10,2)    DEFAULT 0,
UNITS_IN_STOCK  NUMBER(9)   DEFAULT 0,
UNITS_ON_ORDER  NUMBER(9)   DEFAULT 0,
REORDER_LEVEL   NUMBER(9)   DEFAULT 0,
DISCONTINUED    CHAR(1)     DEFAULT 'N',
CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_NAME),
CONSTRAINT FK_CATEGORY_NAME FOREIGN KEY (CATEGORY_NAME) REFERENCES CATEGORY(CATEGORY_NAME),
CONSTRAINT FK_SUPPLIER_NAME FOREIGN KEY (SUPPLIER_NAME) REFERENCES SUPPLIER(COMPANY_NAME),
CONSTRAINT CK_PRODUCT_UNIT_PRICE CHECK (UNIT_PRICE >= 0),
CONSTRAINT CK_PRODUCT_UNITS_IN_STOCK CHECK (UNITS_IN_STOCK >= 0),
CONSTRAINT CK_PRODUCT_UNITS_ON_ORDER CHECK (UNITS_ON_ORDER >= 0),
CONSTRAINT CK_PRODUCT_REORDER_LEVEL CHECK (REORDER_LEVEL >= 0),
CONSTRAINT CK_PRODUCT_DISCONTINUED CHECK (DISCONTINUED in ('Y','N'))
);

CREATE TABLE ORDERS
(
ORDER_ID        NUMBER(9)   NOT NULL,
CUSTOMER_CODE   VARCHAR(5)  NOT NULL,
EMPLOYEE_ID     NUMBER(9)   NOT NULL,
ORDER_DATE      DATE        NOT NULL,
REQUIRED_DATE   DATE            NOT NULL,
SHIPPED_DATE    DATE            NOT NULL,
SHIP_VIA        VARCHAR(40)     NOT NULL,
FREIGHT         NUMBER(10,2)    DEFAULT 0,
SHIP_NAME       VARCHAR(40)     NOT NULL,
SHIP_ADDRESS    VARCHAR(60)     NOT NULL,
SHIP_CITY       VARCHAR(15)     NOT NULL,
SHIP_REGION     VARCHAR(15)         NULL,
SHIP_POSTAL_CODE    VARCHAR(10)         NULL,
SHIP_COUNTRY    VARCHAR(15)     NOT NULL,
CONSTRAINT PK_ORDERS PRIMARY KEY (ORDER_ID),
CONSTRAINT FK_CUSTOMER_CODE FOREIGN KEY (CUSTOMER_CODE) REFERENCES CUSTOMER(CUSTOMER_CODE),  
CONSTRAINT FK_EMPLOYEE_ID FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID),  
CONSTRAINT FK_SHIP_VIA FOREIGN KEY (SHIP_VIA) REFERENCES SHIPPER(COMPANY_NAME)  
);

CREATE TABLE ORDER_DETAIL
(
ORDER_ID         NUMBER(9)  NOT NULL,
PRODUCT_NAME         VARCHAR(40)    NOT NULL,
UNIT_PRICE       NUMBER(10,2)   DEFAULT 0.0,
QUANTITY         NUMBER(9)  DEFAULT 1,
DISCOUNT         NUMBER(4,2)    DEFAULT 0.0,
CONSTRAINT PK_ORDER_DETAIL PRIMARY KEY (ORDER_ID, PRODUCT_NAME),
CONSTRAINT FK_ORDER_ID FOREIGN KEY (ORDER_ID) REFERENCES ORDERS (ORDER_ID),
CONSTRAINT FK_PRODUCT_NAME FOREIGN KEY (PRODUCT_NAME) REFERENCES PRODUCT (PRODUCT_NAME),
CONSTRAINT CK_ORDER_DETAIL_UNIT_PRICE CHECK (UNIT_PRICE >= 0), 
CONSTRAINT CK_ORDER_DETAIL_QUANTITY CHECK (QUANTITY > 0),
CONSTRAINT CK_ORDER_DETAIL_DISCOUNT CHECK (DISCOUNT between 0 and 1) 
);

标签： sqloracle

您只在中插入两列TOTAL_NUM，但它有三列。你跳过了PRODUCT_NAME。您需要使用GROUP BY，否则它会将所有订单聚合到一行中以插入。

INSERT INTO TOTAL_NUM (CUSTOMER_CODE, PRODUCT_NAME, TOTAL_ORDERED)
SELECT ORDERS.CUSTOMER_CODE, ORDER_DETAIL.PRODUCT_NAME, COUNT(*) AS TOTAL_ORDERED
FROM ORDERS
INNER JOIN ORDER_DETAIL ON ORDERS.ORDER_ID = ORDER_DETAIL.ORDER_ID
GROUP BY ORDERS.CUSTOMER_CODE ORDER_DETAIL.PRODUCT_NAME

您还应该使用INNER JOIN而不是LEFT JOIN. PRODUCT_NAME否则，您将为任何没有产品的订单创建一个空行。

sql - 使用 SQL 查找客户订购产品的总次数

问题描述

解决方案

推荐阅读