在表中保存查询 [英] save a query in table

问题描述

嗨

;  WITH  cte

 AS 

(
选择

ID，

((((DATEPART(hour，CAST([时间]  AS   DATETIME ))*  60 )+(DATEPART(分钟，CAST([时间]  AS   DATETIME ))))))) AS  [时间]，

价格，

ROW_NUMBER() OVER ( PARTITION   BY ( (((DATEPART(小时，CAST([时间]  AS   DATETIME ))*  60 )+(DATEPART(分钟，CAST([时间]  AS   DATETIME ))))))))) ORDER   BY  CAST(时间  AS   DATETIME ) ASC ) AS  rn_1，

ROW_NUMBER() OVER ( PARTITION   BY ( (((DATEPART(小时，CAST([时间]  AS   DATETIME ))*  60 )+(DATEPART(分钟，CAST([时间]  AS   DATETIME ))))))))) ORDER   BY  CAST(时间  AS  日期时间) DESC ) AS  rn_2

 FROM 
表格1
位置

id = '  1';)


选择

max(id) as  id，

MAX(LTRIM([时间]/ 60 )+ '  :' + LTRIM([时间]％ 60 )) AS  [时间]，

MAX(案例 何时 rn_1 =  1   and  rn_2！= 1  THEN 价格 ELSE   NULL   END ) AS  [打开]，

MAX(价格) AS 高，

MIN(price) AS 低，

MAX(案例 何时 rn_2 =  1   THEN 价格 ELSE   NULL   END )关闭]

 FROM 

CTE

组  by 
ID，
[时间]
顺序 依据
[时间] 

我想将此查询保存到新表中，但我不知道怎么办?当然，我该如何向该查询发送参数以获取ID?
我的桌子必须像这样:

id时间打开高低关闭
1 10:05 22 33 23 23

非常感谢

解决方案

要将结果存储在新表中，可以使用INSERT INTO..SELECT结构.

例如:

  INSERT   INTO  TargetTableName(Col1，Col2等)
选择 Col1，Col2，...
 FROM  SourceTable 

对于实际情况，您将使用查询和正确的目标表.

-那里有两个查询语句.这两者之间是什么关系?
-将"ID"参数替换为"@ID"，然后使用语句对象的parameters属性添加值.请参见 MSDN
-将结果存储在新表中(这将创建一个新表):

  SELECT 字段1，字段2，字段3
 INTO  newtable
 FROM 
(子查询) as  sq 

hi

;WITH cte

AS

(
SELECT

id,

(((DATEPART(hour, CAST([time] AS DATETIME)) * 60) + (DATEPART(minute, CAST([time] AS DATETIME)) ) )) AS [time],

price,

ROW_NUMBER() OVER(PARTITION BY (((DATEPART(hour, CAST([time] AS DATETIME)) * 60) + (DATEPART(minute, CAST([time] AS DATETIME)) ) )) ORDER BY CAST(Time AS DATETIME) ASC) AS rn_1,

ROW_NUMBER() OVER(PARTITION BY (((DATEPART(hour, CAST([time] AS DATETIME)) * 60) + (DATEPART(minute, CAST([time] AS DATETIME)) ) )) ORDER BY CAST(Time AS DATETIME) DESC) AS rn_2

FROM
Table_1
WHERE

id='1';)


SELECT

max(id) as id,

MAX (LTRIM([Time] / 60) + ':' + LTRIM([Time] % 60)) AS [Time],

MAX(CASE WHEN rn_1 = 1 and rn_2 !=1 THEN price ELSE NULL END) AS [Open],

MAX(price) AS High,

MIN(price) AS Low,

MAX(CASE WHEN rn_2 = 1 THEN price ELSE NULL END) AS [Close]

FROM

cte

group by
id,
[time]
order by
[Time]

i want to save this query in a new table but i do not know how? and of course how i can to send a Parameter to this query for id ?
my table have to be like this for example:

id time open high low close
1 10:05 22 33 23 23

thanks a lot

解决方案

To store the results in a new table you can use INSERT INTO..SELECT structure.

For example:

INSERT INTO TargetTableName (Col1, Col2, ...)
SELECT Col1, Col2, ...
FROM SourceTable

For the actual situation you would use your query and the correct target table.

- You have two query statements there. What is the relation between these two?
- Replace your "ID" parameter with "@ID", and use the parameters property of the statement object to add value. See MSDN
- Storing result in a new table (this will create a new table):

SELECT field1, field2, field3
INTO newtable
FROM
( subquery ) as sq

这篇关于在表中保存查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！