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

sql mysql database 資料庫 oracle

jhik 2010-05-04 10:29:19 ‧ 9136 瀏覽

分享至

各位高手,
我用這種寫法很久了,想問有沒有什麼更好寫法,這樣的寫法會不會太笨了點?請高手指點
說明:
我要從多個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出所需的資料
這就是我處理需多次計算才能得到結果的語法
各位是否有更好的做法?

謝謝

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

pojen

iT邦研究生 5 級 ‧ 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