SQL从具有计算列的查询中插入值

I have a table like this:

我有这样一张桌子：

id type | month | value

Where type is AC,FC or BG

类型是AC，FC或BG

I want to create for each distinct row id,type(where type is AC or FC) a new row with type AF and value as AC if exist or as FC (AC value has priority on FC value).

我想为每个不同的行id创建类型（其中类型为AC或FC），类型为AF的新行，如果存在则值为AC或FC（AC值优先于FC值）。

For the moment I can extract unique list on which for each item I have to create a new row:

目前我可以提取唯一列表，我必须为每个项目创建一个新行：

select id_prj,month  from table where type='AC' or type='FC' group by id_prj,month

How do I create for each item returned from this query a new row with the value that can assume AC or FC value depending on its existence.

如何为从此查询返回的每个项创建一个新行，其值可以根据其存在而采用AC或FC值。

Below a table example

下面的表格示例

| ID_PRJ | TYPE | MONTH | VALUE |
-----------------------------
| 1  | AC   |  1    | 1000  |
-----------------------------
| 1  | FC   |  1    | 200  |
-----------------------------
| 3  | BG   |  1    | 1000  |
-----------------------------
| 4  | FC   |  2    | 100  |

The table after the insert should be:

插入后的表格应为：

| ID_PRJ | TYPE | MONTH | VALUE |
-----------------------------
| 1  | AC   |  1    | 1000  |
-----------------------------
| 1  | FC   |  1    | 200  |
-----------------------------
| 3  | BG   |  1    | 1000  |
-----------------------------
| 4  | FC   |  2    | 100  |
-----------------------------
| 1  | AF   |  1    | 1000  |
-----------------------------
| 4  | AF   |  2    | 100  |

3 个解决方案

#1

how about this :

这个怎么样：

insert into table (ID_PRJ, TYPE, Month, Value)


select id_prj,type,month,value from (
select id_prj,'FC' as type, month,value  , row_number() over ( partition by id_prj order by type asc) rowid
from table 
where type='AC' or type='FC' 
) as x where rowid = 1

#2

You appear to just be changing the type. I think this does it.

您似乎只是在改变类型。我认为这样做。

insert into table (ID_PRJ, TYPE, Month, Value)
Select ID_PRJ, 'AF' Type, Month, Value
from table where Type in ('AC','FC')

#3

One fairly simple way to do it is to use a full join to a derived table.

一种相当简单的方法是使用完全连接到派生表。

Create and populate sample table (Please save us this step in your future questions):

创建并填充样本表（请在将来的问题中保存此步骤）：

DECLARE @T AS TABLE
(
    ID_PRJ int,
    TYPE char(2),
    MONTH int,
    VALUE int
)

INSERT INTO @T VALUES
(1, 'AC', 1, 1000),
(1, 'FC', 1, 200),
(3, 'BG', 1, 1000),
(4, 'FC', 2, 100)

The insert...select statement:

insert ... select语句：

INSERT INTO @T (ID_PRJ, MONTH, TYPE, VALUE)
SELECT  ISNULL(t1.ID_PRJ, t0.ID_PRJ), 
        ISNULL(t1.MONTH, t0.MONTH), 
        'AF', 
        ISNULL(t1.VALUE, t0.VALUE)
FROM @T t0
FULL JOIN
(
    SELECT ID_PRJ, MONTH, TYPE, VALUE
    FROM @T 
    WHERE TYPE = 'AC'
) t1 ON t0.ID_PRJ = t1.ID_PRJ
    AND t0.MONTH = t1.MONTH
WHERE t0.TYPE IS NULL OR t0.TYPE = 'FC'

Verify insert:

验证插入：

SELECT ID_PRJ, MONTH, TYPE, VALUE
FROM @T

Results:

结果：

ID_PRJ  MONTH   TYPE    VALUE
1       1       AC      1000
1       1       FC      200
3       1       BG      1000
4       2       FC      100
1       1       AF      1000
4       2       AF      100

#1