我建了四个表CREATE TABLE customers
(
userID char(20) primary key,
firstName char(50) not null,
middlel char(1) ,
lastName char(50) not null,
emailAddress varchar(100) not null,
active char(1) not null default 'Y'
);CREATE TABLE inventory
(
itemID INT IDENTITY(1,1) PRIMARY KEY,
itemShortName VARCHAR(50) not null,
itemDesc VARCHAR (500) ,
itemPrice DECIMAL CHECK (itemPrice>0),
inventoryCount INT CHECK (inventoryCount >=0)
);
CREATE TABLE orderOverview
(
orderID INT IDENTITY(1,1) PRIMARY KEY,
orderDate DATE not null,
customerID CHAR (20) not null REFERENCES customers(userID),
orderAmt DECIMAL not null CHECK( orderAmt>0),
orderTax DECIMAL not null CHECK (orderTax>0),
orderTotal DECIMAL not null CHECK (orderTotal>0),
shippedDate DATE
);
CREATE TABLE orderItems
(
orderItemID INT IDENTITY (1,1) PRIMARY KEY ,
orderID INT NOT NULL REFERENCES orderOverview(orderID),
itemID int not null REFERENCES inventory (itemID),
itemCount int not null CHECK(itemCount>0) DEFAULT 1,
itemPriceEach DECIMAL not null CHECK(itemPriceEach>0)
);
现在我想建一个函数,go through the orderItems table and add totals for all shipped orders and return the total number.
我觉得我写的有问题,希望哪位大师帮我写一下,我好参考一下。
谢谢啦~
(
userID char(20) primary key,
firstName char(50) not null,
middlel char(1) ,
lastName char(50) not null,
emailAddress varchar(100) not null,
active char(1) not null default 'Y'
);CREATE TABLE inventory
(
itemID INT IDENTITY(1,1) PRIMARY KEY,
itemShortName VARCHAR(50) not null,
itemDesc VARCHAR (500) ,
itemPrice DECIMAL CHECK (itemPrice>0),
inventoryCount INT CHECK (inventoryCount >=0)
);
CREATE TABLE orderOverview
(
orderID INT IDENTITY(1,1) PRIMARY KEY,
orderDate DATE not null,
customerID CHAR (20) not null REFERENCES customers(userID),
orderAmt DECIMAL not null CHECK( orderAmt>0),
orderTax DECIMAL not null CHECK (orderTax>0),
orderTotal DECIMAL not null CHECK (orderTotal>0),
shippedDate DATE
);
CREATE TABLE orderItems
(
orderItemID INT IDENTITY (1,1) PRIMARY KEY ,
orderID INT NOT NULL REFERENCES orderOverview(orderID),
itemID int not null REFERENCES inventory (itemID),
itemCount int not null CHECK(itemCount>0) DEFAULT 1,
itemPriceEach DECIMAL not null CHECK(itemPriceEach>0)
);
现在我想建一个函数,go through the orderItems table and add totals for all shipped orders and return the total number.
我觉得我写的有问题,希望哪位大师帮我写一下,我好参考一下。
谢谢啦~
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
userID primary key
firstName required
middlel optional
lastname required
emailAddress required
active required, default-Ytable inventory
itemID primary key, auto generated, start at 1
itemShortName required
itemDesc optional
inventoryCount required, >=0table orderOverview
orderID primary key, auto generated, starts at 1
orderDate required
customerID required, references customers
orderAmt required, >0
orderTax required, >0
orderTotal required, >0table orderIteams
orderItemID primary key, auto generated, starts at 1
orderID required, references orderOverview
itemID required, references inventory
itemCount required, >0, default 1
itemPriceEach required, >0这样够完整的了吧。这个问题可咋决绝呢?
求高人
a.firstname+a.middlel+a.lastName,
sum(b.itemPrice*b.inventoryCount)
from
customers a,inventory b,orderOverview c,orderIteams d
where
a.userID=c.customerID
and
b.itemID=d.itemID
and
c.orderID=d.orderID
group by
a.firstname+a.middlel+a.lastName
DECLARE funcWithCursor CURSOR FOR
SELECT COUNT(oo.orderID) FROM orderOverview oo, orderItems oi WHERE oo.orderID=oi.orderID AND oo.shippedDate is not null
OPEN funcWithCursor
FETCH NEXT FROM funcWithCursor INTO @total
BEGIN
--
FETCH NEXT FROM funcWithCursor INTO @total
ENDCLOSE funcWithCursor
DEALLOCATE funcWithCursor谁能帮我完成以下begin end中间的部分啊,我觉得这个架构应该是没有问题的