请懂的游标的高手进 - SQL Server论坛

楼主

已结贴√ 问题点数：20 回复次数：3

请懂的游标的高手进

请教高手：如何在B(采购表）可以查到A（销售表）与销售日期最近的一笔记录，并且销售日期不能大于采购日期。（查询出销售表中的采购价格，要与销售日期最近的一笔数，并且销售日期不能大于采购日期）如下图通过A、B表得到C表：

图片附件: 游客没有浏览图片的权限，请登录或注册

[ 本帖最后由 arook 于 2012-4-19 10:20 编辑 ]

业精于勤

第 2 楼

得分:10

你确定要用cursor?

类似　－

select      *
from        B join
            ( select      A.date, A.name, A.price, max(b.date) as BMaxDate
              from        A join B on A.name = B.name and a.date > b.date
              group by    A.date, A.name, A.price
             ) as C on B.name = C.name and B.date = C.BMaxDate

第 3 楼

得分:10

正如楼上那位兄弟所说的，你的问题可以不使用游标解决。
不过，既然你喜欢用游标，下面就给出用游标的代码：

程序代码：

--给利润表添加数据
DECLARE @var1 smalldatetime        --销售日期
DECLARE @var2 nvarchar(50)        --名称
DECLARE @var3 decimal(18, 2)    --销售价
DECLARE @var4 decimal(18, 2)    --采购价
DECLARE @var5 smalldatetime        --采购日期
DECLARE mycursor CURSOR FOR
SELECT 日期, 名称, 销售价
FROM arook_A
OPEN mycursor
FETCH NEXT FROM mycursor
INTO @var1, @var2, @var3

WHILE @@FETCH_STATUS = 0
BEGIN
    select @var4=采购价,@var5=日期 from arook_B where 名称=@var2 and @var1>=日期 order by 日期
    insert into arook_C select @var1,@var2,@var3,@var4,@var3-@var4,@var5
    FETCH NEXT FROM mycursor
        INTO @var1, @var2, @var3
END
CLOSE mycursor
DEALLOCATE mycursor
GO

select * from arook_C

做自己喜欢的事！

第 4 楼

得分:0

把调试环境也贴上来吧！

程序代码：

--创建销售表:arook_A
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[arook_A]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[arook_A]
GO
CREATE TABLE [dbo].[arook_A] (
    [日期] [smalldatetime] NULL ,
    [名称] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [销售价] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
--给销售表添加数据
insert into arook_A values('2011-5-18','苹果',5.38)
insert into arook_A values('2012-10-6','香蕉',3.55)
insert into arook_A values('2011-7-22','梨子',4.5)
insert into arook_A values('2010-6-2','草莓',7.5)

--创建采购表:arook_B
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[arook_B]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[arook_B]
GO
CREATE TABLE [dbo].[arook_B] (
    [日期] [smalldatetime] NULL ,
    [名称] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [采购价] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
--给采购表添加数据
insert into arook_B values('2012-10-1','香蕉',3)
insert into arook_B values('2011-5-13','苹果',4.2)
insert into arook_B values('2012-10-7','香蕉',3.6)
insert into arook_B values('2011-4-11','苹果',3.8)
insert into arook_B values('2011-4-26','梨子',4.1)
insert into arook_B values('2011-5-1','苹果',3.7)
insert into arook_B values('2010-6-1','草莓',6.8)
insert into arook_B values('2011-7-20','梨子',3.9)
insert into arook_B values('2010-5-3','草莓',7.1)

----创建利润表:arook_C
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[arook_C]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[arook_C]
GO
CREATE TABLE [dbo].[arook_C] (
    [日期] [smalldatetime] NULL ,
    [名称] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [销售价] [decimal](18, 2) NULL ,
    [采购价] [decimal](18, 2) NULL ,
    [毛利润] [decimal](18, 2) NULL ,
    [采购日期] [smalldatetime] NULL
) ON [PRIMARY]
GO

做自己喜欢的事！