正規化後，資料結構應該如何呢?
客戶資料表 customer(cusno, cusname, cuszipcode, cusaddress, custel, cusfax, cuscontact)
職員資料表 employee(empno, empname, deptno, emptitle)
公司部門表 department(deptno, deptname)
產品資料表 product(prodno, prodname, prodprice, prodamount)
訂單主檔資料表 myorder(ordno, cusno, orddate, ordtotal, salesno, assistno)
訂單品項資料表 myorderitem(ordno, serial, prodno, prodqty, prodprice)
以下的需求應該如何達成呢?
(1)某個客戶在某個日期，訂購那些商品?
SELECT p.prodno, p.prodname FROM product p, myorder m, myorderitem mi
WHERE m.ordno=mi.ordno
AND mi.prodno=p.prodno
AND m.cusno='某個客戶編號'
AND m.orddate='某個日期'
(2)某個客戶在某個日期，訂購總額多少?
SELECT SUM(ordtotal) FROM myorder
WHERE cusno='某個客戶編號'
AND m.orddate='某個日期'
如果要自己計算訂購總額
SELECT SUM(mi.prodqty*mi.prodprice) FROM myorderitem mi, myorder m
WHERE mi.ordno=m.ordno
AND m.cusno='某個客戶編號'
AND m.orddate='某個日期'
(3)某商品在某個月份的總銷售量是多少?
SELECT SUM(mi.prodprice*mi.prodqty) FROM myorderitem mi, myorder m
WHERE mi.ordno=m.ordno
AND substr(m.orddate,1,6)='某個月份'
AND mi.prodno='某個商品'
(4)某個月份的總銷售量最高的商品是什麼?
SELECT prodno, MAX(mi.prodprice*mi.prodqty) FROM myorderitem mi, myorder m
WHERE substr(m.orddate,1,6)='某個月份'
(5)某個月份的總銷售量最低的商品是什麼?
SELECT prodno, MIN(mi.prodprice*mi.prodqty) FROM myorderitem mi, myorder m
WHERE substr(m.orddate,1,6)='某個月份'
(6)計算某個月份內，各業務人員的業績?
SELECT m.salesno, SUM(m.ordtotal) FROM myorder m, myorderitem mi
WHERE substr(m.orddate,1,6)='某個月份'
GROUP BY m.salesno
(7)由電話去查詢某個月份，該客戶的訂購量?
SELECT SUM(ordtotal) FROM myorder m, customer c
WHERE c.custel='某電話'
AND m.cusno=c.cusno
AND substr(m.orddate,1,6)='某個月份'
(8)依客戶的地區別，統計各地區某個月份的銷售量?
SELECT SUM(ordtotal) FROM myorder m, customer c
WHERE m.cusno=c.cusno
AND substr(m.orddate,1,6)='某個月份'
GROUP BY c.cuszipcode
(9)依照產品名稱去查詢該產品某個月份的銷售量?
SELECT SUM(m.ordtotal) FROM myorder m, myorderitem mi, product p
WHERE m.ordno=mi.ordno
AND mi.prodno=p.prodno
AND substr(m.orddate,1,6)='某個月份'
AND p.prodname='產品名稱'
(10)統計各月份的銷售量?
SELECT substr(orddate,1,6) as d, SUM(ordtotal) FROM myorder
GROUP BY d
----
以下是建立跟插入資料的 SQL
mysql> create database myorder3
-> character set utf8;
Query OK, 1 row affected (0.03 sec)
mysql> use myorder3
Database changed
mysql> create table customer (
-> cusno char(5) not null,
-> cusname char(10),
-> cuszipcode char(3),
-> cusaddress varchar(50),
-> custel char(10),
-> cusfax char(10),
-> cuscontact char(10),
-> primary key (cusno));
Query OK, 0 rows affected (0.25 sec)
mysql> create table employee (
-> empno char(5) not null,
-> empname char(10),
-> deptno char(3),
-> emptitle char(10),
-> primary key (empno));
Query OK, 0 rows affected (0.19 sec)
mysql> create table department (
-> deptno char(3) not null,
-> deptname char(10),
-> primary key (deptno));
Query OK, 0 rows affected (0.17 sec)
mysql> create table product (
-> prodno char(3) not null,
-> prodname char(10),
-> prodprice int(4),
-> prodamount int(5),
-> primary key (prodno));
Query OK, 0 rows affected (0.19 sec)
mysql> create table myorder (
-> ordno char(5) not null,
-> cusno char(5) not null,
-> orddate char(8),
-> ordtotal int(10),
-> salesno char(5),
-> assistno char(5),
-> primary key (ordno));
Query OK, 0 rows affected (0.22 sec)
mysql> create table myorderitem (
-> ordno char(5) not null,
-> serial char(5) not null,
-> prodno char(3),
-> prodqty int(6),
-> prodprice int(10),
-> primary key (ordno,serial));
Query OK, 0 rows affected (0.20 sec)
mysql> insert into department (deptno, deptname) values ('001','SALES');
Query OK, 1 row affected (0.03 sec)
mysql> insert into department (deptno, deptname) values ('002','ENG');
Query OK, 1 row affected (0.00 sec)
mysql> insert into department (deptno, deptname) values ('003','MGM');
Query OK, 1 row affected (0.00 sec)
mysql> insert into employee (empno, empname,deptno,emptitle) values ('001','John
','001','Sales');
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee (empno, empname,deptno,emptitle) values ('002','Mary
','001','Sales');
Query OK, 1 row affected (0.00 sec)
mysql> insert into customer (cusno, cusname, cuszipcode, cusaddress, custel,cusf
ax, cuscontact) values ('001','ABC Co.','632','Yunlin','056310000','056312222','
Tim Doe');
Query OK, 1 row affected (0.00 sec)
mysql> insert into customer (cusno, cusname, cuszipcode, cusaddress, custel,cusf
ax, cuscontact) values ('002','CDE Co.','100','Taipei','0226310000','0256312222'
,'Helen Doris');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into product (prodno, prodname, prodprice,prodamount) values ('001
','Pencil',50,1000);
Query OK, 1 row affected (0.01 sec)
mysql> insert into product (prodno, prodname, prodprice,prodamount) values ('002
','Pen',150,1000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myorder (ordno, cusno, orddate, ordtotal, salesno, assistno)
values ('001','001', '20150601',250,'001','002');
Query OK, 1 row affected (0.00 sec)
mysql> insert into myorder (ordno, cusno, orddate, ordtotal, salesno, assistno)
values ('002','002', '20150602',150,'002','001');
Query OK, 1 row affected (0.00 sec)
mysql> insert into myorderitem (ordno, serial, prodno, prodqty,prodprice) values
('001','001','001',2,50);
Query OK, 1 row affected (0.02 sec)
mysql> insert into myorderitem (ordno, serial, prodno, prodqty,prodprice) values
('001','002','002',1,150);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myorderitem (ordno, serial, prodno, prodqty,prodprice) values
('002','001','002',1,150);
Query OK, 1 row affected (0.00 sec)
0 留言