sql server

select  top 2 * from sc where c#='01' order by score desc
union
select  top 2 * from sc where c#='02' order by score desc
union
select  top 2 * from sc where c#='03' order by score desc

消息 156，级别 15，状态 1，第 2 行
关键字 'union' 附近有语法错误。
消息 156，级别 15，状态 1，第 4 行
关键字 'union' 附近有语法错误。
UNION的语法到底是什么啦，为什么不能这样写

我来帮他解答

检举  |  2011-7-19 18:47

满意回答

select * from (select  top 2 * from sc where c#='01' order by score desc) t1
union
select * from (select  top 2 * from sc where c#='02' order by score desc) t2
union
select * from (select  top 2 * from sc where c#='03' order by score desc) t3

追问

这样写就对了，为什么有这样的语法啦？

回答

union 只允许有一个order by 语句，所以，子句中需要排序的话，要在外面再包一层查询。

游标

declare @userid varChar(100)
declare @tem varchar(100)
set @tem = '我的好友'
declare ucursor cursor for
select UserID from [User]

open ucursor
fetch next from ucursor into @userid
while(@@fetch_status=0)
begin
insert into FriendGroup(UserID,GroupName)values (@userid,@tem)
fetch next from ucursor into @userid
end 
close ucursor
deallocate ucursor
go

数据导出问文本文档

C:\Users\Administrator>bcp "select * from AlgorithmTest.DBO.OperatorConfiguratio
n" queryout D:\test.txt -c -S 192.168.0.1 -U sa -P rabbit666

我写的sql

alter function fn_getChild(@childID int)
returns @t table(MindjetDataID int, ParentID int, MindjetDataName nvarchar(50), MindjetDataExtInfo nvarchar(max), MindjetDataType tinyint, UserID int, OrderID int, CreateTime datetime)
begin
insert @t 
select MindjetDataID, ParentID, MindjetDataName, MindjetDataExtInfo, MindjetDataType, UserID, OrderID, CreateTime from MindjetData where ParentID = @childID
while @@rowcount <> 0
begin
insert @t 
select m.MindjetDataID, m.ParentID, m.MindjetDataName, m.MindjetDataExtInfo, m.MindjetDataType, m.UserID, m.OrderID, m.CreateTime 
from MindjetData as m,@t as t
where m.ParentID = t.MindjetDataID
end
return
end

查询子节点

with md as(
select [MindjetDataID] from dbo.MindjetData where MindjetDataID = 15
union all select mm.MindjetDataID from md, dbo.MindjetData as mm
 where mm.ParentID = md.MindjetDataID
)
select * from dbo.MindjetData where MindjetDataID in(select [MindjetDataID] from md)

复制 搜索

复制 搜索

复制 搜索

复制 搜索

复制 搜索

复制 搜索

复制 搜索

复制 搜索