[SQL ] 請問這樣的語法能再改成有效率的嗎?
各位大大好:因為我用linked server的方式將二個資料庫連結,
這樣語法就能結何成一起用,並且我用了檢示表的方式來作,結果
發生了二台不同的機器,效能差很多,a機器2秒,b機器要1分鐘
不過想先請教各位,我的語法能再調整嗎?
vc <---檢示表名
Select a.學期, a.學號, a.課程代碼, a.課程班級, b.老師編號, b.老師姓名
From 學生選修表 as a
Inner Join 教師教授表 as b on
a.課程代碼 = b.課程代碼 And
a.課程班級 = b.課程班級 And
a.學期 = b.學期
Inner Join 課程名稱表 as c on
a.課程代碼 = c.課程代碼
Where (a.課程代碼 like '%900%') and (c.課程名稱 like '%國語文%')
//////以下有結合上面的檢示表
vt <----檢示表名
SELECT sd.學號 AS std_no, sd.學期 AS smtr, sd.老師編號, sb.學生姓名,
(SELECT CASE WHEN SUM(sm.修課時數) IS NULL THEN 0 ELSE SUM(sm.修課時數
) END AS Expr1
FROM 修課記錄 AS sm
WHERE (學號 = sd.學號) AND (學期 = sd.學期) AND (導師認可 = 1) AND (主
任認可 = 1)) AS hr,
(SELECT cOUNT(學號) AS Expr1
FROM 修課記錄 AS sm
WHERE (學號 = sd.學號) AND (學期 = sd.學期) AND
(導師認可 = 1) AND (主任認可 = 1)) AS stdcount
FROM VC AS sd
LEFT OUTER JOIN 學生資料表 AS sb ON sd.學號 = sb.學號
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 220.134.121.234
→
09/29 10:02, , 1F
09/29 10:02, 1F
→
09/29 12:41, , 2F
09/29 12:41, 2F
→
09/30 08:15, , 3F
09/30 08:15, 3F
→
09/30 08:15, , 4F
09/30 08:15, 4F
→
09/30 08:15, , 5F
09/30 08:15, 5F
→
09/30 08:16, , 6F
09/30 08:16, 6F
→
09/30 20:21, , 7F
09/30 20:21, 7F
→
10/01 03:23, , 8F
10/01 03:23, 8F
→
10/01 03:24, , 9F
10/01 03:24, 9F
→
10/01 03:26, , 10F
10/01 03:26, 10F
→
10/01 03:27, , 11F
10/01 03:27, 11F
→
10/01 22:44, , 12F
10/01 22:44, 12F
→
10/01 22:44, , 13F
10/01 22:44, 13F