建议写一个函数返回统计时间 fuc_statistime
select a.StationID as StationID, max(PassTime) as StatisTime, a.TypeName as TypeName, a.TypeNum as TypeNum, a.Passtype as PassType, sum(a.Money) as TotalMoney, sum(a.PassNumber) as UpDirect, 0 as DownDirect into #t1 from vehicletoll a, Station b
where a.stationID = b.StationID and a.RoadID = b.RoadID and RoadDirect = 0 and a.PassTime > fuc_statistime() and a.PassTime < dateadd(hh, 1, fuc_statistime()) group by a.StationID , a.TypeName, a.TypeNum, a.Passtypeinsert into #t1
select a.StationID, max(PassTime), a.TypeName, a.TypeNum, a.Passtype, sum(a.Money), 0, sum(a.PassNumber) as DownDirect into #t1 from vehicletoll a, Station b
where a.stationID = b.StationID and a.RoadID = b.RoadID and RoadDirect = 1 and a.PassTime < dateadd(hh, 1, fuc_statistime()) group by a.StationID , a.TypeName, a.TypeNum, a.Passtype用case效率低。
select a.StationID as StationID, max(PassTime) as StatisTime, a.TypeName as TypeName, a.TypeNum as TypeNum, a.Passtype as PassType, sum(a.Money) as TotalMoney, sum(a.PassNumber) as UpDirect, 0 as DownDirect into #t1 from vehicletoll a, Station b
where a.stationID = b.StationID and a.RoadID = b.RoadID and RoadDirect = 0 and a.PassTime > fuc_statistime() and a.PassTime < dateadd(hh, 1, fuc_statistime()) group by a.StationID , a.TypeName, a.TypeNum, a.Passtypeinsert into #t1
select a.StationID, max(PassTime), a.TypeName, a.TypeNum, a.Passtype, sum(a.Money), 0, sum(a.PassNumber) as DownDirect into #t1 from vehicletoll a, Station b
where a.stationID = b.StationID and a.RoadID = b.RoadID and RoadDirect = 1 and a.PassTime < dateadd(hh, 1, fuc_statistime()) group by a.StationID , a.TypeName, a.TypeNum, a.Passtype用case效率低。
另外我给游标赋NULL值,为什么它总是前一个非NULL值?我应该怎么做?
@StationID tinyint = 1
AS
SET NOCOUNT ON
SET ANSI_NULLS OFF DECLARE @RoadDirect tinyint,
@TypeName varchar(16),
@TypeNum tinyint,
@Passtype tinyint,
@TotalMoney money,
@UpDirect int,
@DownDirect int,
@stat_clock datetime
--计算统计时间(整时)
SET @stat_clock = CAST(Left(convert(varchar, getdate(), 120),13)+':00:00' AS DATETIME)DECLARE @CrsStatis CURSOR
SET @CrsStatis = CURSOR FOR
SELECT TypeName, TypeNum, PassType, SUM(Money)
FROM VehicleToll WHERE StationID = @StationID
AND PassTime < @stat_clock AND PassTime >= DateAdd(hh, -1, @stat_clock)
GROUP BY TypeName,TypeNum,PassType
OPEN @CrsStatis
FETCH NEXT FROM @CrsStatis
INTO @TypeName, @TypeNum, @PassType, @TotalMoney
WHILE (@@FETCH_STATUS = 0)
BEGIN
--上行(RoadDirect = 0)
SELECT @UpDirect = SUM(PassNumber) FROM VehicleToll V
LEFT JOIN Station S
ON V.StationID = S.StationID
AND V.RoadID = S.RoadID
WHERE V.RoadID IN (SELECT RoadID FROM Station WHERE RoadDirect = 0 AND StationID = @StationID)
AND V.StationID = @StationID AND V.TypeName = @TypeName
AND V.TypeNum = @TypeNum AND V.PassType = @PassType
AND PassTime < @stat_clock AND PassTime >= DateAdd(hh, -1, @stat_clock)
GROUP BY V.TypeName,V.TypeNum,V.PassType
SET @UpDirect = ISNULL(@UpDirect,0)
select @TypeName,@TypeNum,@PassType,@UpDirect
--下行(RoadDirect = 1)
Select @DownDirect = SUM(PassNumber) FROM VehicleToll V
LEFT JOIN Station S
ON V.StationID = S.StationID
AND V.RoadID = S.RoadID
WHERE V.RoadID IN (SELECT RoadID FROM Station WHERE RoadDirect = 1 AND StationID = @StationID)
AND V.StationID = @StationID AND V.TypeName = @TypeName
AND V.TypeNum = @TypeNum AND V.PassType = @PassType
AND PassTime < @stat_clock AND PassTime >= DateAdd(hh, -1, @stat_clock)
GROUP BY V.TypeName,V.TypeNum,V.PassType
SET @DownDirect = ISNULL(@DownDirect,0)
select @TypeName,@TypeNum,@PassType,@DownDirect
--将统计数据插入ProvinceStatis表中
INSERT INTO ProvinceStatis (StationID, StatisTime, TypeName, TypeNum, PassType, TotalMoney, UpDirect, DownDirect)
VALUES ( @StationID, GetDate(), @TypeName, @TypeNum, @PassType, @TotalMoney, @UpDirect, @DownDirect)
FETCH NEXT FROM @CrsStatis
INTO @TypeName, @TypeNum, @PassType, @TotalMoney
END
CLOSE @CrsStatis
DEALLOCATE @CrsStatisRETURN @@ERROR
不行,5555~~~
isnull(@aa, 0)或isnull(@aa,'')
先取出上行的放到临时表,再取下行的插入到临时表然后再从临时表写入到provincestatis不行吗?这样的效率高。
当 SET CONCAT_NULL_YIELDS_NULL 为 ON 时,串联空值与字符串将输出 NULL 结果。例如,SELECT 'abc' + NULL 输出 NULL。当 SET CONCAT_NULL_YIELDS_NULL 为 OFF 时,串联空值与字符串将输出字符串本身(空值作为空字符串处理)。例如,SELECT 'abc' + NULL 输出 abc。如果未指定,则应用 concat null yields null 数据库选项。