sql基本查询问题

[dayinxiang]

我现在有这么一个问题：按某一列分组，然后再输出每一组的前10项
请问该怎么解决？
还有个问题就是查询的结果能不能转化成文本文件

[csdnweii]

select top 10 id,max(a),max(b) from tab group by id

[csdnweii]

错了

[internetcsdn]

关注

[internetcsdn]

我想可以用函数来解决的.

[dayinxiang]

关于用SQL语言写函数，我还没学，那位大哥能帮小弟找点这方面的资料啊，谢谢了。。

[xiaoliaoyun]

--建表,插入数据
declare @a table (xuhao int identity(1,1),type char(1))

insert into @a(type)
select 'A' union all
select 'A' union all
select 'A' union all
select 'A' union all
select 'A' union all
select 'A' union all
select 'A' union all
select 'A' union all
select 'A' union all
select 'A' union all
select 'A' union all
select 'A' union all
select 'A' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B'


--查询
select * from
(
select *,numbers=(select count (*) from @a b where b.xuhao<a.xuhao and b.type=a.type)
from @a a
) x
where numbers<10

--结果
/*
xuhao type numbers
----------- ---- -----------
1 A 0
2 A 1
3 A 2
4 A 3
5 A 4
6 A 5
7 A 6
8 A 7
9 A 8
10 A 9
14 B 0
15 B 1
16 B 2
17 B 3
18 B 4
19 B 5
20 B 6
21 B 7
22 B 8
23 B 9

（所影响的行数为 20 行）


*/

[xiaoliaoyun]

思想:新增numbers列,记录type出现的次数

[outwindows]

如果没有xuhao呢？

[dayinxiang]

由于我学的少，所以以上的程序看的不是很明白，
不过该程序在查询的时候有问题，说@a这个变量没有声明

[dayinxiang]

不好意思，原来放到一个查询文件里就好使了，我分开了。。。

[shyyu]

查询的结果能转化成文本文件,但着要用函数实现

[shyyu]

Private Sub WriteTable(TableName As String, TableNameOther As String)
Dim lstrSql As String
Dim i As Integer
Dim lintFields As Integer
Dim lstrLine As String
Dim rs As New ADODB.Recordset

lstrSql = "select * from " & TableName
rs.Open lstrSql, cnn, adOpenStatic, adLockReadOnly
If Not (rs.EOF = True And rs.BOF = True) Then
iTxtFile.writeline ("Table:" & TableNameOther)
Do
lintFields = rs.Fields.Count
For i = 0 To lintFields - 1
If IsNull(rs(i)) Then
If rs(i).Type = adNumeric Or rs(i).Type = adInteger Or rs(i).Type = adSingle Then
lstrLine = rs(i).Name & ":" & "0"
ElseIf rs(i).Type = adDBTimeStamp Then
lstrLine = rs(i).Name & ":" & Format(Now, "yyyy-mm-dd")
Else
lstrLine = rs(i).Name & ":" & ""
End If
Else
lstrLine = rs(i).Name & ":" & CStr(rs(i))
End If
iTxtFile.writeline (lstrLine)
Next i
rs.MoveNext
If rs.EOF <> True Then iTxtFile.writeline ("NEXT") Else iTxtFile.writeline ("END")
Loop While rs.EOF <> True
rs.Close
End If
End Sub

Private Sub WriteTableForSql(sql As String, TableNameOther As String)
Dim lstrSql As String
Dim i As Integer
Dim lintFields As Integer
Dim lstrLine As String
Dim rs As New ADODB.Recordset
lstrSql = sql
rs.Open lstrSql, cnn, adOpenStatic, adLockReadOnly
If Not (rs.EOF = True And rs.BOF = True) Then
iTxtFile.writeline ("Table:" & TableNameOther)
Do
lintFields = rs.Fields.Count
For i = 0 To lintFields - 1
If IsNull(rs(i)) Then
If rs(i).Type = adNumeric Or rs(i).Type = adInteger Or rs(i).Type = adSingle Then
lstrLine = rs(i).Name & ":" & "0"
ElseIf rs(i).Type = adDBTimeStamp Then
lstrLine = rs(i).Name & ":" & Format(Now, "yyyy-mm-dd")
Else
lstrLine = rs(i).Name & ":" & ""
End If
Else
lstrLine = rs(i).Name & ":" & CStr(rs(i))
End If
iTxtFile.writeline (lstrLine)
Next i
rs.MoveNext
If rs.EOF <> True Then iTxtFile.writeline ("NEXT") Else iTxtFile.writeline ("END")
Loop While rs.EOF <> True
rs.Close
End If
End Sub

[xiaoliaoyun]

xuhao可以换成表中的主建

[xiaoliaoyun]

查询结果导出为文本文件:
exec master..xp_cmdshell 'osql /U sa /P 密码 /d northwind /S 服务器IP -h 1 /Q "set nocount on;select OrderID from orders" -o c:\test.txt'