SQL Server 选择所需存储过程的查询帮助 [英] SQL server select query help for stored procedure needed

问题描述

我正在努力解决在存储过程中构建 sql 选择查询所需的逻辑.我的数据库适用于股票市场数据.我有一个 2012 年的交易日期表，一个股票代码表，以及这些代码和交易日期通过网络服务的每日定价数据表.我正在尝试从我正在努力解决的选择查询中获取用组件编写的 Web 服务地址字符串.

I'm struggling with the logic required to structure a sql select query in a stored procedure. My database works with stock market data. I have a table of trading dates for 2012, a table of ticker symbols, and a table of daily pricing data for those symbols and trade dates via a web service. I'm trying to get web service address strings written with components from my select query I'm struggling with.

下面的第一个程序运行良好，可以返回包含符号、开始日期、结束日期"字段的记录，其中开始日期和结束日期表示每个符号所需的最早和最新缺失数据点:

The first procedure below works well enough to return records that have the fields "symbol, start date, end date" with start date and end date representing the earliest and latest missing data points that I need for each symbol:

ALTER PROCEDURE dbo.sprocSymbsDatesForHistoricalPricingVoll

AS

DECLARE @NxtAvailableDataDownloadDate date

SET @NxtAvailableDataDownloadDate = dbo.NextAvailableDataDownloadDate()

SELECT Symbol, MIN(TradingDate), Max(TradingDate)
FROM (SELECT Symbol, TradingDate
FROM (SELECT tblSymbolsMain.Symbol, tblTradingDays.TradingDate
      FROM tblSymbolsMain CROSS JOIN
      tblTradingDays
      WHERE (tblTradingDays.TradingDate <= @NxtAvailableDataDownloadDate)) AS T1
      WHERE (NOT EXISTS
             (SELECT TradeDate, Symbol
              FROM tblDailyPricingAndVol
              WHERE (TradeDate = T1.TradingDate) AND (Symbol = T1.Symbol)))) t GROUP BY Symbol ORDER BY Symbol

这可以很好地交叉连接所有交易日期的所有股票代码，并返回不在我的定价表中的那些.美好的.我的问题是，虽然我可能会返回一行，该行为我提供了该表中每个我需要的最小和最大日期:

This does an ok job of cross- joining all stock symbols with all trading dates, and returning the ones that aren't in my pricing table. Fine. My problem is, while I may get back a row that gives me the min and max dates from that table per symbol that I need as such:

AAPL, 1/1/12, 1/10/12

我的定价表中可能已经有该日期范围内的一些必需数据点，例如:

I might already have in my pricing table some of the required data points within that date range, such as:

AAPL, 1/5/12- 1/9/12

在这种情况下我想得到的是:

What I would like to get back in this case would be:

AAPL, 1/1/12, 1/4/12
AAPL, 1/10/12, 1/10/12

而不是上面带有股票代码的第一个示例.我可以通过以程序代码方式循环来轻松完成此操作，但我希望在 db 端实现这一点.有任何想法吗?提前致谢...

Instead of the first example with stock symbol above. I could do this easily by looping in a procedural code fashion but I'd like to see this implemented on the db side. Any ideas? Thanks in advance...

推荐答案

递归就是你的答案.以下是如何获取内部选择语句并将其转换为范围行.递归在 SQL 2005 或更高版本中可用，所以我希望对你有用.

Recursion is your answer. Here's how you can take your inner select statement and transform it into range rows. Recursion is available in SQL 2005 or above so I hope that works for you.

create table dates (dt datetime  null)
go
insert into dates(dt) values('1/1/2012')
insert into dates(dt) values('1/2/2012')
insert into dates(dt) values('1/3/2012')
insert into dates(dt) values('1/4/2012')
insert into dates(dt) values('1/10/2012')
go

--Show raw data
select * from dates
go

--Define the recursive query
WITH RecursiveQuery (anchorDate, dt)
AS
(
    -- Anchor member of recursive query
    SELECT 
        anchorDate=dt,
        dt
    FROM 
        dates AS e
    WHERE 
        NOT EXISTS(select 1 from dates where dateadd(day,-1,e.dt)=dt)
    UNION ALL
    -- Recursive member (notice join to RecursiveQuery)
    SELECT 
        anchorDate=d.anchorDate,
        e.dt
    FROM 
        dates AS e
        INNER JOIN RecursiveQuery AS d ON e.dt = dateadd(day,1,d.dt)
)

--Final Join
select 
    startdt=anchorDate,enddt=max(dt) 
FROM 
    RecursiveQuery 
group by anchorDate
order by anchorDate

这篇关于SQL Server 选择所需存储过程的查询帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！