iT邦幫忙

0

SQL 語法這樣寫效能好嗎?有沒有更好的做法?

jhik 2010-05-04 10:29:197313 瀏覽

各位高手,
我用這種寫法很久了,想問有沒有什麼更好寫法,這樣的寫法會不會太笨了點?請高手指點
說明:
我要從多個TABLE中取出並計算後集中在一個TABLE供我取資料,重點在於多個TABLE計算後的結果無法一次算完,需要分開算,內容如下:

declare @a table (mk_no decimal(8) , mk_qty decimal(8))
insert @a
Select COUNT(DISTINCT a.tran_no) AS trancnt, SUM(B.tran_qty) AS tranqty
From ((dx79 AS b left join dx78 as a on a.tran_no = b.tran_no)left join dx11 on dx11.isbn = b.isbn )left join x220 on b.isbn = x220.isbn
Where CONVERT(varchar(12) , a.tran_dt, 111 ) Between LEFT(CONVERT(varchar(12) , getdate(), 111 ),8) + '01' AND CONVERT(varchar(12) , getdate(), 111 ) Group by CONVERT(varchar(12) , a.tran_dt, 111 )

declare @b table(mk_no decimal(8))
insert @b
Select COUNT(B.tran_qty) AS tranqty
From ((dx79 AS b left join dx78 as a on a.tran_no = b.tran_no)left join dx11 on dx11.isbn = b.isbn )left join dx20 on b.isbn = dx20.isbn
Where CONVERT(varchar(12) , a.tran_dt, 111 ) Between LEFT(CONVERT(varchar(12) , getdate(), 111 ),8) + '01' AND CONVERT(varchar(12) , getdate(), 111 ) and b.tran_qty > 0
Group by CONVERT(varchar(12) , a.tran_dt, 111 )

以上我有了@a的mk_no mk_qty 及 @b的mk_no
於是我從@a中及@b中再Select出所需的資料
這就是我處理需多次計算才能得到結果的語法
各位是否有更好的做法?

謝謝

1 個回答

14
pojen
iT邦新手 2 級 ‧ 2010-05-04 12:25:51
最佳解答

或許可以試試 CTE?

declare @tran_dt_beg datetime
declare @tran_dt_end datetime
select @tran_dt_beg = getdate(), @tran_dt_end = dateadd(day,1,getdate())
with aa as
(Select COUNT(DISTINCT a.tran_no) AS trancnt, SUM(B.tran_qty) AS tranqty From dx79 AS b
left join dx78 as a on a.tran_no = b.tran_no
left join dx11 on dx11.isbn = b.isbn <- 不清楚
left join x220 on b.isbn = x220.isbn <- 不清楚
Where a.tran_dt Between @tran_dt_beg and @tran_dt_end
Group by CONVERT(varchar(10) , a.tran_dt, 101 ) )
, bb as (
Select COUNT(B.tran_qty) AS tranqty
From dx79 AS b
left join dx78 as a on a.tran_no = b.tran_no
left join dx11 on dx11.isbn = b.isbn <- 不清楚
left join dx20 on b.isbn = dx20.isbn <- 不清楚
Where a.tran_dt Between @ tran_dt_beg AND @ tran_dt_beg
and b.tran_qty > 0
Group by CONVERT(varchar(10) , a.tran_dt, 101 )
)
這裡寫最後的 SELECT

@table 的話要注意 result 大小, 一般來說超過 1k 行會建議使用 #table. (port 完後記得順便記得建 index). Convert 的話只是我個人喜好. 如果想要只在 dx11/dx20 的話, 我會建議使用 exists.

我要發表回答

立即登入回答