一:理解sql执行顺序

/************************************************************  
 * 标题:MS SQLServer 批量附加数据库  
 * 说明:请根据下面的注释使用此脚本  
 * 时间: 2015/7/13 11:16:41  
 ************************************************************/  

USE MASTER  
GO  

IF OBJECT_ID('[sp_AttchDataBase]') IS NOT NULL  
    DROP PROCEDURE [sp_AttchDataBase]  
GO  

/*附加数据库(V2.0) Andy 2011-7-8 */  
CREATE PROCEDURE sp_AttchDataBase(  
    @Path       NVARCHAR(1024),  
    @DataFiles  NVARCHAR(MAX) = NULL,  
    @SplitStr   NVARCHAR(50) = ','  
)  
AS  
    SET NOCOUNT ON  

    /*  
    V2.0 版本,在V1.0基础上,处理文件路径不规范原則,e.g. @DataFiles='E:\"my data DB"\"Hello RT"'  

    @Path       文件路径  
    @DataFiles  文件名列表  
    @SplitStr   文件名列表中的文件分隔符  

    1.必须把要附加的数据库文件(*.mdf和*.ldf)放到@Path下,  
    2.当@DataFiles Is Null 会附加@Path文件夹下的所有数据库文件.  

    e.g:  
    Exec sp_AttchDataBase 'D:\db2'  
    */  


    --检查文件路径是否正确  
    DECLARE @Dir  NVARCHAR(1024),  
            @i    INT,  
            @x    XML  

    IF RIGHT(@Path, 1) <> '\'  
        SET @Path = @Path + '\'  

    IF CHARINDEX('\\', @Path) > 0  
    BEGIN  
        --RAISERROR 50001 N'文件路径中不能包含有"\\",@Path设置错误.'  
        RETURN(1)  
    END  

    SET @Dir = 'Dir ' + @Path  
    EXEC @i = xp_cmdshell @Dir,  
         no_output  

    IF @i <> 0  
    BEGIN  
        --RAISERROR 50001 N'无效的文件路径,@Path设置错误.'  
        RETURN(1)  
    END  

    SET @Path = REPLACE(@Path, '"', '') /*处理文件路径不规范原則*/  

    DECLARE @Files               TABLE(NAME NVARCHAR(512))  
    DECLARE @filetmpfin          TABLE(  
                NAME NVARCHAR(255) NOT NULL,  
                depth INT NULL,  
                IsFile BIT NULL  
            )  

    DECLARE @SmoPrimayChildren   TABLE(  
                STATUS INT,  
                fileid INT,  
                NAME SYSNAME,  
                FILENAME NVARCHAR(512)  
            )  

    DECLARE @smoPrimaryFileProp  TABLE(PROPERTY SQL_VARIANT NULL, VALUE SQL_VARIANT NULL)  

    SET @DataFiles = REPLACE(  
            REPLACE(REPLACE(@DataFiles, CHAR(13) + CHAR(10), ''), CHAR(13), ''),  
            CHAR(10),  
            ''  
        )  

    SET @x = N'<Root><File>' + REPLACE(@DataFiles, @SplitStr, N'</File><File>') +   
        N'</File></Root>'  


    INSERT INTO @Files  
    SELECT t.v.value('.[1]', 'nvarchar(512)') AS NAME  
    FROM   @x.nodes('Root/File') t(v)  
    WHERE  t.v.value('.[1]', 'nvarchar(512)') > ''  


    INSERT INTO @filetmpfin  
    EXEC MASTER.dbo.xp_dirtree @Path,  
         1,  
         1  

    DECLARE @File      NVARCHAR(255),  
            @sql       NVARCHAR(4000),  
            @DataBase  SYSNAME  



    DECLARE cur_File   CURSOR    
    FOR  
        SELECT NAME  
        FROM   @filetmpfin AS a  
        WHERE  IsFile = 1  
               AND NAME LIKE '%.mdf'  
               AND (  
                       EXISTS(  
                           SELECT 1  
                           FROM   @Files  
                           WHERE  NAME = a.Name  
                       )  
                       OR @DataFiles IS NULL  
                   )  
               AND NOT EXISTS(  
                       SELECT 1  
                       FROM   MASTER.sys.master_files  
                       WHERE  physical_name = @Path + a.Name  
                   )  

    OPEN cur_File  

    BEGIN TRY  
        FETCH NEXT FROM cur_File INTO @File  
        WHILE @@Fetch_Status = 0  
        BEGIN  
            SET @sql = 'dbcc checkprimaryfile (N''' + @Path + @File + ''' , 2) With No_Infomsgs'  

            INSERT INTO @smoPrimaryFileProp  
            EXEC (@sql)  

            SET @sql = 'dbcc checkprimaryfile (N''' + @Path + @File + ''' , 3) With No_Infomsgs'  

            INSERT INTO @SmoPrimayChildren  
            EXEC (@sql)  

            SELECT @DataBase = QUOTENAME(CONVERT(NVARCHAR(255), VALUE)),  
                   @sql = NULL  
            FROM   @smoPrimaryFileProp  
            WHERE  CONVERT(NVARCHAR(255), PROPERTY) = 'Database name'  

            SELECT @sql = ISNULL(  
                       @sql + ',' + CHAR(13) + CHAR(10),  
                       'Create DataBase ' + @DataBase + ' On' + CHAR(13) + CHAR(10)  
                   ) +  
                   '(FileName=N''' + @Path + RIGHT(  
                       RTRIM(FILENAME),  
                       CHARINDEX('\', REVERSE(RTRIM(FILENAME))) -1  
                   ) + ''')'  
            FROM   @SmoPrimayChildren  

            EXEC (@sql + ' For Attach')  

            PRINT N'成功附加数据库: ' + @DataBase  

            DELETE   
            FROM   @SmoPrimayChildren  

            DELETE   
            FROM   @smoPrimaryFileProp  

            FETCH NEXT FROM cur_File INTO @File  
        END  
    END TRY  
    BEGIN CATCH  
        DECLARE @Error NVARCHAR(2047)  
        SET @Error = ERROR_MESSAGE()  
        --RAISERROR 50001 @Error  
    END CATCH  


    CLOSE cur_File  
    DEALLOCATE cur_File  
GO  

/************************************************************  
 * 调用方式  
 ************************************************************/  
--use master  
--Go  

--Exec sp_AttchDataBase   
--        @Path = 'E:\100.其他\测试', -- nvarchar(1024)  
--        @DataFiles = NULL, -- nvarchar(max)  
--        @SplitStr = NULL -- nvarchar(50)  

查询本月所有的天数:

       
在sql中,第一个被执行的是from语句,每一个步骤都会产生一个虚拟表,该表供下一个步骤查询时调用,比如语句:select
top 10 column1,colum2,max(column3) from user where id>1 group by
column1,colum2 having
count(column1)>1 order by colum2.

 

--本月所有的天数
select convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) day from 
(select substring(convert(varchar,GETDATE(),120),1,7)+'-01' day) t1, 
(select number from MASTER..spt_values WHERE TYPE='P' AND number>=0 and number<=31) t2 
where convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) like substring(convert(varchar,GETDATE(),120),1,7)+'%'

      sqlserver 2005
各个环节简单介绍:

澳门微尼斯人手机版, 

(8)SELECT (9)DISTINCT  (11)<Top Num> <select list>
(1)FROM [left_table]
(3)<join_type> JOIN <right_table>
(2)ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <CUBE | RollUP>
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list>

查询本周所有的天数:

   a)各个步骤简介:

 select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 0),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 1),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 2),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 3),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 4),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 5),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 6),120)
  1. FROM:对FROM子句中的多个表执行笛卡尔积(Cartesian
    product)(交叉联接),生成虚拟表VT1
  2. ON:对VT1应用ON筛选器。只有那些使<join_condition>为真的行才被插入VT2。
  3. OUTER(JOIN):如 果指定了OUTER
    JOIN(相对于CROSS JOIN 或(INNER JOIN),保留表(preserved
    table:左外部联接把左表标记为保留表,右外部联接把右表标记为保留表,完全外部联接把两个表都标记为保留表)中未找到匹配的行将作为外部行添加到
    VT2,生成VT3.如果FROM子句包含两个以上的表,则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3,直到处理完所有的表为止。
  4. WHERE:对VT3应用WHERE筛选器。只有使<where_condition>为true的行才被插入VT4.
  5. GROUP BY:按GROUP
    BY子句中的列列表对VT4中的行分组,生成VT5.
  6. CUBE|ROLLUP:把超组(Suppergroups)插入VT5,生成VT6.
  7. HAVING:对VT6应用HAVING筛选器。只有使<having_condition>为true的组才会被插入VT7.
  8. SELECT:处理SELECT列表,产生VT8.
  9. DISTINCT:将重复的行从VT8中移除,产生VT9.
  10. ORDER BY:将VT9中的行按ORDER BY
    子句中的列列表排序,生成游标(VC10).
  11. TOP:从VC10的开始处选择指定数量或比例的行,生成表VT11,并返回调用者.

示例:

 

如下图所示,有表MO_Orders,字段:BookTime,Number,Count,有如下数据。

  b)标准sql执行顺序是:

澳门微尼斯人手机版 1

  1:form 组装来自不同表的数据,如 form user或者,form user as u join
goodsOrder as r on u.id= r.userid

 

  2:where 过滤符合查询条件的数据,如:id>1000

查询出本周的每天的数据总和,**语句如下:**

  3:group by 将查询数据进行分组

 with t as 
( 
    select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 0),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 1),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 2),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 3),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 4),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 5),120)
    union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 6),120)
)
select id=ROW_NUMBER()OVER(ORDER BY t1.date),
        DATENAME(weekday,t1.date) as weekday,
        t1.date,
        Numbers=sum(isnull(t2.Number,0)),  
        Count=sum(isnull(t2.Count,0)) 
from t t1 
left join
(  
    select substring(convert(varchar,BookTime,120),1,11) as BookTime,
            Number,Count 
    from  MO_Orders 
    where datediff(wk,BookTime-1,getdate()) = 0  
    )
    t2 
on t1.date= substring(convert(varchar,t2.BookTime,120),1,11)
group by t1.date 

  4:使用sum等聚合函数进行计算。

 

  5:使用having 进行筛选分组。

查询效果如下图,其中 weekday为星期,此图还需替换,稍后补上:
澳门微尼斯人手机版 2

  6:执行select语种

 

  7:执行排序语句

示例:如下图所示,有表: MO_Orders,字段:BookTime,Cost,Count 。

  如:select count(gid),gname from shopping_goods where gcid=1 group
by gname having count(gid)>1 order by count(gid) desc

查询本月的所有数据总和(其中:total=Cost*Count)。

  1:首页查询shopping_goods 表,得到表中的数据

澳门微尼斯人手机版 3

  2:执行where,过滤出gcid=1的商品。

查询出本月的每天的数据总和,显示每天的,查询语句如下:

  3:对gname进行分组。

 with t as 
( 
  select convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) date from 
    (select substring(convert(varchar,GETDATE(),120),1,7)+'-01' day) t1, 
    (select number from MASTER..spt_values WHERE TYPE='P' AND number>=0 and number<=31) t2 
    where convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) like substring(convert(varchar,GETDATE(),120),1,7)+'%'
)
select id=ROW_NUMBER()OVER(ORDER BY t1.date),
        t1.date,
        CostTotal=sum(isnull(t2.CostTotal,0))
from t t1 
left join 
(    
    select BookTime,sum(CostTotal) as CostTotal from 
    (
        select substring(convert(varchar,BookTime,120),1,11) as BookTime,
                Cost*Count as CostTotal 
        from  MO_Orders 
        where datediff(month,BookTime,getdate()) = 0 
    ) o group by BookTime
) t2
on t1.date= t2.BookTime 
group by t1.date

  4:使用聚合函数count(),计算出商品类型为1,不同商品名称的数量.

 

  5:使用having,过滤出类型为1,商品统计数量大于1的商品

查询结果如下图:
澳门微尼斯人手机版 4

  6:执行select语句

 

  7:执行order by ,按照商品数量降序排列。

二:百万数据量优化

发表评论

电子邮件地址不会被公开。 必填项已用*标注