SQL脚本积累之一-----UNION的使用示例

最新推荐文章于 2026-06-22 20:28:17 发布

原创最新推荐文章于 2026-06-22 20:28:17 发布 · 503 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#sql

SQL Server数据库专栏收录该内容

6 篇文章

订阅专栏

本博客展示了如何使用SQL查询来筛选特定价格区间内的市场数据，包括成交价低于当日行情最低价、高于最高价及不在五档行情中的交易记录。

查询【成交价】小于当日行情中的显示的【最低价】

SELECT [SpotCode],MinPrice,[Code],LPrice 
FROM (SELECT [SpotCode],Min([TradePrice]) MinPrice,Max([TradePrice]) MaxPrice 
        FROM [VTS_ReckoningCounter_4.5].[dbo].[XH_TodayTradeTable]
        where [TradePrice]>0
        group by SpotCode) tempA,
     (SELECT [Code],Max([High]) HPrice,Min([Low]) LPrice    
        FROM [MRS].[dbo].[SHL1_MarketInfo_20120130]
        where [Low]>0 
        and [High]>0
        group by [Code]
      union 
      SELECT [Code],Max([High]) HPrice,Min([Low]) LPrice    
        FROM [MRS].[dbo].[SZL1_MarketInfo_20120130] 
        where [Low]>0 
        and [High]>0
        group by [Code]) tempB 
where tempA.[SpotCode]=tempB.[Code]
and tempA.MinPrice<tempB.LPrice

查询【成交价】高于当日行情中的显示的【最高价】

SELECT [SpotCode],MaxPrice,[Code],HPrice 
FROM (SELECT [SpotCode],Min([TradePrice]) MinPrice,Max([TradePrice]) MaxPrice 
        FROM [VTS_ReckoningCounter_4.5].[dbo].[XH_TodayTradeTable]
        where [TradePrice]>0
        group by SpotCode) tempA,
     (SELECT [Code],Max([High]) HPrice,Min([Low]) LPrice    
        FROM [MRS].[dbo].[SHL1_MarketInfo_20120130]
        where [Low]>0 
        and [High]>0
        group by [Code]
      union 
      SELECT [Code],Max([High]) HPrice,Min([Low]) LPrice    
        FROM [MRS].[dbo].[SZL1_MarketInfo_20120130] 
        where [Low]>0 
        and [High]>0
        group by [Code]) tempB 
where tempA.[SpotCode]=tempB.[Code]
and tempA.MaxPrice>tempB.HPrice

查询成交价不在五档行情中

SELECT tempD.[EntrustNumber]
FROM (SELECT  count([EntrustNumber]) num,[EntrustNumber]
        FROM [VTS_ReckoningCounter_4.5].[dbo].[XH_TodayTradeTable] tempA,
             [MRS].[dbo].SZL1_MarketInfo_20120201 tempB
        where tempA.[SpotCode] = tempB.Code
          and tempA.[TradePrice]>0
          and tempB.MarketRefreshTime > dateadd(mi,-10,tempA.TradeTime) 
          and tempB.MarketRefreshTime < dateadd(mi,+10,tempA.TradeTime) 
          and tempA.[BuySellTypeId]=1  
        group by [EntrustNumber]
        union 
     SELECT count([EntrustNumber]) num,[EntrustNumber]
        FROM [VTS_ReckoningCounter_4.5].[dbo].[XH_TodayTradeTable] tempA,
             [MRS].[dbo].SHL1_MarketInfo_20120201 tempC 
          where tempA.[SpotCode]= tempC.Code
          and tempA.[TradePrice]>0
          and tempC.MarketRefreshTime > dateadd(mi,-10,tempA.TradeTime) 
          and tempC.MarketRefreshTime < dateadd(mi,+10,tempA.TradeTime) 
          and tempA.[BuySellTypeId]=1 
        group by [EntrustNumber]) tempD,
    (SELECT  count([EntrustNumber]) num,[EntrustNumber]
            FROM [VTS_ReckoningCounter_4.5].[dbo].[XH_TodayTradeTable] tempA,
                 [MRS].[dbo].SZL1_MarketInfo_20120201 tempB
            where tempA.[SpotCode] = tempB.Code
              and tempA.[TradePrice]>0
              and tempA.[TradePrice] not in(tempB.lastprice,tempB.[SellFirstPrice],tempB.[SellSecondPrice],tempB.[SellThirdPrice],tempB.[SellFourthPrice],tempB.[SellFivePrice])
              and tempB.MarketRefreshTime > dateadd(mi,-10,tempA.TradeTime) 
              and tempB.MarketRefreshTime < dateadd(mi,+10,tempA.TradeTime) 
              and tempA.[BuySellTypeId]=1  
            group by [EntrustNumber]
            union 
         SELECT count([EntrustNumber]) num,[EntrustNumber]
            FROM [VTS_ReckoningCounter_4.5].[dbo].[XH_TodayTradeTable] tempA,
                 [MRS].[dbo].SHL1_MarketInfo_20120201 tempC 
              where tempA.[SpotCode]= tempC.Code
              and tempA.[TradePrice]>0
              and tempA.[TradePrice] not in(tempC.lastprice,tempC.[SellFirstPrice],tempC.[SellSecondPrice],tempC.[SellThirdPrice],tempC.[SellFourthPrice],tempC.[SellFivePrice])
              and tempC.MarketRefreshTime > dateadd(mi,-10,tempA.TradeTime) 
              and tempC.MarketRefreshTime < dateadd(mi,+10,tempA.TradeTime) 
              and tempA.[BuySellTypeId]=1 
            group by [EntrustNumber]) tempE
where tempD.[EntrustNumber]=tempE.[EntrustNumber]
and tempD.num=tempE.num

查询成交价不在五档行情中的SQL语句—-历史成交表与撮合表

SELECT tempD.[EntrustNumber] 
FROM (SELECT  count([EntrustNumber]) num,[EntrustNumber]
        FROM [VTS_ReckoningCounter_4.5].[dbo].[XH_HistoryTradeTable] tempA,
             [VTS_MatchCenter_4.5].[dbo].[XH_MarketInfo] tempB
        where tempA.[SpotCode] = tempB.Code
        and tempA.[TradeTime] > '2012-01-30 18:30:22.920'
        and tempA.[TradePrice]>0
        and tempA.[TradePrice] not in(tempB.[lastprice],tempB.[SellPrice1],tempB.[SellPrice2],tempB.[SellPrice3],tempB.[SellPrice4],tempB.[SellPrice5])
        and tempB.[MarketTime]>dateadd(mi,-5,tempA.TradeTime) 
        and tempB.[MarketTime]<dateadd(mi,+5,tempA.TradeTime) 
        group by [EntrustNumber]) tempC,
    (SELECT count([EntrustNumber]) num,[EntrustNumber]
        FROM [VTS_ReckoningCounter_4.5].[dbo].[XH_HistoryTradeTable] tempA,
             [VTS_MatchCenter_4.5].[dbo].[XH_MarketInfo] tempB
        where tempA.[SpotCode] = tempB.Code
        and tempA.[TradeTime] > '2012-01-30 18:30:22.920'
        and tempA.[TradePrice]>0
        and tempB.[MarketTime]>dateadd(mi,-5,tempA.TradeTime) 
        and tempB.[MarketTime]<dateadd(mi,+5,tempA.TradeTime) 
        group by [EntrustNumber]) tempD
where tempC.[EntrustNumber]=tempD.[EntrustNumber]
and tempC.num = tempD.num