我有以下查詢，它會吐出一些值，我需要對其進行基本修改，以便我可以根據選擇查詢的輸出中是否表示 Ticker 符號和日期從源表中洗掉一些行。這是查詢。
;with cte_tenPct as (
select exchange, ticker
, CurrentDate = date, Prior_Date = (lag(date) over(partition by ticker order by date))
, Current_open = [open] , Prior_open = (lag([open]) over(partition by ticker order by date))
, Current_high = [high] , Prior_high = (lag([high]) over(partition by ticker order by date))
, Current_low = [low] , Prior_low = (lag([low]) over(partition by ticker order by date))
, Current_close = [close] , Prior_close = (lag([close]) over(partition by ticker order by date))
from dbo.V with (nolock)
)
select * from cte_tenPct
where (Current_open <> 0 and Current_open = Prior_open / 10)
and (Current_high <> 0 and Current_high = Prior_high / 10)
and (Current_low <> 0 and Current_low = Prior_low / 10)
and (Current_close <> 0 and Current_close = Prior_close / 10)
這是結果（只是相關部分）
因此，我想基本上以以下方式開始查詢： DELETE FROM V where V.Ticker = cte_tenPct.Ticker and V.Date = cte_tenPct.CurrentDate IN --然后我將在下面進行選擇查詢。有人可以幫我解決語法。謝謝
uj5u.com熱心網友回復：
;with cte_tenPct as (
select exchange, ticker
, CurrentDate = date, Prior_Date = (lag(date) over(partition by ticker order by date))
, Current_open = [open] , Prior_open = (lag([open]) over(partition by ticker order by date))
, Current_high = [high] , Prior_high = (lag([high]) over(partition by ticker order by date))
, Current_low = [low] , Prior_low = (lag([low]) over(partition by ticker order by date))
, Current_close = [close] , Prior_close = (lag([close]) over(partition by ticker order by date))
from dbo.V with (nolock)
)
delete from cte_tenPct
where (Current_open <> 0 and Current_open = Prior_open / 10)
and (Current_high <> 0 and Current_high = Prior_high / 10)
and (Current_low <> 0 and Current_low = Prior_low / 10)
and (Current_close <> 0 and Current_close = Prior_close / 10)
基本上你只需要洗掉而不是選擇 cte 結果。
當然，您可以洗掉 cte 結果中存在的所有條目，也可以使用 where 子句過濾哪些條目將被洗掉。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/468907.html
標籤：sql
下一篇：Gradle下載舊的編譯依賴項