--------------------------------------------
附上数据库脚本：
CREATE VIEW [dbo].[详细药品]
AS
SELECT dbo.药品分类代码表.ypcode, dbo.药品分类代码表.ypname, dbo.药品信息表.guige,
dbo.药品信息表.[function], dbo.药品信息表.chengfen, dbo.药品信息表.yongliang,
dbo.药品信息表.referenceprice
FROM dbo.药品信息表 INNER JOIN
dbo.药品分类代码表 ON dbo.药品信息表.ypcode = dbo.药品分类代码表.ypcode
GO
/****** 对象: View [dbo].[库存信息]脚本日期: 05/25/2012 14:32:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[库存信息]
AS
SELECT dbo.药品库存表.ypcode, dbo.药品库存表.productdate, dbo.药品库存表.kcamount,
dbo.药品分类代码表.ypname, dbo.药品信息表.referenceprice,
dbo.药品库存表.yxqz
FROM dbo.药品分类代码表 INNER JOIN
dbo.药品库存表 ON
dbo.药品分类代码表.ypcode = dbo.药品库存表.ypcode INNER JOIN
dbo.药品信息表 ON dbo.药品库存表.ypcode = dbo.药品信息表.ypcode
GO
/****** 对象: StoredProcedure [dbo].[gqyp_insert]脚本日期: 05/25/2012 14:32:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[gqyp_insert]
@ypcode char(50),
@yxqz smalldatetime,
@deal char(50)='焚烧'
as
insert into 过期药品出库登记(ypcode,yxqz,deal,ckamount)
select 药品库存表.ypcode,药品库存表.yxqz,@deal,药品库存表.kcamount
from 药品库存表
where 药品库存表.ypcode=@ypcode and 药品库存表.yxqz=@yxqz
delete from 药品库存表
where ypcode=@ypcode and yxqz=@yxqz
GO
/****** 对象: Table [dbo].[药品分类代码表]脚本日期: 05/25/2012 14:32:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[药品分类代码表](
[ypcode] [char](12) NOT NULL,
[ypname] [char](50) NULL,
CONSTRAINT [PK_药品分类代码表] PRIMARY KEY CLUSTERED
(
[ypcode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[供货商信息表]脚本日期: 05/25/2012 14:32:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[供货商信息表](
[ghno] [char](50) NOT NULL,
[ghname] [char](50) NULL,
[city] [char](50) NULL,
[phone] [char](50) NULL,
CONSTRAINT [PK_供货商信息表] PRIMARY KEY CLUSTERED
(
[ghno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: StoredProcedure [dbo].[insert_采购信息表_1]脚本日期: 05/25/2012 14:32:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[insert_采购信息表_1]
(@采购单号_1 [int],
@采购时间_2 [smalldatetime],
@药品代码_3 [char](50),
@供货商号_4 [char](50),
@进价_5 [float],
@采购数量_6 [int],
@金额_7 [float],
@采购员编号_8 [char](50))
AS INSERT INTO [医院药品进销存系统].[dbo].[采购信息表]
( [采购单号],
[采购时间],
[药品代码],
[供货商号],
[进价],
[采购数量],
[金额],
[采购员编号])
VALUES
( @采购单号_1,
@采购时间_2,
@药品代码_3,
@供货商号_4,
@进价_5,
@采购数量_6,
@金额_7,
@采购员编号_8)
GO
/****** 对象: StoredProcedure [dbo].[kcsearch]脚本日期: 05/25/2012 14:32:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[kcsearch]
@no char(50)
as
select 药品库存表.ypcode,ypname,kcamount,productdate,yxqz
from 药品库存表,药品分类代码表
where 药品库存表.ypcode=药品分类代码表.ypcode and 药品库存表.ypcode=@no
GO
/****** 对象: StoredProcedure [dbo].[kcsearch_name]脚本日期: 05/25/2012 14:32:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[kcsearch_name]
@name char(50)
as
select 药品库存表.ypcode,ypname,kcamount,productdate,yxqz
from 药品库存表,药品分类代码表
where 药品库存表.ypcode=药品分类代码表.ypcode and ypname=@name
GO
/****** 对象: Table [dbo].[采购人员信息]脚本日期: 05/25/2012 14:32:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[采购人员信息](
[cgyno] [char](50) NOT NULL,
[cgyname] [char](50) NULL,
[sex] [char](10) NULL,
[cgyphone] [char](50) NULL,
[email] [char](50) NULL,
CONSTRAINT [PK_采购人员信息] PRIMARY KEY CLUSTERED
(
[cgyno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
-----------------------------------
需要可与本人联系。
问题太基础，而且 太多了。都是基础的查询。。这些是作业吧，都帮你写了 你其实学不到什么东西的、
可以给你说几个 需要用到的关键字，你套上去用下。
select * from table1 as t1 join table2 as t2 on t1.id=t2.id 多表连接
where a>1 条件
order by 成绩 desc 倒序
order by 成绩 asc 正序
sum() 计算 字段 总和
还有些子查询 多看看书 其实都不难。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）