一个表(T)结构:
PK(A,B) 字段B数字型
---------------------------
A B
---------------------------
a 1
a 2
a 5
a 6
a 8
b 9
---------------------------
要求结果按A分组,并且B连续的记录数及每组B的最小值,结果:
a 2 1
a 2 5
a 1 8
b 1 9
--------
求一个SQL语句,可以使用视图。
...全文
26011打赏收藏
一个烦人的分组问题
一个表(T)结构: PK(A,B) 字段B数字型 --------------------------- A B --------------------------- a 1 a 2 a 5 a 6 a 8 b 9 --------------------------- 要求结果按A分组,并且B连续的记录数及每组B的最小值,结果: a 2 1 a 2 5 a 1 8 b 1 9 -------- 求一个SQL语句,可以使用视图。
不过这个重要的子查询(SELECT MAX(x.B) FROM #base x WHERE x.A=y.A AND x.B<y.B AND x.hasBefore=0)在Access下有问题:
因此修改了一下,具体如下:
------------------------------------------
视图1(V1)
SELECT y.A, y.B, max(x.B) AS MinB
FROM T AS y, T AS x
WHERE x.A=y.A and x.B<y.B and x.hasBefore=0 and y.hasBefore=1
GROUP BY y.A, y.B;
----------------------------------------------------
视图2(V2)
SELECT y.A, y.B, max(x.B) AS MinB
FROM V1 AS y, V1 AS x
WHERE x.A=y.A and x.B<y.B and x.hasBefore=0 and y.hasBefore=1
GROUP BY y.A, y.B;
--------------------------------------------------------
最后的SQL
-----------------------------------------------------
SELECT T.A, count(T.MinB) AS CountB, Min(T.MinB) AS MinB
FROM [SELECT y.A,y.B, iif(y.hasBefore=0, y.B, x.MinB) as MinB
FROM V1 y left join V2 x on y.A=x.A and y.b=x.B
]. AS T
GROUP BY T.A, T.Minb;
DECLARE @T TABLE(A CHAR(1),B INT)
INSERT @T
SELECT 'a', 1
UNION ALL SELECT 'a', 2
UNION ALL SELECT 'a', 5
UNION ALL SELECT 'a', 6
UNION ALL SELECT 'a', 8
UNION ALL SELECT 'b', 9
UNION ALL SELECT 'a', 10
SELECT * FROM @t
/*
-------------------
A B
--------------------
a 1
a 2
a 5
a 6
a 8
b 9
a 10
*/
SELECT A,B,hasBefore=(SELECT COUNT(1) FROM @t x WHERE x.A=y.A AND x.B=y.B-1) INTO #Base FROM @t y
SELECT * FROM #base
SELECT A,COUNT(B),MIN(B) FROM
(SELECT A,B,Far=(CASE hasBefore WHEN 0 THEN B
ELSE
(SELECT MAX(x.B) FROM #base x WHERE x.A=y.A AND x.B<y.B AND x.hasBefore=0)
END)
FROM #base y
) base
GROUP BY A,Far
DROP TABLE #base
查询结果
/*
A COUNT MIN
-------------------------
a 2 1
a 2 5
a 1 8
b 1 9
a 1 10
'只用SQL语句很难做到,使用如下VBA函数可以实现:
Public Sub GetData()
Dim Tp As String
Dim iMin As Integer
Dim jCount As Integer
Dim k As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim sResult As String
Dim strSQL As String
Dim Cnn As New ADODB.Connection
Dim Rst1 As New ADODB.Recordset
Dim Rst2 As New ADODB.Recordset
Set Cnn = CurrentProject.Connection
strSQL = "select A from T group by A order by A"
Rst1.Open strSQL, Cnn, adOpenKeyset, adLockReadOnly
If Not Rst1.EOF Then
Rst1.MoveFirst
Do While Not Rst1.EOF
Tp = Rst1!a
jCount = 0
x = DMin("B", "T", "A='" & Tp & "'")
y = DCount("B", "T", "A='" & Tp & "'")
z = 0
Lbl:
Set Rst2 = Nothing
strSQL = "select B from T where A='" & Rst1!a & "' and B>=" & x & " order by B"
Rst2.Open strSQL, Cnn, adOpenKeyset, adLockReadOnly
If Not Rst2.EOF Then
Rst2.MoveFirst
iMin = Rst2!b
jCount = 1
k = Rst2!b + 1
Do While Not Rst2.EOF
If k > Rst2!b Then
If z + jCount = y Then
'记录集结尾
Debug.Print Tp & "," & jCount & "," & iMin
sResult = sResult & vbCrLf & Tp & "," & jCount & "," & iMin
End If
ElseIf k = Rst2!b Then
jCount = jCount + 1
k = k + 1
If z + jCount = y Then
'记录集结尾
Debug.Print Tp & "," & jCount & "," & iMin
sResult = sResult & vbCrLf & Tp & "," & jCount & "," & iMin
End If
Else
'开始断号
Debug.Print Tp & "," & jCount & "," & iMin
sResult = sResult & vbCrLf & Tp & "," & jCount & "," & iMin