- 阿啵呲嘚
-
--1.创建数据库 shopping
create database shopping
go
use shopping
go
--(1)创建参数表:param [param_id(自增长主键), 参数名称(param_name)]
create table param
(
param_id int primary key identity(1,1),
param_name varchar(20),
)
go
--创建商品表:goods[,goods_id(自增长主键) 商品名称(goods_name varchar),商品类型(goods_type, int, 外键,参考参数表中的param_id),单价(price float), 库中原始数量(init_num int), 备注]
create table goods
(
goods_id int primary key identity(1,1),
goods_name varchar(20),
goods_type int foreign key (goods_type) references param(param_id),
price float,
init_num int ,
remark varchar(50)
)
go
--创建商品出库表: goods_out[goods_out_id(自增长主键), 商品(外键),出库数量, 出库时间,备注]
create table goods_out
(
goods_out_id int primary key identity(1,1),
goods int not null foreign key (goods) references goods(goods_id),
goods_out int default 0 ,
out_time datetime default getdate(),
remark varchar(50)
)
go
--向参数表添加信息
insert into param(param_name) values ("日用品")
insert into param values ("电器")
insert into param values ("食品")
insert into param values ("其他")
go
--向商品表添加信息
insert into goods values ("硬盘",2,320.0,200,"这是硬盘")
insert into goods values ("牙膏",1,12.2,100,"这是牙膏")
insert into goods values ("巧克力",3 ,2.9, 300,"这是巧克力")
insert into goods values ("钢笔",4,30.1,200,"这是钢笔")
insert into goods values ("毛笔",4,30.1,120,"这是毛笔")
go
--向商品出库表添加信息
insert into goods_out values (1,20,"2009-01-21","买了")
insert into goods_out values (2,32,"2009-01-21","买了")
insert into goods_out values (3,11,"2009-01-21","买了")
go
--查询三张表
select * from param
select * from goods
select * from goods_out
go
--查询出所有的商品信息
select * from param ,goods,goods_out
where goods.goods_id *= goods_out.goods
and goods.goods_type *= param.param_id
go
--查询所有 日用品 的商品信息
select * from param ,goods,goods_out
where param.param_name = "日用品"
and goods.goods_id *= goods_out.goods
and goods.goods_type = param.param_id
go
--查询所有从来没有出库的商品的信息
select * from goods left join param
on goods.goods_type = param.param_Id
where
goods.goods_id not in
(
select goods from goods_out
)
go
--查询出目前所有的商品的库存数量
select goods_name, goods.init_num - a.out_num, goods.init_num, a.out_num
from goods, (select goods, sum(goods_out) out_num from goods_out group by goods) a
where goods.goods_id *= a.goods
go
--查询出出库数量最多的商品信息
select goods.*, param.param_name from
(select max(out_num) max_num
from (select goods, sum(goods_out) out_num from goods_out group by goods) a) b,
(select goods, sum(goods_out) out_num from goods_out group by goods) c,
goods,
param
where b.max_num = c.out_num
and goods.goods_Id = c.goods
and goods.goods_type = param.param_id
- cloudcone
-
自己写的:
1. Create DataBase shopping
USE shopping --如果只是在这题中的话,这句可以不要.
2.
⑴ Create Table param
(
param_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
param_name VARCHAR(30)
)
insert into param VALUES("日用品")
insert into param VALUES("电器")
insert into param VALUES("食品")
insert into param VALUES("其它")
⑵ Create Table goods
(
goods_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
goods_name Varchar(30),
goods_type INT FOREIGN KEY REFERENCES param ( param_id ),
price Float,
init_num INT,
Remark NVARCHAR(300)
)
insert into goods VALUES("冰箱",2,100,0,"")
insert into goods VALUES("话梅",3,3,0,"")
⑶ Create Table goods_out
(
goods_out_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY ,
goods_id INT FOREIGN KEY REFERENCES goods ( goods_id ),
OutQty FLOAT,
OutDate DATETIME,
Remark NVARCHAR(300)
)
3. SELECT * FROM goods
4. SELECT A.* FROM goods A LEFT JOIN param B ON A.goods_type=B.param_id WHERE B.param_name="日用品"
5. Select * From Goods WHERE goods_id Not In (Select goods_id From goods_out)
6. Select A.goods_name,ISNULL(A.init_num-B.OutQty,0) As [库存数量] FROM goods A
Left Join (Select goods_id,ISNULL(Sum(OutQty),0) As OutQty From goods_out GROUP BY goods_id) B ON A.goods_id=B.goods_id
7. Select A.* FROM goods A
INNER Join (Select goods_id,MAX(OutQty) As OutQty From goods_out GROUP BY goods_id) B ON A.goods_id=B.goods_id