특정시간 후 1% 이상 오른 종목

1. 13시간 전 종목별 거래

SELECT B.*
FROM StockPrice AS B, [SELECT MAX(IID) AS AB
  FROM (SELECT IID, Code FROM StockPrice WHERE DATEDIFF("h",DateTime,NOW())=13)
  GROUP BY Code
]. AS C
WHERE B.IID = C.AB;

 

2. 종목별 최근(최종) 거래

SELECT SP.*
FROM StockPrice AS SP, [SELECT MAX(IID) AS MAXIID FROM StockPrice GROUP BY Code; ] AS PT
WHERE SP.IID=PT.MAXIID;

 

3. 특정시간 후 1% 이상 오른 종목  (1 + 2 = 3)

SELECT X.*, Y.Diff AS ['13시간전 증가율'], (X.Diff-Y.Diff) AS 증가율
FROM [SELECT SP.* FROM StockPrice AS SP,
  (SELECT MAX(IID) AS MAXIID FROM StockPrice GROUP BY Code) AS PT
  WHERE SP.IID = PT.MAXIID
]. AS X, [SELECT B.*  FROM StockPrice AS B, (SELECT MAX(IID) AS AB FROM (SELECT IID, Code FROM StockPrice WHERE DATEDIFF("h",DateTime,NOW())=13) GROUP BY Code) AS C WHERE B.IID = C.AB]. AS Y
WHERE (((X.Code)=[Y].[Code]) AND (([X].[Diff]-[Y].[Diff])>1) AND (X.Diff > 0) AND ((X.Volume)>500000))
ORDER BY (X.Diff-Y.Diff) DESC;
/* 13시간 전보다 1%이상 증가 AND 거래량 500,000 이상 AND 최근 거래 증가율 0%이상 */ 

Code : 종목코드
DIff : 증가율
Volume : 거래량
IID : 일련번호