實務常見場景:主檔 A (廠商)、交易主檔 B (單據)、交易明細 C (品項)。需依 **單號 (DOCNO)**、**條碼 (PLU)**、**日期 (CDATE)** 彈性查詢,並顯示廠商名稱。本文提供兩種 SQL 寫法與參數化 ASP 實作。
資料結構
| 表 | 欄位 | 說明 |
| A: 廠商 | VENID | 廠商代碼 (PK) |
| VENNAME | 廠商名稱 |
| B: 單據主檔 | DOCNO | 單號 (PK) |
| VENID | 廠商代碼 (FK→A) |
| CDATE | 單據日期 |
| C: 明細檔 | DOCNO | 單號 (FK→B) |
| PLU | 品項條碼 |
| STORY | 數量/金額 |
期望輸出
單號(DOCNO) 廠商名稱(VENNAME) 日期(CDATE)
20090001 公司1 2009-01-01
20090002 公司2 2009-01-02
20090003 公司1 2009-01-03
方法一:子查詢 + IN (適用 Access/SQL Server/Oracle)
SELECT DISTINCT
B.DOCNO,
A.VENNAME,
B.CDATE
FROM
A
INNER JOIN B ON A.VENID = B.VENID
WHERE
B.DOCNO IN (
SELECT DOCNO FROM C
WHERE DOCNO = ? -- 單號查詢
OR PLU = ? -- 條碼查詢
)
ORDER BY B.CDATE DESC
方法二:EXISTS 相關子查詢 (效能通常優於 IN)
SELECT
B.DOCNO,
A.VENNAME,
B.CDATE
FROM
A
INNER JOIN B ON A.VENID = B.VENID
WHERE
EXISTS (
SELECT 1 FROM C
WHERE C.DOCNO = B.DOCNO
AND (C.DOCNO = ? OR C.PLU = ?)
)
ORDER BY B.CDATE DESC
日期區間查詢 (方法二擴充)
-- Access 日期格式需用 #yyyy-mm-dd#
WHERE EXISTS (
SELECT 1 FROM C
WHERE C.DOCNO = B.DOCNO
)
AND B.CDATE BETWEEN #?# AND #?# -- 參數化日期
-- SQL Server
WHERE EXISTS (...)
AND B.CDATE BETWEEN ? AND ? -- 參數化日期 (建議傳 Date 型別)
-- Oracle
WHERE EXISTS (...)
AND B.CDATE BETWEEN TO_DATE(?, 'YYYY-MM-DD') AND TO_DATE(?, 'YYYY-MM-DD')
ASP 參數化實作 (ADODB.Command)
<%@ Language=VBScript CodePage=65001 %><%
Option Explicit
Dim conn, cmd, rs, sql, docno, plu, dateFrom, dateTo
docno = Trim(Request("docno"))
plu = Trim(Request("plu"))
dateFrom = Trim(Request("dateFrom"))
dateTo = Trim(Request("dateTo"))
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=.;Initial Catalog=ERP;User ID=web;Password=****;"
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandType = 1 ' adCmdText
'--- 動態建構 WHERE ---
Dim whereClauses, params
whereClauses = ""
params = Array()
If docno <> "" Then
whereClauses = whereClauses & " AND B.DOCNO = ?"
ReDim Preserve params(UBound(params)+1)
params(UBound(params)) = docno
End If
If plu <> "" Then
whereClauses = whereClauses & " AND EXISTS (SELECT 1 FROM C WHERE C.DOCNO = B.DOCNO AND C.PLU = ?)"
ReDim Preserve params(UBound(params)+1)
params(UBound(params)) = plu
End If
If dateFrom <> "" And dateTo <> "" Then
whereClauses = whereClauses & " AND B.CDATE BETWEEN ? AND ?"
ReDim Preserve params(UBound(params)+1) : params(UBound(params)) = dateFrom
ReDim Preserve params(UBound(params)+1) : params(UBound(params)) = dateTo
End If
sql = "SELECT B.DOCNO, A.VENNAME, B.CDATE " & _
"FROM A INNER JOIN B ON A.VENID = B.VENID " & _
"WHERE 1=1 " & whereClauses & " " & _
"ORDER BY B.CDATE DESC"
cmd.CommandText = sql
'--- 綁定參數 (順序必須與 SQL 中 ? 順序一致) ---
Dim i
For i = 0 To UBound(params)
cmd.Parameters.Append cmd.CreateParameter("@p" & i, 200, 1, 50, params(i)) ' adVarChar
Next
Set rs = cmd.Execute
'--- 輸出 HTML 表格 ---
Response.Write "| 單號 | 廠商 | 日期 |
"
Do Until rs.EOF
Response.Write "| " & rs("DOCNO") & " | " & Server.HTMLEncode(rs("VENNAME")) & " | " & rs("CDATE") & " |
"
rs.MoveNext
Loop
Response.Write "
"
rs.Close: Set rs = Nothing
Set cmd = Nothing
conn.Close: Set conn = Nothing
%>
索引建議 (關鍵效能)
-- B 表
CREATE INDEX IX_B_VENID ON B(VENID);
CREATE INDEX IX_B_CDATE ON B(CDATE);
-- C 表
CREATE INDEX IX_C_DOCNO ON C(DOCNO);
CREATE INDEX IX_C_PLU ON C(PLU);
CREATE INDEX IX_C_DOCNO_PLU ON C(DOCNO, PLU); -- 複合索引支援聯查
分頁擴充 (SQL Server 2012+)
sql = sql & " OFFSET " & ((page-1)*pageSize) & " ROWS FETCH NEXT " & pageSize & " ROWS ONLY;"
關鍵提醒:
1. **永不拼接 SQL** — 所有使用者輸入 (`docno`、`plu`、`dateFrom`、`dateTo`) 必須透過 `Command.Parameters` 綁定。
2. `DISTINCT` vs `EXISTS` — `EXISTS` 通常效能較好,且不會因 C 表重複 DOCNO 產生重複列。
3. 日期參數 — Access 用 `#yyyy-mm-dd#`,SQL Server/Oracle 直接傳 `Date` 型別參數最安全。
4. 索引 — `C(DOCNO, PLU)` 複合索引可大幅加速 `EXISTS` 子查詢。