你想象不到的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