SQL经典查询

经典报表查询方式

1.数据库相关字段

DictIndex(指标库)
IndexID, IndexCode, IndexName, MeasureName(计量单位)

DictComp(单位库)
  CompID, CompName
DictProjectType(项目类型)
  ProjectTypeTID, ProjectTypeName
DictProject(项目库)
  ProjectID, ProjectName, CompID(单位ID), ProjectTypeTID(项目类别)
DictSegment(标段库)
  SegmentID, SegmentName, ProjectID, BuildCompID(标段施工单位)

SegmentDesignData(标段指标设计数据)
  SegmentID, IndexID, IndexValue, StateFlag(审核状态)
SegmentPlanFinishData(标段指标计划完成数据)
  SegmentID, IndexID, IndexValue, StateFlag,
  DataType(p为计划数据,f为完成数据),
  PeriodType(y为年，s为季，m为月),
  PeriodValue(2007-01-03)

2.分组顺序
显示的层次：汇总、公司、类别、项目、标段、标段施工单位(这只是在标段的基本上加上其施工单位)
Groupt By Z.CompID, Z.ProjectTypeTID, Z.ProjectID, Z.SegmentID

0 合计
1 公司
2 项目类别
3 项目
4 标段
5 标段施工单位

3.先生成视图，以便根据它进行汇总

这个视图包含了公司/类别/项目/标段/设计数据/计划和完成数据
注意：生成后的视图中，设计数据/计划数据/完成数据组成了一个非常冗余的表。
我们必段在统计时，区分出相关的数据。

------------------------------------------------------
---- 视图：计调数据
---- vPF
------------------------------------------------------
--Begin
if (exists (select * from sysobjects where name = 'vPF' and type = 'V'))
drop view vPF
go

CREATE View dbo.vPF
AS

  Select
   A.CompID,
   B.ProjectTypeTID, B.ProjectID,
   C.SegmentID, C.BuildCompID,
   D.IndexID, D.IndexValue As DesignValue, D.StateFlag As DesignState,

E.IndexValue, E.StateFlag,
E.DataType, E.PeriodType, E.PeriodValue

  From DictComp A
  Left Join DictProject B On A.CompID = B.CompID
  Left Join DictSegment C On B.ProjectID = C.ProjectID
  Left Join SegmentDesignData D On C.SegmentID = D.SegmentID
  Left Join SegmentPlanFinishData E On D.SegmentID = E.SegmentID And D.IndexID = E.IndexID

4.拼出的SQL

//0--合计
Select
'0' As GroupFieldId,
'Z0' + '0' As '工程项目',

Sum(Case Z.IndexId When '1' Then case Z.DataType when 'p' then case when ( Z.PeriodType ='M' And ( (Z.PeriodValue >='2007-04') And (Z.PeriodValue <='2007-04') ) ) then Z.DesignValue else 0 end else 0 end Else 0 End ) As '设计', Sum(Case Z.IndexId When '1' Then case Z.DataType when 'p' then case when ( Z.PeriodType ='M' And ( (Z.PeriodValue >='2007-04') And (Z.PeriodValue <='2007-04') ) ) then Z.IndexValue else 0 end else 0 end Else 0 End ) As '计划', Sum(Case Z.IndexId When '1' Then case Z.DataType when 'f' then case when ( Z.PeriodType ='M' And ( (Z.PeriodValue >='2007-04') And (Z.PeriodValue <='2007-04') ) ) then Z.IndexValue else 0 end else 0 end Else 0 End ) As '完成',

'S' As RowIndex

From vPlanFinish Z
Where 1=1 And Z.CompID=1001

//1--公司
Union All
Select
'1' As GroupFieldId,
'Z1' + Cast(Z.CompID as varchar(20)) As '工程项目',

Sum(Case Z.IndexId When '1' Then case Z.DataType when 'p' then case when ( Z.PeriodType ='M' And ( (Z.PeriodValue >='2007-04') And (Z.PeriodValue <='2007-04') ) ) then Z.DesignValue else 0 end else 0 end Else 0 End ) As '设计', Sum(Case Z.IndexId When '1' Then case Z.DataType when 'p' then case when ( Z.PeriodType ='M' And ( (Z.PeriodValue >='2007-04') And (Z.PeriodValue <='2007-04') ) ) then Z.IndexValue else 0 end else 0 end Else 0 End ) As '计划', Sum(Case Z.IndexId When '1' Then case Z.DataType when 'f' then case when ( Z.PeriodType ='M' And ( (Z.PeriodValue >='2007-04') And (Z.PeriodValue <='2007-04') ) ) then Z.IndexValue else 0 end else 0 end Else 0 End ) As '完成',

'S' + Cast(Z.CompID as varchar(20)) As RowIndex

From vPlanFinish Z
Where 1=1 And Z.CompID=1001
Group By Z.CompID

//1--类别
Union All
Select
'2' As GroupFieldId,
'Z2' + Z.ProjectTypeTID As '工程项目',

'S' + Cast(Z.CompID as varchar(100)) + ProjectTypeTID As RowIndex

From vPlanFinish Z
Where 1=1 And Z.CompID=1001
Group By Z.CompID, Z.ProjectTypeTID

//3--项目
Union All
Select
'3' As GroupFieldId,
'Z3' + Cast(Z.ProjectID As Varchar(100)) As '工程项目',

Sum(Case Z.IndexId When '1' Then case Z.DataType when 'p' then case when ( Z.PeriodType ='M' And ( (Z.PeriodValue >='2007-04') And (Z.PeriodValue <='2007-04') ) ) then Z.DesignValue else 0 end else 0 end Else 0 End ) As '设计', Sum(Case Z.IndexId When '1' Then case Z.DataType when 'p' then case when ( Z.PeriodType ='M' And ( (Z.PeriodValue >='2007-04') And (Z.PeriodValue <='2007-04') ) ) then Z.IndexValue else 0 end else 0 end Else 0 End ) As '计划', Sum(Case Z.IndexId When '1' Then case Z.DataType when 'f' then case when ( Z.PeriodType ='M' And ( (Z.PeriodValue >='2007-04') And (Z.PeriodValue <='2007-04') ) ) then Z.IndexValue else 0 end else 0 end Else 0 End ) As '完成',

'S'+ Cast(Z.CompID as varchar(100)) + ProjectTypeTID + Cast(Z.ProjectID As Varchar(100)) As RowIndex

From vPlanFinish Z
Where 1=1 And Z.CompID=1001
Group By Z.CompID, Z.ProjectTypeTID, Z.ProjectID

//4--标段(5--标段施工单位，只是显示上的不同)
Union All
Select
'4' As GroupFieldId,
'Z5' + Cast(Z.SegmentID As Varchar(100)) As '工程项目',

'S'+ Cast(Z.CompID as varchar(100)) + ProjectTypeTID + Cast(Z.ProjectID As Varchar(100)) + Cast(Z.SegmentID As Varchar(100)) As RowIndex

From vPlanFinish Z
Where 1=1 And Z.CompID=1001
Group By Z.CompID, Z.ProjectTypeTID, Z.ProjectID, Z.SegmentID

Order By RowIndex Asc

5.SQL说明
5.1.每一个层次分组后进行Union All
5.2.字段的组成:
   (1)GroupFieldId
    表示当前的分组层次
   (2)'工程项目'
    表示当前分组层次(Z0/Z1/Z2/Z3/Z4/Z5)及该层次的分组字段ID。在显示时，我们取出该字段的前两位判断其层次，同时，取出其余的字符--即该层次的ID，取出该ID所对应的名称。从这个意义上说，GroupFieldId可以不存在？
    另外，这种处理方式，在显示时还要连接数据库。速度会有点慢。
   (3)汇总字段分析
    因指标有很多，故汇总字段是也是拼装出来的。

   (4)汇总字段分析(设计字段)
    下面是某个指标的设计数据。
    Sum(Case Z.IndexId When '1'
     Then case Z.DataType
      when 'p' then
       case when ( Z.PeriodType ='M' And ( (Z.PeriodValue >='2007-04') And (Z.PeriodValue <='2007-04') ) )
        then Z.DesignValue
        else 0 end
       else 0 end
      Else 0 End
       ) As '设计' ,

为某个指标ID时，判断其数据类型：计划/完成，（设计数据只根据计划数据进行计算即可）。
判断了数据类型后，还要判断时间的区间类型和区间值，都正确都生成该值Z.DesignValue ，否则为0，以便统计时不将不相关的数据汇总。

   (5)汇总字段分析(计划字段)

    Sum(Case Z.IndexId When '1'
     Then case Z.DataType
      when 'p' then
       case when ( Z.PeriodType ='M' And ( (Z.PeriodValue >='2007-04') And (Z.PeriodValue <='2007-04') ) )
        then Z.IndexValue
        else 0 end
      else 0 end
     Else 0 End
     ) As '计划' ,

它的判断跟设计字段类似，取值是取Z.PeriodValue。

(6)汇总字段分析(完成字段)

    Sum(Case Z.IndexId When '1'
     Then case Z.DataType
      when 'f' then
       case when ( Z.PeriodType ='M' And ( (Z.PeriodValue >='2007-04') And (Z.PeriodValue <='2007-04') ) )
        then Z.IndexValue
        else 0 end
       else 0
      end
     Else 0 End
     ) As '完成' ,

它的判断跟设计字段类似，取值是取Z.PeriodValue。

   (7)汇总字段疑问
    为什么不将这些判断放在整个SQL的Where条件中呢？这样会大大减少SQL的长度。
    理由是：汇总时，计划数据的区间类型和区间值，有时会跟完成数据的区间类型和区间值不一致。如果有Where条件中设置，则只能取计划数据或完成数据，而不能达到计划数据和完成数据一起取的目的。备注：设计数据都在。

业务需求：计划数据的区间类型有“年/季/月”之分，而完成数据的区间类型只有“月”。查询时，如果选择的区间类型为年，则计划数据为年，完成数据则为该年各月的汇总。这时，要将区间类型为年的类型和值转化为月，以便求出完成数据。

   (8)RowIndex排序字段
    这个字段的设计有些地方要注意。以第二层次(类型)的为例。
    'S' + Cast(Z.CompID as varchar(100)) + ProjectTypeTID As RowIndex
    其中，"S"只是区分一下，之后是第一层次的CompID, 接着是类型。因CompID为Int，故要转换。
    由此得出该字段的构成：S + 公司ID + 类型ID + 项目ID + 标段ID 。这本身就是一棵树，类型树型设计中的IDPath的构成。
    这个应该是这种设计的亮点所以，因为只要Order By RowIndex Asc就生成了一棵完整的树。

5.3.Where 条件
  Where 1=1 And Z.CompID=1001
  其中，1=1只是为了便于所有其它条件都要添加 And ，以免顾此失彼。
  And CompID = 1001
  这个条件在也是动态生成的。
  查询多个公司时的条件时，该条件可以更改。如查询1001下的直接子公司：
  And CompID In(Select CompID From DictComp Where ParentID='1001')

5.4.Group By 分组
  要根据该层次确定。
  Group By Z.CompID, Z.ProjectTypeTID, Z.ProjectID, Z.SegmentID

5.5.Order By 排序
  Order By RowIndex Asc
  这就是RowIndex生成的魅力所在了，只根据一个字段就可以生成树。

生成的的情况见图。

6.亮点总结
6.1.'工程项目'字段的生成，该字段时不同表的ID，要用两个字符来区分。
6.2.汇总字段的内嵌条件，解决了条件不同时的数据筛选
6.4.RowIndex字段的生成，解决了树形结构的排序

SQL的写法真是太灵活了。