我这里有两张表
CREATE TABLE [dbo].[Mileage](
[id] [int] IDENTITY(1,1) NOT NULL,
[CarId] [int] NOT NULL,
[DateTime] [datetime] NULL,
[Mileage] [float] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Maintenance](
[id] [int] IDENTITY(1,1) NOT NULL,
[CheId] [int] NOT NULL,
[MaintenanceStartTime] [datetime] NOT NULL,
[MaintenanceTipsTime] [datetime] NOT NULL,
[Mileage] [numeric](18, 2) NOT NULL,
[MileageTips] [numeric](18, 2) NULL,
[Type] [int] NOT NULL,
[Excessive] [int] NULL,
[MileageHas] [numeric](18, 2) NULL
) ON [PRIMARY]下面是我大概要表达的意思:
S1 = (select * from Maintenance a where a.MaintenanceStartTime =
(select max(b.MaintenanceStartTime) from Maintenance b
where CheID = @CheId and Typ = @Type
))if(Exit(S1))
{
@MileageTotal = select sum(Mileage) from Mileage where cheId = @CheId and convert(varchar(10), DateTime],120) >=convert(varchar(10),[S1.MaintenanceStartTime],120)
Update Maintenance Set S1.MileageHas = @MileageTotal where id = S1.id
Update Maintenance Set Excessive = 1 where MileageHas > MileageTips
}
我要将上面的几条语句组织成一个存储过程
CREATE TABLE [dbo].[Mileage](
[id] [int] IDENTITY(1,1) NOT NULL,
[CarId] [int] NOT NULL,
[DateTime] [datetime] NULL,
[Mileage] [float] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Maintenance](
[id] [int] IDENTITY(1,1) NOT NULL,
[CheId] [int] NOT NULL,
[MaintenanceStartTime] [datetime] NOT NULL,
[MaintenanceTipsTime] [datetime] NOT NULL,
[Mileage] [numeric](18, 2) NOT NULL,
[MileageTips] [numeric](18, 2) NULL,
[Type] [int] NOT NULL,
[Excessive] [int] NULL,
[MileageHas] [numeric](18, 2) NULL
) ON [PRIMARY]下面是我大概要表达的意思:
S1 = (select * from Maintenance a where a.MaintenanceStartTime =
(select max(b.MaintenanceStartTime) from Maintenance b
where CheID = @CheId and Typ = @Type
))if(Exit(S1))
{
@MileageTotal = select sum(Mileage) from Mileage where cheId = @CheId and convert(varchar(10), DateTime],120) >=convert(varchar(10),[S1.MaintenanceStartTime],120)
Update Maintenance Set S1.MileageHas = @MileageTotal where id = S1.id
Update Maintenance Set Excessive = 1 where MileageHas > MileageTips
}
我要将上面的几条语句组织成一个存储过程
(
select 1 from Maintenance a where CheID = @CheId and Typ = @Type
) begin
Update Maintenance
Set MileageHas = @MileageTotal
from Maintenance s,
(
select Total=sum(Mileage)
from Mileage ,Maintenance s1
where cheId = @CheId and [DateTime]>=[S1].[MaintenanceStartTime]
)
where id = S.id Update Maintenance Set Excessive = 1 where MileageHas > MileageTips
end
(select max(b.MaintenanceStartTime) from Maintenance b
where CheID = @CheId and Typ = @Type
))结果,不能这样写的,要根据它的结果做下面的判断
Set MileageHas = (select sum(Mileage) from Mileage where cheId = @CheId
and convert(varchar(10),[DateTime],120)>=convert(varchar(10),(select max(MaintenanceStartTime)
from Maintenance),120))
where MaintenanceStartTime=(select max(MaintenanceStartTime) from Maintenance)
and CheID = @CheId and [Type] = @Type
as
begin
declare @MileageTotal as numeric(18, 2)
if exists (select 1 from Maintenance a where a.MaintenanceStartTime = (select max(b.MaintenanceStartTime) from Maintenance b where CheID = @CheId and Typ = @Type))
begin
select @MileageTotal = sum(Mileage) from Mileage where cheId = @CheId and convert(varchar(10), DateTime,120) >=convert(varchar(10),S1.MaintenanceStartTime,120)
Update Maintenance Set MileageHas = @MileageTotal
from Maintenance m , (select * from Maintenance a where a.MaintenanceStartTime = (select max(b.MaintenanceStartTime) from Maintenance b where CheID = @CheId and Typ = @Type)) n
where m.id = n.id
Update Maintenance Set Excessive = 1 where MileageHas > MileageTips
end
end