你想象不到的SQL语句?

对于熟悉SQL来说,Select语句是最基本的，我们常见的语句是这样的: select [列1],[列2],[列3] from TableName，如:select usrerid,username from User.但是这样的语法你见过了吗？select [列1],[列2],[集合] from TableName，如:select usrerid,username ,(select [列1] from ClassName) from User
看客如果细心的话，可以发现上面的语法其实是错误的。但是以下语句你又作何解释？
1:考虑一下以下场景:VideoList是视频总表，里面有一个字段是tag,就是标签，tags表是存放视频表所有标签以及其数量。
表:tags

CREATE TABLE [dbo].[tags] (
    [tags] [varchar] ( 500 ) COLLATE Chinese_PRC_CI_AS  NOT  NULL ,
    [counts] [ int ]  NOT  NULL 
)  ON  [PRIMARY]
GO

表:VideoList 

CREATE TABLE [dbo].[VideoList] (
    [VideoId] [ int ] IDENTITY ( 1 ,  1 )  NOT  NULL ,
    [VideoPath] [varchar] ( 8000 ) COLLATE Chinese_PRC_CI_AS  NOT  NULL ,
    [VideoName] [varchar] ( 1024 ) COLLATE Chinese_PRC_CI_AS  NOT  NULL ,
    [tag] [varchar] ( 50 ) COLLATE Chinese_PRC_CI_AS  NOT  NULL 
)  ON  [PRIMARY]
GO

sql语句:

insert into tags(tags,counts)
select  tag,( select  count( * ) from  dbo.VideoList  where  tag = a.tag) from ( select  distinct tag  from dbo.VideoList  where  isnull(tag , ' ')!='') as  a

解释:这里面关键的语句是(select count(*) from  dbo.VideoList  where  tag=a.tag) 我们认为它是一个函数，返回一个数字的函数。

2:在看一个列子，场景是这样的。表PicList是存放图片的文字信息,主键兼任自增长键是picid,表PicAttachList是存放图片的地址信息，外键是picid，图片地址字段是Picurl。现在我们有这个需求,想在PicList增加一个列viewpicur,用来存放图片预览图.用一个SQL语句给实现了
表:PicList

CREATE TABLE [dbo].[PicList] (
    [picid] [ int ] IDENTITY ( 1 ,  1 )  NOT  NULL ,
    [picname] [varchar] ( 200 ) COLLATE Chinese_PRC_CI_AS  NOT  NULL ,
    [viewpicurl] [varchar] ( 1024 ) COLLATE Chinese_PRC_CI_AS NULL 
)  ON  [PRIMARY]

表:PicAttachList

CREATE TABLE [dbo].[PicAttachList] (
    [urlid] [ int ] IDENTITY ( 1 ,  1 )  NOT  NULL ,
    [picid] [ int ]  NOT  NULL ,
    [Picurl] [varchar] ( 1024 ) COLLATE Chinese_PRC_CI_AS  NOT  NULL ,
    [orderby] [ int ]  NOT  NULL 
)  ON  [PRIMARY]
GO

SQL语句:

update PicList   set  viewpicurl = ( select  top  1  Picurl from PicAttachList where PicAttachList.picid = PicList.picid order by orderby desc)

解释:这个SQL语句是最不可思议的一句，当时凭我是感觉写的，觉得这个SQL语句没有问题，结果执行起来效果非常好，比用游标写的要方便快捷。按照语法，这个SQL是不符合update的语法的，我也无法解释，我的感觉是这样的，只要能创造一个VALUE值赋给viewpicurl就可以了.别小看这个语句，有了这个语句，我们甚至可以给数据库做索引。

下面我们来考虑以下需求：
给音乐表MusicList增加几个排序字段，也就是做索引，我们在程序做排序的时候就可以使用page>1 and page<30这样的语法了，这样做可大幅提高排序效果，提高系统性能,尤其是表记录超过1000万的时候。

表:MusicList

CREATE TABLE [dbo].[MusicList] (
    [MusicId] [ int ] IDENTITY ( 1 ,  1 )  NOT  NULL ,
    [MusicPath] [varchar] ( 8000 ) COLLATE Chinese_PRC_CI_AS  NOT  NULL ,
    [MusicName] [varchar] ( 1024 ) COLLATE Chinese_PRC_CI_AS  NOT  NULL ,
        [Classid] [ int ]  NOT  NULL ,
    [hits] [ int ]  NOT  NULL ,
    [hitsdesc] [ int ]  NOT  NULL ,
    [orderby] [ int ]  NOT  NULL ,
    [orderbydesc] [ int ]  NOT  NULL ,
    [randid] [varchar] ( 64 ) COLLATE Chinese_PRC_CI_AS  NOT  NULL ,
    [randiddesc] [ int ]  NOT  NULL ,
    [addtime] [datetime]  NOT  NULL ,
    [addtimedesc] [ int ]  NOT  NULL 
)  ON  [PRIMARY]
GO

表:ClassID 是歌曲的分类，如大陆歌手。

CREATE TABLE [dbo].[ClassID] (
    [MusicClassid] [ int ]  NOT  NULL ,
    [classname] [varchar] ( 1024 ) COLLATE Chinese_PRC_CI_AS  NOT  NULL ,
    [addtime] [datetime]  NOT  NULL 
)  ON  [PRIMARY]
GO

其中[dbo].[MusicList]的Classid是歌曲的分类ID，对应于表[dbo].[ClassID] 的主键MusicClassid
下面的就来给hits，hitsdesc，orderby，orderbydesc，randid，randiddesc，addtimedesc做索引了

SET  QUOTED_IDENTIFIER  ON  
GO
SET  ANSI_NULLS  ON  
GO
Create    Proc SQLPageList
as  
begin
declare  @classid  int
declare  getcursor cursor  for  
select  MusicClassid from dbo.ClassID
open getcursor
FETCH  NEXT  FROM getcursor INTO
@classid
WHILE  @@FETCH_STATUS  =   0
BEGIN
--- 1 -------------------------------------------
CREATE TABLE #IdentityTempTable1 (
    [ID] [ int ] IDENTITY ( 1 ,  1 )  NOT  NULL ,
    [musicid] [ int ]  NOT  NULL ,
    [classid] [ int ]  NOT  NULL 
)  ON  [PRIMARY]

insert into #IdentityTempTable1(musicid,classid)
select  musicid,classid from dbo.MusicList  where classid  =  @classid order by addtime  desc 
update dbo.MusicList   set    addtimedesc = ( select  id from #IdentityTempTable1 where #IdentityTempTable1.musicid = dbo.MusicList.musicid  and  #IdentityTempTable1.classid = dbo.MusicList.classid ) where classid = @classid
drop table #IdentityTempTable1
--- 1 --------------------------------------------

--- 2 -------------------------------------------
CREATE TABLE #IdentityTempTable2 (
    [ID] [ int ] IDENTITY ( 1 ,  1 )  NOT  NULL ,
    [musicid] [ int ]  NOT  NULL ,
    [classid] [ int ]  NOT  NULL 
) 

insert into #IdentityTempTable2(musicid,classid)
select  musicid,classid from dbo.MusicList  where classid  =  @classid order by randid  desc 
update dbo.MusicList   set   randiddesc  = ( select  id from #IdentityTempTable2 where #IdentityTempTable2.musicid = dbo.MusicList.musicid  and  #IdentityTempTable2.classid = dbo.MusicList.classid ) where classid = @classid
drop table #IdentityTempTable2
--- 2 --------------------------------------------



--- 3 -------------------------------------------
CREATE TABLE #IdentityTempTable3 (
    [ID] [ int ] IDENTITY ( 1 ,  1 )  NOT  NULL ,
    [musicid] [ int ]  NOT  NULL ,
    [classid] [ int ]  NOT  NULL 
)  ON  [PRIMARY]

insert into #IdentityTempTable3(musicid,classid)
select  musicid,classid from dbo.MusicList  where classid  =  @classid order by orderby  desc 
update dbo.MusicList   set   orderbydesc  = ( select  id from #IdentityTempTable3 where #IdentityTempTable3.musicid = dbo.MusicList.musicid  and  #IdentityTempTable3.classid = dbo.MusicList.classid ) where classid = @classid
drop table #IdentityTempTable3
--- 3 --------------------------------------------


--- 4 -------------------------------------------
CREATE TABLE #IdentityTempTable4 (
    [ID] [ int ] IDENTITY ( 1 ,  1 )  NOT  NULL ,
    [musicid] [ int ]  NOT  NULL ,
    [classid] [ int ]  NOT  NULL 
)  ON  [PRIMARY]

insert into #IdentityTempTable4(musicid,classid)
select  musicid,classid from dbo.MusicList  where classid  =  @classid order by  hits desc 
update dbo.MusicList   set   hitsdesc  = ( select  id from #IdentityTempTable4 where #IdentityTempTable4.musicid = dbo.MusicList.musicid  and  #IdentityTempTable4.classid = dbo.MusicList.classid ) where classid = @classid
drop table #IdentityTempTable4
--- 4 --------------------------------------------

FETCH  NEXT  FROM getcursor INTO
@classid
END
CLOSE getcursor
DEALLOCATE getcursor
end
GO
SET  QUOTED_IDENTIFIER  OFF  
GO
SET  ANSI_NULLS  ON  
GO