0

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

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

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 )

### 1 個回答

14
pojen
iT邦新手 1 級 ‧ 2010-05-04 12:25:51

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 )
)

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