Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate sequence number generation for subsets in the result set.
------------
-- SQL sequence number for subset: same OrderID - SQL self join
------------
USE Northwind
GO
SELECT SeqNo,
odet.OrderID,
odet.ProductID,
UnitPrice,
Quantity,
Discount = convert(NUMERIC(3,2),Discount)
FROM [Order Details] odet
JOIN (SELECT count(* ) SeqNo,
a.OrderID,
a.ProductID
FROM [Order Details] A
INNER JOIN [Order Details] B
ON A.ProductID >= B.ProductID
AND A.OrderID = B.OrderID
GROUP BY A.OrderID,
A.ProductID) a
ON odet.OrderID = a.OrderID
AND odet.ProductID = a.ProductID
WHERE odet.OrderID < 10400
ORDER BY odet.OrderID,
odet.ProductID,
SeqNo
GO
/* Partial results
SeqNo OrderID ProductID UnitPrice Quantity Discount
1 10248 11 14 12 0
2 10248 42 9.8 10 0
3 10248 72 34.8 5 0
1 10249 14 18.6 9 0
2 10249 51 42.4 40 0
1 10250 41 7.7 10 0
2 10250 51 42.4 35 0.15
3 10250 65 16.8 15 0.15
1 10251 22 16.8 6 0.05
2 10251 57 15.6 15 0.05
3 10251 65 16.8 20 0
1 10252 20 64.8 40 0.05
2 10252 33 2 25 0.05
3 10252 60 27.2 40 0
1 10253 31 10 20 0
2 10253 39 14.4 42 0
3 10253 49 16 40 0
*/
------------
No comments:
Post a Comment