商品銷售管理系統(tǒng)-在線瀏覽
2025-05-30 06:00本頁面
【正文】 IntNot null商品總利潤Number102Not null員工編號IntNot null客戶編號IntNot null銷售日期DATENot nullcreate database ZCH。（1）員工信息表的建立：CREATE TABLE YGXX( YGBH int IDENTITY(1,1) NOT NULL, YGXM char(10) NOT NULL, YGXB char(2) NOT NULL, YGNL int NOT NULL, YGMM int NOT NULL,constraint PK_YGXX primary key nonclustered (YGBH) )（2）商品信息表的建立CREATE TABLE SPXX( SPBH int NOT NULL, SPM char(10) COLLATE Chinese_PRC_CI_AS NOT NULL, KCL int NOT NULL, JHJG numeric(10, 2) NOT NULL, CHJG numeric(10, 2) NOT NULL, LR numeric(10, 2) , constraint PK_SPXX primary key nonclustered (SPBH))（3）員工銷售信息表的建立CREATE TABLE YGXSXX( DDH int NOT NULL, YGBH int NOT NULL, KHBH int NOT NULL, XSRQ datetime NOT NULL, PRIMARY KEY (DDH,YGBH)) (4)商品銷售信息表的建立CREATE TABLE SPXSXX( DDH int NOT NULL, SPBH int NULL, XSL int NULL,XSRQ datetime,constraint PK_SPXSXX primary key nonclustered (DDH)) (5)庫存信息表的建立CREATE TABLE KCXX( SPBH int NOT NULL, KCL int NOT NULL, QDRQ datetime NOT NULL, PRIMARY KEY (SPBH,QDRQ))（6）客戶表的建立CREATE TABLE KH( KHBH int NOT NULL, KHXM char(10) COLLATE Chinese_PRC_CI_AS Not null, KHXB char(2) COLLATE Chinese_PRC_CI_AS , KHDH char(15) Not null,constraint PK_KH primary key nonclustered (KHBH))（7）進(jìn)貨記錄表的建立 CREATE TABLE JHJL( JHLSH int IDENTITY(1,1) NOT NULL, SPBH int NOT NULL , JHL int NOT NULL, JHJG numeric (10, 2) NOT NULL, JHRQ datetime NOT NULL, PRIMARY KEY (JHLSH)) (8)經(jīng)理表的建立CREATE TABLE JL( JLXM char(9) NOT NULL, JLMM int NOT NULL,)（1） 用于經(jīng)理查詢員工表的視圖定義如下： CREATE VIEW 經(jīng)理查看視圖ASSELECT YGBH, YGXM, YGXB, YGNLFROM YGXX（2） 用于經(jīng)理查詢商品當(dāng)日銷售信息的視圖定義如下：CREATE VIEW 當(dāng)日銷售視圖ASSELECT , SPM, XSLFROM SPXX,SPXSXXwhere EXISTS (select * from SPXSXX where datediff(dd,XSRQ,GETDATE())=0 and =)and =（3） 用于經(jīng)理查詢當(dāng)日庫存的視圖定義如下：CREATE VIEW 當(dāng)日庫存視圖ASSELECT , ,QDRQFROM KCXX,SPXXwhere exists(select * from (select SPBH,max(QDRQ) as FTime from KCXX group by SPBH) xwhere = and = and =)（4） 用于員工查詢商品信息的視圖定義如下：CREATE VIEW 商品信息視圖ASSELECT , , , , , FROM SPXX INNER JOIN JHJL ON = （5）用于查詢銷售信息的視圖定義如下： CREATE VIEW 銷售信息視圖ASSELECT , , , , * AS SPZLR, FROM SPXX INNER JOIN SPXSXX ON = INNER JOIN YGXSXX ON = CREATE CLUSTERED INDEX [員工編號索引] ON YGXSXX( YGBH ASC, DDH ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IG
公司管理相關(guān)推薦
鄂ICP備17016276號-1