snoopy11hk

如果oracle 咁都叫慢, 其他可以唔洗用.
奇雲 發表於 2015-12-7 17:04

    小弟不才, 現拋磚引玉
OPEN CURSOR FOR
SELECT "DATE", "ID", "Number"
FROM
(
SELECT "DATE", "ID", "Number",
LAG("DATE") OVER (ORDER BY "NUMBER" ASC) PREVIOUS_DATE,
LAG("ID") OVER (ORDER BY "NUMBER" ASC) PREVIOUS_ID,
LAG("NUMBER") OVER (ORDER BY "NUMBER" ASC) PREVIOUS_NUMBER
FROM tb1
WHERE  "DATE"  BETWEEN :START_DATE AND :END_DATE
)
WHERE LNNVL("DATE" = PREVIOUS_DATE) OR  LNNVL("ID" = PREVIOUS_ID) OR LNNVL("NUMBER" =PREVIOUS_NUMBER)

接近(03-DEC-15 的這個日子)的出來, 再用024-645073去碰下024-645074, 呢句唔多明
大約係 lag function 補 CASE WHEN 就可以了

董事長

樓主題目寫
024-645073 下面無record 就搵同一佪ID 而時間最接近start_date做比較

咁結果點解會係 024-645073 同 024-645074 比較?
024-645074 個ID 係 A25458 並與 A2000 不同
024-645073 同ID 時間最接新3-DEC-15 應該係 024-645072
睇黎睇去佢都只係想響相同date range下搵不同既number

拋豆腐引樓主
SELECT DISTINCT date, id, number
FROM   tb1
WHERE DATE BETWEEN to_date(:start_date,'DD-MON-YYYY')
               AND to_date(:end_date,'DD-MON-YYYY')
ORDER BY date;

7h1r733n

小弟不才, 現拋磚引玉
OPEN CURSOR FOR
SELECT "DATE", "ID", "Number"
FROM
(
SELECT "DATE", "ID ...
snoopy11hk 發表於 2015-12-7 21:35

我想寫個stored procedure 是當我輸入date range之後, 去search date range 得出來的3個records 每個record 去比較

好在而家唔係考試..

杜龍

Dear Sir
this is mock version..
And I am happy to offer the full working version for $500 HKD
Best Regards
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT  ROW_NUMBER() over (order by nbr ) as _id,
     date, [id]
      ,[nbr]
  FROM [test].[dbo].[testtable] tbl1
select exist(

  SELECT  ROW_NUMBER() over (order by nbr ) -1 as _id,
     date, [id]
      ,[nbr]
  FROM [test].[dbo].[testtable] tbl2
where tbl1._id = tbl2._id and tbl1.nbr <> tbl2.nbr)

henrywho

研究左兩日還未研究到
save_null 發表於 2015-12-7 08:28

我完全睇唔明個 requirement.