检查SELECT中的数据，然后插入新值

Question

因此，我有一些带有数据的表，我需要在旧数据的基础上获得一个新的数据表，然后将它们插入临时表中，以便进行下一次处理。

但是我的SQL有一些问题。

DECLARE @PGNPGE float,
            @PGHTTP400PCT float,
            @PGHTTP500PCT float,
            @PGSLPCT float,
            @PGTME float;

    DECLARE @tempTable TABLE (
        PViews int, 
        Http400 int, 
        Http500 int, 
        PTime int, 
        ExclSimul int, 
        DispSimul int, 
        SlowPages int, 
        AESWA int
    );

    IF EXISTS (SELECT COUNT(*) FROM [ApplicationThreshold] WHERE [ApplicationID] = @AppID)
    BEGIN
        SELECT @PGNPGE = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 6;
        SELECT @PGHTTP400PCT = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 31;
        SELECT @PGHTTP500PCT = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 12;
        SELECT @PGSLPCT = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 49;
        SELECT @PGTME = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 72;
    END
    ELSE
    BEGIN
        SELECT @PGNPGE = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 6;
        SELECT @PGHTTP400PCT = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 31;
        SELECT @PGHTTP500PCT = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 12;
        SELECT @PGSLPCT = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 49;
        SELECT @PGTME = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 72;
    END

    INSERT INTO @tempTable (
        PViews, 
        Http400, 
        Http500, 
        PTime, 
        ExclSimul, 
        DispSimul, 
        SlowPages, 
        AESWA )
    SELECT 
        [PViews] = CASE WHEN sel.[PGNPGE] < @PGNPGE THEN 1 ELSE 0 END,
        [Http400] = CASE WHEN sel.[PGHTTP400PCT] > @PGHTTP400PCT THEN 1 ELSE 0 END, 
        [Http500] = CASE WHEN sel.[PGHTTP500PCT] > @PGHTTP500PCT THEN 1 ELSE 0 END, 
        [PTime] = CASE WHEN sel.[PGTME] > @PGTME THEN 1 ELSE 0 END, 
        [ExclSimul] = CASE WHEN ([PViews] + [Http400] + [Http500] + [PTime]) >= 1 THEN 1 ELSE 0 END, 
        [DispSimul] = CASE WHEN ([PViews] + [Http400] + [Http500] + [PTime]) > 1 THEN ([PViews] + [Http400] + [Http500] + [PTime]) ELSE 0 END, 
        [SlowPages] = CASE WHEN sel.[PGSLPCT] > @PGSLPCT THEN 1 ELSE 0 END, 
        [AESWA] = CASE WHEN [ExclSimul] >=1  THEN 0 ELSE [SlowPages] END
    FROM (SELECT [PGNPGE], [PGHTTP400PCT], [PGHTTP500PCT], [PGSLPCT], [PGTME]
            FROM [dbo].[TimeValue]
            WHERE [ApplicationID] = @AppID
            AND [Time] BETWEEN @DateFrom AND @DateTo) sel;

SELECT * FROM @tempTable;

和错误，我要添加新的值

Msg 207，第16级，状态1，第59行 无效列名“PViews”。 Msg 207，第16级，状态1，第59行 无效列名“Http400 400”。 Msg 207，第16级，状态1，第59行 无效列名“Http500 500”。 Msg 207，第16级，状态1，第59行 无效的列名'PTime‘。 Msg 207，第16级，状态1，第60线 无效列名“PViews”。 Msg 207，第16级，状态1，第60线 无效列名“Http400 400”。 Msg 207，第16级，状态1，第60线 无效列名“Http500 500”。 Msg 207，第16级，状态1，第60线 无效的列名'PTime‘。 Msg 207，第16级，状态1，第60线 无效列名“PViews”。 Msg 207，第16级，状态1，第60线 无效列名“Http400 400”。 Msg 207，第16级，状态1，第60线 无效列名“Http500 500”。 Msg 207，第16级，状态1，第60线 无效的列名'PTime‘。 Msg 207，第16级，状态1，第62线 无效列名“ExclSimul”。 Msg 207，第16级，状态1，第62线 无效列名“SlowPages”。

Answer 1

尝试使用CTE在表变量中插入数据：

declare @PGNPGE float,
    @PGHTTP400PCT float,
    @PGHTTP500PCT float,
    @PGSLPCT float,
    @PGTME float;

declare @tempTable TABLE 
(
    PViews int, 
    Http400 int, 
    Http500 int, 
    PTime int, 
    ExclSimul int, 
    DispSimul int, 
    SlowPages int, 
    AESWA int
);

IF EXISTS (SELECT COUNT(*) FROM [ApplicationThreshold] WHERE [ApplicationID] = @AppID)
BEGIN
    SELECT @PGNPGE = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 6;
    SELECT @PGHTTP400PCT = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 31;
    SELECT @PGHTTP500PCT = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 12;
    SELECT @PGSLPCT = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 49;
    SELECT @PGTME = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 72;
END
ELSE
BEGIN
    SELECT @PGNPGE = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 6;
    SELECT @PGHTTP400PCT = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 31;
    SELECT @PGHTTP500PCT = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 12;
    SELECT @PGSLPCT = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 49;
    SELECT @PGTME = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 72;
END

;with ThresholdCTE
as
(
    SELECT 
        case when sel.[PGNPGE] < @PGNPGE then 1 else 0 end as [PViews],
        case when sel.[PGHTTP400PCT] > @PGHTTP400PCT then 1 else 0 end as [Http400], 
        case when sel.[PGHTTP500PCT] > @PGHTTP500PCT then 1 else 0 end as [Http500], 
        case when sel.[PGTME] > @PGTME then 1 else 0 end as [PTime], 
        case when ([PViews] + [Http400] + [Http500] + [PTime]) >= 1 then 1  else 0 end as [ExclSimul], 
        case when ([PViews] + [Http400] + [Http500] + [PTime]) > 1 then ([PViews] + [Http400] + [Http500] + [PTime]) else 0 end as [DispSimul], 
        case when sel.[PGSLPCT] > @PGSLPCT then 1 else 0 end as [SlowPages], 
        case when [ExclSimul] >=1 then 0 else [SlowPages] end as [AESWA]
    from [dbo].[TimeValue]
    where [ApplicationID] = @AppID
          and [Time] between @DateFrom and @DateTo
)

insert into @tempTable 
(
    PViews, 
    Http400, 
    Http500, 
    PTime, 
    ExclSimul, 
    DispSimul, 
    SlowPages, 
    AESWA 
)
select
    PViews, 
    Http400, 
    Http500, 
    PTime, 
    ExclSimul, 
    DispSimul, 
    SlowPages, 
    AESWA 
from [ThresholdCTE]


SELECT * FROM @tempTable;