将数据插入临时表，一个表中有多列

Question

我有一个包含列的表：ID（Int）、Date（Date）和Price（Decimal）。Date列格式为2013-04-14:

表示例

ID      Date        Price
    1       2012/05/02  23.5
    1       2012/05/03  25.2
    1       2012/05/04  22.5
    1       2012/05/05  22.2
    1       2012/05/06  26.5
    2       2012/05/02  143.5
    2       2012/05/03  145.2
    2       2012/05/04  142.2
    2       2012/05/05  146.5
    3       2012/05/02  83.5
    3       2012/05/03  85.2
    3       2012/05/04  80.5

查询示例：

我希望能够从表中选择日期范围内的所有ID 1和ID 3的数据，并将其放在一个有三列的表中，按Date列排序。此外，我还想将其插入一个临时表中，以便对数据进行数学计算。如果有更好的方法，请评论。

正确结果示例

Date        ID1     ID3
2012-05-02  23.5    83.5    
2012-05-03  25.2    85.2    
2012-05-04  22.5    80.2

任何帮助和建议都将不胜感激，

感谢

---

Answer 1

尝试以下操作。

CREATE TABLE #temp (
  Date date,
  x money,
  y money
  )
;
SELECT
 Date,
 MAX(CASE WHEN id=1 THEN price END) AS x,
 MAX(CASE WHEN id=3 THEN price END) AS y
FROM Top40
WHERE Date BETWEEN '2012-05-02' AND '2012-05-04'
GROUP BY 
 Date
;

有关的工作示例，请参阅SQL Fiddle

编辑：要在x和y列上使用LAG窗口函数，必须首先使用公共表表达式或CTE。

WITH prices AS(
SELECT
 Date as myDate,
 MAX(CASE WHEN id=1 THEN price END) AS x,
 MAX(CASE WHEN id=3 THEN price END) AS y
FROM Top40
WHERE Date BETWEEN '2012-05-02' AND '2012-05-04'
GROUP BY 
 Date
 )
SELECT
 myDate,
 p.x,
 (p.x/(LAG(p.x) OVER (ORDER BY MyDate))-1) as x_return,
 p.y,
 (p.y/(LAG(p.y) OVER (ORDER BY MyDate))-1) as y_return
FROM prices p
ORDER BY
 myDate
;

例如，请参阅新的SQL Fiddle。

Answer 2

在代码中实现这一点的最简单方法（尽管它可能在大型数据集中表现不佳）是执行以下操作：

SELECT [Date], x = MAX(CASE WHEN ID = 1 THEN PRICE END)
, y = MAX(CASE WHEN ID = 3 THEN PRICE END)
INTO #tmp
FROM Top40
GROUP BY [Date]

Answer 3

或者。。。

select Date , t1.Price as Stock_1_Price , t2.Price as Stock_3_price
from      ( select "Date" , max(Price) as Price from myData where ID = 1 group by "Date" ) t1
full join ( select "Date" , max(Price) as Price from myData where ID = 3 group by "Date" ) t2 on t2.Date = t1.Date

至于填充临时表，任何常用的方法都可以：

• 表变量：

  declare @work table
  (
    yyyymmdd      varchar(32) not null ,
    stock_1_price money null ,
    stock_3_price money null
  )
  insert @work ( yyyymmdd , stock_1_price , stock_3_price )
  select Date , t1.Price as Stock_1_Price , t2.Price as Stock_3_price
  from      ( select "Date" , max(Price) as Price from myData where ID = 1 group by "Date" ) t1
  full join ( select "Date" , max(Price) as Price from myData where ID = 3 group by "Date" ) t2 on t2.Date = t1.Date
  

• tempdb 中声明的临时表

  create table #work
  (
    yyyymmdd      varchar(32) not null primary key clustered ,
    stock_1_price money null ,
    stock_3_price money null
  )
  insert #work ( yyyymmdd , stock_1_price , stock_3_price )
  select Date , t1.Price as Stock_1_Price , t2.Price as Stock_3_price
  from      ( select "Date" , max(Price) as Price from myData where ID = 1 group by "Date" ) t1
  full join ( select "Date" , max(Price) as Price from myData where ID = 3 group by "Date" ) t2 on t2.Date = t1.Date
  

• 通过select into:在tempdb中不声明临时表

  select Date , t1.Price as Stock_1_Price , t2.Price as Stock_3_price
  into #work
  from      ( select "Date" , max(Price) as Price from myData where ID = 1 group by "Date" ) t1
  full join ( select "Date" , max(Price) as Price from myData where ID = 3 group by "Date" ) t2 on t2.Date = t1.Date