萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP 程式設計

ASP 多表聯查 SQL 語句實戰:三表關聯 (A/B/C) 依單號/條碼/日期查詢

實務常見場景:主檔 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.MoveNext
Loop
Response.Write "
單號廠商日期
" & rs("DOCNO") & "" & Server.HTMLEncode(rs("VENNAME")) & "" & rs("CDATE") & "
" 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` 子查詢。
copyright © 萬盛學電腦網 all rights reserved