SQL 同时显示总计、分组小计、和明细数据的统计查询 ，应用 SQL笛卡尔积 链接 、COUNT()函数 和 UNION

笛卡尔积应用例子：
同时显示总计、分组小计、和明细数据的统计查询
使用 笛卡尔积 防止 不同数据类型合并失败，省略进行数据转换的代码。
dbo.s_count 表是只有一个整数型字段（s_count.计数）和一行记录s_count.计数记录为 1 的表。

select   '总计' , sum(iid) , 0 as 计数
from dbo.s_fielstype 


UNION

select   s_fielstype.cvfptype + '小计' , sum(iid) , COUNT(s_count.计数) as 计数
from dbo.s_fielstype , dbo.s_count
group by s_fielstype.cvfptype

UNION

select   s_fielstype.csqltype , iid , s_count.计数 as 计数
from dbo.s_fielstype , dbo.s_count

例： 分组，小计，合计 ，明细 合并显示

 select

 '分组' as 标记, gd_jm.工资表编号  as 工资表编号, CONVERT(varchar(10), gd_jm.日期, 120)  as 日期, gd_js.员工编号  as 员工编号, gd_js.姓名  as 姓名,sum(gd_js.数量) as 数量,sum(gd_js.金额) as 金额, count(*) as 计数,                  0 as 显示顺序,'工资表编号+日期+员工编号+姓名 汇总' as 汇总标记,   15855596 as 行颜色
 from gd_js left join gd_jm
   on gd_js.计件单主表序号 = gd_jm.计件单主表序号
   left join gm_ygml as  v_gmygml
   on gd_js.员工编号 = v_gmygml.员工编号
   left join pm_bm
     on v_gmygml.部门编号 = pm_bm.部门编号
   left join gd_gzb
     on gd_jm.工资表编号 = gd_gzb.工资表编号
   left join gm_jml
     on gd_js.工序编号 = gm_jml.工序编号
   left join gm_ph
     on gd_js.批号 = gm_ph.批号  where 1=1  and gd_jm.工资表编号 LIKE  '%2019-10%' and v_gmygml.部门编号 LIKE  '10%'  group by gd_jm.工资表编号,gd_jm.日期,gd_js.员工编号,gd_js.姓名 

 UNION  
-- DQ.1397
-- 2
--


 select

 '小计' as 标记, gd_jm.工资表编号  as 工资表编号, CONVERT(varchar(10), gd_jm.日期, 120)  + ' 小计' as 日期, NULL  as 员工编号, NULL  as 姓名,sum(gd_js.数量) as 数量,sum(gd_js.金额) as 金额, count(*) as 计数,                  2 as 显示顺序,'工资表编号+日期 小计' as 汇总标记,   14473423 as 行颜色
 from gd_js left join gd_jm
   on gd_js.计件单主表序号 = gd_jm.计件单主表序号
   left join gm_ygml as  v_gmygml
   on gd_js.员工编号 = v_gmygml.员工编号
   left join pm_bm
     on v_gmygml.部门编号 = pm_bm.部门编号
   left join gd_gzb
     on gd_jm.工资表编号 = gd_gzb.工资表编号
   left join gm_jml
     on gd_js.工序编号 = gm_jml.工序编号
   left join gm_ph
     on gd_js.批号 = gm_ph.批号  where 1=1  and gd_jm.工资表编号 LIKE  '%2019-10%' and v_gmygml.部门编号 LIKE  '10%'  group by gd_jm.工资表编号,gd_jm.日期  

UNION  

-- DQ.1397
-- 0
--


 select

 '合计' as 标记,  '合计'   as 工资表编号, NULL  as 日期, NULL  as 员工编号, NULL  as 姓名,sum(gd_js.数量) as 数量,sum(gd_js.金额) as 金额, count(*) as 计数,                  4 as 显示顺序, '合计' as 汇总标记,   11445392 as 行颜色
 from gd_js left join gd_jm
   on gd_js.计件单主表序号 = gd_jm.计件单主表序号
   left join gm_ygml as  v_gmygml
   on gd_js.员工编号 = v_gmygml.员工编号
   left join pm_bm
     on v_gmygml.部门编号 = pm_bm.部门编号
   left join gd_gzb
     on gd_jm.工资表编号 = gd_gzb.工资表编号
   left join gm_jml
     on gd_js.工序编号 = gm_jml.工序编号
   left join gm_ph
     on gd_js.批号 = gm_ph.批号  where 1=1  and gd_jm.工资表编号 LIKE  '%2019-10%' and v_gmygml.部门编号 LIKE  '10%'   UNION   -- DQ.1397

 -- 明细数据部份
 select
 '明细' as 标记, gd_jm.工资表编号  as 工资表编号, CONVERT(varchar(10), gd_jm.日期, 120)  as 日期, gd_js.员工编号  as 员工编号, gd_js.姓名  as 姓名,gd_js.数量 as 数量,gd_js.金额 as 金额, convert ( Int , 1 ) as 计数,                 -1 as 显示顺序, '明细' as 汇总标记,   16438401  as 行颜色
 from gd_js left join gd_jm
   on gd_js.计件单主表序号 = gd_jm.计件单主表序号
   left join gm_ygml as  v_gmygml
   on gd_js.员工编号 = v_gmygml.员工编号
   left join pm_bm
     on v_gmygml.部门编号 = pm_bm.部门编号
   left join gd_gzb
     on gd_jm.工资表编号 = gd_gzb.工资表编号
   left join gm_jml
     on gd_js.工序编号 = gm_jml.工序编号
   left join gm_ph
     on gd_js.批号 = gm_ph.批号  where 1=1  and gd_jm.工资表编号 LIKE  '%2019-10%' and v_gmygml.部门编号 LIKE  '10%'
 order by 2,3,4,5