分组然后排序 (SQL Server)
按照之前的问题
我有这个查询:
SELECT Acc.DocTLItem.TLRef ,
Acc.DocTLItem.Debit AS deb,
Acc.DocTLItem.Credit AS cred,
info.MiladiToShamsi(Acc.DocTLItem.StartDocDate) Date,
Acc.TL.TLCode ,
Acc.DocTLItem.DocTLHeaderRef ,
Acc.DocTLHeader.Num
FROM Acc.DocTLItem
INNER JOIN Acc.TL ON Acc.DocTLItem.TLRef = Acc.TL.Id
INNER JOIN Acc.DocTLHeader ON Acc.DocTLItem.DocTLHeaderRef = Acc.DocTLHeader.Id
ORDER BY ( CASE WHEN debit > 0 THEN 0 ELSE 1 END ) ,
Acc.TL.TLCode ,
debit
结果:
TLRef deb cred Date TLCode DocTLHeaderRef Num
--------------------------------------------------------------------------
44 1 0 1396/09/12 111 16 2
44 1 0 1396/09/21 111 18 4
28 13 0 1396/09/11 982 15 1
28 10 0 1396/09/19 982 17 3
44 0 10 1396/09/19 111 17 3
44 0 1 1396/09/21 111 18 4
44 0 9 1396/09/11 111 15 1
44 0 1 1396/09/12 111 16 2
如何按日期分组然后按日期排序?
How can I Group by Date then sort by Date?
我需要生成这样的结果集，债务先出现，然后按 TLCode 列排序，然后按日期分组.
I need to generate a result set like this that debt comes first and then ordered by TLCode column after all group by date.
预期结果:
TLRef deb cred Date TLCode DocTLHeaderRef Num
--------------------------------------------------------------------------------
44 1 0 1396/09/12 111 16 2
28 13 0 1396/09/11 982 15 1
28 10 0 1396/09/19 982 17 3
44 0 9 1396/09/11 111 15 1
44 0 1 1396/09/12 111 16 2
44 0 10 1396/09/19 111 17 3
Sum 24 20
44 1 0 1396/09/21 111 18 4
44 0 1 1396/09/21 111 18 4
Sum 1 1
推荐答案
可能以下查询块可以帮助您:此查询将分 4 个步骤进行:
May be following query block can help you: This query will work in 4 steps:
--1. Create a temporary table that we can take as base table (#TMP)
Select *
INTO #TMP
From
(
Select 44 as TLRef, 1 as deb, 0 as cred, '1396/09/12' as Date, 111 as TLCode, 16 as DocTLHeaderRef, 2 as Num Union All
Select 44 as TLRef, 1 as deb, 0 as cred, '1396/09/21' as Date, 111 as TLCode, 18 as DocTLHeaderRef, 4 as Num Union All
Select 28 as TLRef, 13 as deb, 0 as cred, '1396/09/11' as Date, 982 as TLCode, 15 as DocTLHeaderRef, 1 as Num Union All
Select 28 as TLRef, 10 as deb, 0 as cred, '1396/09/19' as Date, 982 as TLCode, 17 as DocTLHeaderRef, 3 as Num Union All
Select 44 as TLRef, 0 as deb, 10 as cred, '1396/09/19' as Date, 111 as TLCode, 17 as DocTLHeaderRef, 3 as Num Union All
Select 44 as TLRef, 0 as deb, 1 as cred, '1396/09/21' as Date, 111 as TLCode, 18 as DocTLHeaderRef, 4 as Num Union All
Select 44 as TLRef, 0 as deb, 9 as cred, '1396/09/11' as Date, 111 as TLCode, 15 as DocTLHeaderRef, 1 as Num Union All
Select 44 as TLRef, 0 as deb, 1 as cred, '1396/09/12' as Date, 111 as TLCode, 16 as DocTLHeaderRef, 2 as Num
) X
--2. Group table by "Date" and select sum of "deb", "cred" columns and insert result in another temporary table (#TMP2)
Select null as TLRef, SUM(deb) as deb, SUM(cred) as cred, Date, null as TLCode, null as DocTLHeaderRef, null as Num
INTO #TMP2
From #TMP
GROUP BY Date
--3. Union both tables to resulting table gets both detail and grouped data.
Select *
From
(
Select *, 0 as IsDetail From #TMP
Union All
Select *, 1 as IsDetail From #TMP2
) X
Order By Date,IsDetail
--4. Drop both temporary table
DROP TABLE #TMP
DROP TABLE #TMP2
相关文章