Закину ка я сюда для примера один из запросов из системы Тенцера, что бы ощутить глубину так сказать...
Взят из конфы, пытался его несколько отформатировать, для прочтения но не очень-то получилось.
Видел этот запрос ДО и ПОСЛЕ прочтения статьи.
ДО - непонятно вообще нифига.
ПОСЛЕ - уже можно ковыряться.
Hello Igor.
Вторник Февраль 27 2001 12:19, you wrote to All:
IT> ПРИМЕР ТЕнцера в студию! Пущай Попов покажет этим MSSQL'истам!
на деле примеров от Анатолия была куча и куда более большие, а это
видимо, то, что под руку попалось.
-=== Cut ===-
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
SELECT T.*,
- DATEDIFF(day, @EndDate + 1 , CASE WHEN @StartDate > T.ItemDate
THEN @StartDate
ELSE T.ItemDate END)
* T.Amount * @PayPercent * T.[Index] / 100 AS Income
FROM ( SELECT T1.*, (SELECT -SUM(CASE WHEN MM.Direction = 0 AND O.TypeId <> @TAccount
THEN MM.DirAmount
WHEN MM.Direction = 1 AND H2.Id IS NOT NULL
THEN DirAmount
ELSE 0 END)
FROM Movement MM WITH(INDEX(IN_Movement_GroupIdContentId))
LEFT LOOP JOIN Objects O ON O.Id = MM.ContainerId
LEFT LOOP JOIN History H2 ON H2.ObjectId = MM.ContainerId AND
H2.StatusId IN (@IODate, @ITDate) AND H2.ObjectId = MM.ContainerId AND
H2.ItemDate < @EndDate + 1
WHERE MM.GroupId = T1.Id AND MM.ContentId = @Roubles
) AS Amount
FROM (
SELECT M.GroupId AS Id,
H2.ItemDate,
D.DocNum,
OT.ItemName AS Type,
OC.Itemname AS Contractor,
OC.Id AS ContractorId,
OM.ItemName AS Manager,
PInd.Value AS [Index]
FROM History H
INNER LOOP JOIN Movement M WITH(INDEX(IN_Movement_ContainerId))
ON M.ContainerId = H.ObjectId
AND M.ContentId = @Roubles
INNER LOOP JOIN Objects O1
ON O1.Id = M.GroupId
AND O1.TypeId IN (@CashOrder, @DraftIn)
INNER JOIN Links L ON L.ChildId = M.GroupId
AND L.TypeId = @Contractor
INNER JOIN Links LM ON LM.ChildId = L.ParentId
AND LM.TypeId = @Manager
AND ISNULL(@ManagerId, LM.parentId) = LM.parentId
INNER JOIN History H2 ON H2.ObjectId = M.GroupId
AND H2.ItemDate < @EndDate + 1
INNER JOIN Status S2 ON S2.Id = H2.StatusId
AND S2.Code = 'DATE'
INNER JOIN History H1 ON H1.ObjectId = M.ContainerId
INNER JOIN Status S ON S.Id = H1.StatusId
AND S.Code = 'PAYDATE'
INNER LOOP JOIN Objects OM ON OM.Id = LM.parentId
INNER LOOP JOIN Objects OC ON OC.Id = L.ParentId
LEFT LOOP JOIN Properties PInd ON PInd.ObjectId = OC.Id
AND PInd.TypeId = @Index
INNER LOOP JOIN Doc D ON D.Id = M.GroupId
INNER LOOP JOIN Objects O ON O.Id = M.GroupId
INNER LOOP JOIN ObjType OT ON OT.Id = O.TypeId
WHERE H.StatusId IN (@IODate, @ITDate) AND H.Itemdate >= @EndDate + 1
AND (H2.StatusId = @CODate OR EXISTS(SELECT TOP 1 * FROM MoveLink ML
INNER JOIN History HL ON HL.ObjectId = ML.ObjectId
INNER JOIN Status SL ON SL.Id = HL.StatusId
AND SL.GroupId = @AccType
AND SL.Code IN ('BANK', 'VEXELMANY')
WHERE ML.MoveId = M.Id
AND ML.TypeId = @Account))
AND NOT EXISTS (SELECT TOP 1 * FROM History
WHERE ObjectId = H2.ObjectId AND StatusId = @Tender)
UNION
SELECT LMP.ChildId AS Id,
H2.ItemDate AS DocDate,
D.DocNum,
OT.ItemName AS DocType,
OC.Itemname AS Contractor,
OC.Id AS ContractorId,
OM.ItemName AS Manager,
PInd.Value AS [Index]
FROM Objects O
INNER LOOP JOIN Links LM ON LM.ChildId = O.Id AND LM.TypeId = @Manager
AND ISNULL(@ManagerId, LM.parentId) = LM.parentId
INNER LOOP JOIN Links LMP ON LMP.TypeId = @MustCompl AND LMP.ParentId
= O.Id
INNER LOOP JOIN Objects O1 ON O1.Id = LMP.ChildId AND O1.TypeId IN
(@InvoiceOut, @InvoiceTr)
INNER LOOP JOIN History H1 ON H1.ObjectId = O1.Id AND H1.ItemDate <
@EndDate + 1
INNER LOOP JOIN Status S ON S.Id = H1.StatusId AND S.Code = 'PAYDATE'
INNER JOIN History H2 ON H2.ObjectId = LMP.ChildId AND H2.ItemDate <
@EndDate + 1
INNER JOIN Status S2 ON S2.Id = H2.StatusId AND S2.Code = 'DATE'
INNER LOOP JOIN Doc D ON D.Id = O1.Id
INNER LOOP JOIN ObjType OT ON OT.Id = O1.TypeId
INNER JOIN Objects OM ON OM.Id = LM.parentId
INNER JOIN Objects OC ON OC.Id = O.Id
LEFT LOOP JOIN Properties PInd ON PInd.ObjectId = OC.Id AND
PInd.TypeId = @Index
WHERE O.TypeId = @Firm
AND NOT EXISTS (SELECT TOP 1 * FROM History WHERE ObjectId =
LMP.ChildId AND StatusId = @Tender)
AND (O1.TypeId = @InvoiceTr OR EXISTS (SELECT TOP 1 *
FROM Links L
INNER LOOP JOIN History H ON
H.ObjectId = L.ParentId
INNER LOOP JOIN Status S ON
S.Id = H.StatusId AND S.GroupId = @CalcStyle AND S.Code IN
('CSDELAY','CSCASH','CSPREPAY')
WHERE L.ChildId = O1.Id AND
L.TypeId = @InvQueryOut
))
) AS T1
) AS T
ORDER BY T.Manager, T.Itemdate, T.DocNum
-=== Cut ===-
а записей там под 10М.
Andrew
