xml地图|网站地图|网站标签 [设为首页] [加入收藏]
SQL一次性查询一个字段不同条件下的统计结果,
分类:数据库

1、数据库文件类型:

参考了一下这篇文章: , 感谢原作者

复制路径 找到SqlStudio.bin 文件删除即可

①数据文件

有两个表,分别存放了【操作员】和【单据】,要根据单据的不同类型来分类汇总(销售单、销售退货单,笔数和金额),并且显示在同一张表里,不想用做两次查询再合并的方法,研究了一下,终于搞定:

SQL Server 2016 版本:

主要数据文件:后缀 .mdf ,有且只有一个,默认已创建,包含启动信息、数据对象

d_employee表

C:Users%username%AppDataRoamingMicrosoftSQL Server Management Studio13.0SqlStudio.bin

次要数据文件:后缀 .ndf ,可有任意个,默认无

图片 1

SQL Server 2014 版本:

文件流数据:存储图片、音频等文件

d_bilndx表

C:Users%username%AppDataRoamingMicrosoftSQL Server Management Studio12.0SqlStudio.bin

②事务日志文件:后缀 .ldf ,至少一个,默认已创建一个,记录所有事务的SQL语句,用于恢复数据库

图片 2

SQL Server 2012 版本:

2、创建和扩展数据库

 

C:Users%username%AppDataRoamingMicrosoftSQL Server Management Studio11.0SqlStudio.bin

文件大小:有一个初始大小,可扩展,最小单位1MB

代码如下:

SQL Server 2008 版本:

增长方式:①按百分比②按MB

select  b.inputid as 开单员编号, 
        e.fullname as 开单员, 

        isnull( ( select count(*)
          from d_bilndx
          where draft=3 and biltype=12 and d_bilndx.inputid=e.id
        ), 0) as '销售开单笔数',

        isnull( ( select sum(d_bilndx.amount)
          from d_bilndx
          where draft=3 and biltype=12 and d_bilndx.inputid=e.id
        ), 0) as '销售开单金额',

        isnull( ( select count(*)
          from d_bilndx
          where draft=3 and biltype=13 and d_bilndx.inputid=e.id
        ), 0) as '销售退单笔数',

        isnull( ( select sum(d_bilndx.amount)
          from d_bilndx
          where draft=3 and biltype=13 and d_bilndx.inputid=e.id
        ), 0) as '销售退单金额',

        count(b.biltype) as 开单总笔数,
        sum(b.Amount) as 开单金额

from d_bilndx as b
left join d_employee as e 
on b.inputid=e.id 
where b.draft=3 and ( b.biltype=12 or b.biltype=13 )
group by b.inputid, e.fullname, e.id
C:Users%username%AppDataRoamingMicrosoftMicrosoft SQL Server100ToolsShellSqlStudio.bin

可限制数据大小:方式:①限制大小②不限制大小

 

SQL Server 2005 版本,删除mru.dat 文件:

3、收缩数据库:释放不使用的空间

得到结果:

C:Users%username%AppDataRoamingMicrosoftMicrosoft SQL Server90ToolsShellmru.dat

方式:①手动收缩

 

收缩数据库:不能低于初始大小

图片 3

收缩文件:可以低于初始大小,最小单位1M

 

②自动收缩

补记:以上代码有一个问题,就是如果没有符合条件的单据,查到的结果为空,而我们可能希望,查不到符合条件的记录,相关字段要显示为0(并且按天来统计),改写代码如下:

数据库属性,可设置自动收缩

 

4、分离和附加数据库

select  e1.id as ePersonCode, e1.FullName as eFullName, 
        isnull(Bill_Sale_NUm, 0) as Bill_Sale_Num, 
        isnull(Bill_Sale_Amount, 0) as Bill_Sale_Amount, 
        isnull(Bill_SaleReturn_Num, 0) as Bill_SaleReturn_Num, 
        isnull(Bill_SaleReturn_Amount, 0) as Bill_SaleReturn_Amount
from d_employee as e1
left join (

        select  b.inputid as ePersonCode, 
                e.fullname as eFullName, 

                isnull( ( select count(*)
                  from d_bilndx
                  where biltype=12 and d_bilndx.inputid=e.id and d_bilndx.date>='2018-06-03' and d_bilndx.date<='2018-06-03'
                ), 0) as Bill_Sale_Num,

                isnull( ( select sum(d_bilndx.amount)
                  from d_bilndx
                  where biltype=12 and d_bilndx.inputid=e.id and d_bilndx.date>='2018-06-03' and d_bilndx.date<='2018-06-03'
                ), 0) as Bill_Sale_Amount,

                isnull( ( select count(*)
                  from d_bilndx
                  where biltype=13 and d_bilndx.inputid=e.id and d_bilndx.date>='2018-06-03' and d_bilndx.date<='2018-06-03'
                ), 0) as Bill_SaleReturn_Num,

                isnull( ( select sum(d_bilndx.amount)
                  from d_bilndx
                  where biltype=13 and d_bilndx.inputid=e.id and d_bilndx.date>='2018-06-03' and d_bilndx.date<='2018-06-03'
                ), 0) as Bill_SaleReturn_Amount,

                count(b.biltype) as Bill_Total_Num

        from d_employee as e 
        left join d_bilndx as b
        on b.inputid=e.id 
        where (b.draft=3 or b.draft=2) and ( b.biltype=12 or b.biltype=13 ) and  b.date>='2018-06-03' and b.date<='2018-06-03'
        group by b.inputid, e.fullname, e.id


) as t1
on e1.id = t1.ePersonCode
where e1.id<>'00000'
order by ePersonCode asc

分离:将数据库从实例中移除,便于移动或复制数据库

 

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:SQL一次性查询一个字段不同条件下的统计结果,

上一篇:数据库常用SQL用法,SQL应用实例 下一篇:没有了
猜你喜欢
热门排行
精彩图文