SELECT * INTO NewTable FROM SourceTable WHERE 1 <> 1
SELECT TOP 0 * INTO NewTable FROM SourceTable
兩種方式均創建相同列定義、NULL屬性、標識屬性的空表,不復制索引、約束、觸發器、權限。
INSERT INTO TargetTable (Col1, Col2)
SELECT Col1, Col2 FROM SourceTable
列名可省略(順序、數量、類型完全匹配時)。
-- 三段式命名:[LinkedServer.]Database.Schema.Table
INSERT INTO TargetDB.dbo.TargetTable (Col1)
SELECT Col1 FROM SourceDB.dbo.SourceTable
若涉及鏈接服務器:INSERT INTO [LinkedSrv].TargetDB.dbo.Tbl ...
SELECT OrderID,
(SELECT CustName FROM Customers c WHERE c.CustID = o.CustID) AS Customer
FROM Orders o
-- 查詢每個客戶的最大訂單
SELECT * FROM Orders o
WHERE OrderAmount = (SELECT MAX(OrderAmount) FROM Orders WHERE CustID = o.CustID)
-- 有訂單的客戶
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)
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
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
結果示例:
| Year | Month | BizType | Cnt |
|---|---|---|---|
| 2014 | 3 | TypeA | 15 |
| 2014 | 3 | TypeB | 8 |
| 2014 | 4 | TypeA | 22 |
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