萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 基礎語法實戰(二):表復制、跨庫拷貝、子查詢、分頁、分組統計

1. 僅復制表結構(不含數據)

方法一:WHERE 1<>1

SELECT * INTO NewTable FROM SourceTable WHERE 1 <> 1

方法二:TOP 0(更直觀)

SELECT TOP 0 * INTO NewTable FROM SourceTable

兩種方式均創建相同列定義、NULL屬性、標識屬性的空表,不復制索引、約束、觸發器、權限。

2. 復制表數據(表已存在)

INSERT INTO TargetTable (Col1, Col2)
SELECT Col1, Col2 FROM SourceTable

列名可省略(順序、數量、類型完全匹配時)。

3. 跨數據庫表數據拷貝

-- 三段式命名:[LinkedServer.]Database.Schema.Table
INSERT INTO TargetDB.dbo.TargetTable (Col1)
SELECT Col1 FROM SourceDB.dbo.SourceTable

若涉及鏈接服務器:INSERT INTO [LinkedSrv].TargetDB.dbo.Tbl ...

4. 子查詢應用

標量子查詢(SELECT 列表)

SELECT OrderID, 
       (SELECT CustName FROM Customers c WHERE c.CustID = o.CustID) AS Customer
FROM Orders o

相關子查詢(WHERE/HAVING)

-- 查詢每個客戶的最大訂單
SELECT * FROM Orders o
WHERE OrderAmount = (SELECT MAX(OrderAmount) FROM Orders WHERE CustID = o.CustID)

EXISTS / NOT EXISTS(性能優於 IN/NOT IN)

-- 有訂單的客戶
SELECT * FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustID = c.CustID)

-- 無訂單客戶
SELECT * FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustID = c.CustID)

5. 分頁查詢 (SQL Server 2012+ 推薦 OFFSET/FETCH)

DECLARE @PageSize INT = 20, @PageNum INT = 3
SELECT OrderID, CustID, OrderDate, Amount
FROM Orders
ORDER BY OrderDate DESC
OFFSET (@PageNum - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY

舊版本 (2005-2008R2) 使用 ROW_NUMBER() CTE:

WITH Paged AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS RN
    FROM Orders
)
SELECT * FROM Paged WHERE RN BETWEEN 41 AND 60

6. 分組統計實戰

按年月統計業務類型數量

SELECT 
    YEAR(AA_YWRQ) AS [Year],
    MONTH(AA_YWRQ) AS [Month],
    AA_YWLX AS BizType,
    COUNT(*) AS Cnt
FROM AA
GROUP BY YEAR(AA_YWRQ), MONTH(AA_YWRQ), AA_YWLX
ORDER BY [Year], [Month], BizType

結果示例:

YearMonthBizTypeCnt
20143TypeA15
20143TypeB8
20144TypeA22

7. 多表聯聚合:每類商品最新訂單

SELECT c.CategoryName, o.OrderID, o.OrderDate, o.Amount
FROM Categories c
JOIN Products p ON p.CategoryID = c.CategoryID
JOIN (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY OrderDate DESC) AS RN
    FROM OrderDetails
) od ON od.ProductID = p.ProductID AND od.RN = 1
JOIN Orders o ON o.OrderID = od.OrderID
copyright © 萬盛學電腦網 all rights reserved