编写SQL INSERT，从两个独立的相关行中检索其数据

问题描述

我正在编写一个SQL脚本，该脚本将使用同一AccountID下的两行中的数据插入一条新记录.

I am writing a SQL script that is to insert a new record using data from two rows that are under the same AccountID.

我的表如下所示:

AccountID | ActivityId | DisplayDetails | TransactionDate    | EnvironmentId
============================================================================
1              7         Display1        2015-02-02 00:00:00.000    1
1              8         DisplayThis1    2018-02-02 00:00:00.000    1
1              7         Display2        1999-02-02 00:00:00.000    2
1              8         DisplayThis2    2000-02-02 00:00:00.000    2

我的解决方法是查找找到每个7,8组合，并插入带有ActivityId 78的新行，该行从ActivityId 7获取DisplayDetails，从ActivityId 8获取TransactionDate.

My fix is to find find each 7,8 combination and insert a new row with ActivityId 78 that gets the DisplayDetails from ActivityId 7 and TransactionDate from ActivityId 8.

我的查询如下:

SELECT * 
INTO #ActivityEight
FROM Account A
WHERE A.ActivityId = 8


INSERT INTO #Account (AccountId, ActivityId, DisplayDetails, TransactionDate)
SELECT VL.AccountId, 78, S.DisplayDetails, VL.TransactionDate
FROM #temp2 VL WITH(NOLOCK)
JOIN #ActivityEight S
    ON VL.AccountId = S.AccountId
WHERE VL.ActivityId = 7

但是，当我运行SELECT * FROM Account时，每7和8行我得到78行，而每7和8组合我应该只得到1 78行.

However when I run SELECT * FROM Account I get a 78 row for each 7 and 8 row, when I should only get 1 78 row per 7 and 8 combination.

AccountID | ActivityId | DisplayDetails | TransactionDate     | EnvironmentId
=============================================================================
   1            7        Display1        2015-02-02 00:00:00.000    1
   1            8        DisplayThis1    2018-02-02 00:00:00.000    1
   1            7        Display2        1999-02-02 00:00:00.000    2
   1            8        DisplayThis2    2000-02-02 00:00:00.000    2
   1           78        DisplayThis1    2015-02-02 00:00:00.000    NULL
   1           78        DisplayThis2    2015-02-02 00:00:00.000    NULL
   1           78        DisplayThis1    1999-02-02 00:00:00.000    NULL
   1           78        DisplayThis2    1999-02-02 00:00:00.000    NULL

我相信我可以利用EnvironmentId实现所需的功能，但是我不确定如何实现.

I believe I can utilize the EnvironmentId to achieve the desired functionality, but I'm not sure how.

任何帮助将不胜感激.

谢谢！

推荐答案

我认为这会对您有所帮助

I think this will help you

INSERT INTO #Account (AccountId, ActivityId, DisplayDetails, TransactionDate)
SELECT VL.AccountId, 78, S.DisplayDetails, VL.TransactionDate
FROM Account VL WITH(NOLOCK)
JOIN Account S ON VL.AccountId = S.AccountId and VL.EnvironmentId = S.EnvironmentId
WHERE VL.ActivityId = 7 and S.ActivityId = 8 

这篇关于编写SQL INSERT，从两个独立的相关行中检索其数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！