SQL综合应用（以SqlServer为例）

需求：做一个统计作业票数量的柱状图

数据的格式如下

series : [
        {
            name:'用火',
            type:'bar',
            stack: '作业',
            barWidth : 50,
            data:[320, 332, 301, 334, 390]
        },
        {
            name:'受限空间',
            type:'bar',
            stack: '作业',
            data:[120, 132, 101, 134, 90]
        },

name的值是作业类型、data里面的数据是每个月的数据汇总。

1.先看一眼用到的表 作业开票表（TB_HSE_BILL_ZYKP） 和 字典表 （TB_HSE_DICTIONARY_DETAILS）
因为这两个表是存在不同的数据库里的，关联的时候相当于跨库查询。在使用的时候需要加上前缀 ： 数据库名.dbo.表名 如 HSE_BILL.dbo.TB_HSE_BILL_ZYKP
看一眼表中的数据 SELECT ZYLX FROM HSE_BILL.dbo.TB_HSE_BILL_ZYKP

主要用到的作业票类型字段（ZYLX）与数据是一对多的关系所以多个类型的用逗号隔开了
ps:在我与DBA 的接触中，我所做的一对多的关系都是需要建从表的，这里没有建从表是不太规范的。
因为我们做的是统计，这里的ZYLX是逗号拼接的要当作多条数据处理的。所以这里我们要把数据处理一下。这里又用到了列转行

select  
     c1.U_REGISTIME,c2.ZYLX 
 from  
     (select U_REGISTIME,ZYLX=convert(xml,' <root> <v>'+replace(ZYLX,',',' </v> <v>')+' </v> </root>') from HSE_BILL.dbo.TB_HSE_BILL_ZYKP)c1 
 outer apply 
     (select ZYLX=C.v.value('.','nvarchar(100)') from c1.ZYLX.nodes('/root/v')C(v))c2  WHERE U_REGISTIME > (
		SELECT
			DateAdd(MONTH ,- 12, getdate())
	)  

结果如下

这里行转列用到了xml，我建议不用理解，直接百度过来，把自己的表名和字段名替换进来用就行。
因为数据比较多，而且我们只需要用到当前时间12个月之前的数据，所以用到了函数

SELECT   DateAdd(MONTH ,- 12, getdate())   

dateadd(，，，) 返回一个日期，第一个参数是Month，第二个参数是-12 第三个参数是当前日期，就代表返回当前日期 12 月前的日期，（同理2年前就是 SELECT DateAdd(YEAR ,- 2, getdate()) ）
我们让数据的判断条件 登记时间 > 12个月前的当前时间，就过滤出来12个月前的所有数据了。

但是还有一个问题：如果某个月没数据怎么办，那样是查不出来的（这个月的数据不存在）。但是我们做统计的时候，即使没有数据我们也要表示出来，数量用0来表示
所以我们自然而然想到了左连接。
我们最后要的数据是每种作业票类型，12个月的数据。左表应该是每一种作业类型对应上12个月。但是这个表也是不存在的。
所以要凑一个表。首先是时间表，我发现数据库里早就存在这个表，估计是为了统计分析而创建的，内容如下

SELECT  *  FROM    HSE_DEV.dbo.DimDate 

类型的话，我们取字典就好。他和时间表一合并就成了

SELECT * FROM (SELECT  MONTH  FROM    HSE_DEV.dbo.DimDate   WHERE YEAR= YEAR (GETDATE()) GROUP by MONTH) t1,  
  --类型表
 (SELECT DETAILS_CODE,DETAILS_NAME FROM HSE_DEV.dbo.TB_HSE_DICTIONARY_DETAILS   WHERE  TYPE_CODE='11278' AND ORGCODE='10010031' ) t2

这里需要注意时间表和类型表本身是没有关联的，我们求的是他们的笛卡儿积
select * from a,b
左表和右表都有了，关联起来

 SELECT t3.*,CASE WHEN  t4.Num IS NULL THEN '0' ELSE t4.Num END AS Num FROM
 (  --时间表
 SELECT * FROM (SELECT  MONTH  FROM    HSE_DEV.dbo.DimDate   WHERE YEAR= YEAR (GETDATE()) GROUP by MONTH) t1,  
  --类型表
 (SELECT DETAILS_CODE,DETAILS_NAME FROM HSE_DEV.dbo.TB_HSE_DICTIONARY_DETAILS   WHERE  TYPE_CODE='11278' AND ORGCODE='10010031' ) t2) t3
 --数据表
LEFT JOIN (
SELECT
a.ZYLX,	MONTH (U_REGISTIME) as Month ,cast(COUNT(a.ZYLX) as varchar) as Num
FROM
	 (select  
     c1.U_REGISTIME,c2.ZYLX 
 from  
     (select U_REGISTIME,ZYLX=convert(xml,' <root> <v>'+replace(ZYLX,',',' </v> <v>')+' </v> </root>') from HSE_BILL.dbo.TB_HSE_BILL_ZYKP)c1 
 outer apply 
     (select ZYLX=C.v.value('.','nvarchar(100)') from c1.ZYLX.nodes('/root/v')C(v))c2  WHERE U_REGISTIME > (
		SELECT
			DateAdd(MONTH ,- 12, getdate())
	)   )  a
LEFT JOIN
   (SELECT DETAILS_CODE,DETAILS_NAME FROM HSE_DEV.dbo.TB_HSE_DICTIONARY_DETAILS   WHERE  TYPE_CODE='11278' AND orgcode='10010031') b
  ON a.ZYLX = b.DETAILS_CODE 
	GROUP BY MONTH (a.U_REGISTIME),a.ZYLX
) t4  ON t3.Month= t4.Month and t3.DETAILS_CODE=t4.ZYLX

如图

最后一步，因为我们需要的数据是每一种作业类型12个月的数据，所以每种类型的12条数据要合成一条数据。用到了的是行转列
行转列的用法请参考
https://blog.youkuaiyun.com/weixin_42206732/article/details/88092196
也不需要理解，直接把内容替换即可。

SELECT
DETAILS_CODE,DETAILS_NAME ,Num = ( STUFF(( SELECT ', ' + Num
FROM (
 SELECT t3.*,CASE WHEN  t4.Num IS NULL THEN '0' ELSE t4.Num END AS Num FROM
 (  --时间表
 SELECT * FROM (SELECT  MONTH  FROM  HSE_DEV.dbo.DimDate   WHERE YEAR= YEAR (GETDATE()) GROUP by MONTH) t1,  
  --类型表
 (SELECT DETAILS_CODE,DETAILS_NAME FROM HSE_DEV.dbo.TB_HSE_DICTIONARY_DETAILS   WHERE  TYPE_CODE='11278' AND ORGCODE='10010031' ) t2) t3
 --数据表
LEFT JOIN (
SELECT
a.ZYLX,	MONTH (U_REGISTIME) as Month ,cast(COUNT(a.ZYLX) as varchar) as Num
FROM
	 (select  
     c1.U_REGISTIME,c2.ZYLX 
 from  
     (select U_REGISTIME,ZYLX=convert(xml,' <root> <v>'+replace(ZYLX,',',' </v> <v>')+' </v> </root>') from HSE_BILL.dbo.TB_HSE_BILL_ZYKP)c1 
 outer apply 
     (select ZYLX=C.v.value('.','nvarchar(100)') from c1.ZYLX.nodes('/root/v')C(v))c2 )  a
LEFT JOIN
   (SELECT DETAILS_CODE,DETAILS_NAME FROM HSE_DEV.dbo.TB_HSE_DICTIONARY_DETAILS   WHERE  TYPE_CODE='11278' AND orgcode='10010031') b
   ON a.ZYLX = b.DETAILS_CODE 
WHERE
	a.U_REGISTIME > (
		SELECT
			DateAdd(MONTH ,- 12, getdate())
	)  GROUP BY MONTH (a.U_REGISTIME),a.ZYLX
) t4  ON t3.Month= t4.Month and t3.DETAILS_CODE=t4.ZYLX
)AS R
WHERE R.DETAILS_CODE = Test.DETAILS_CODE
FOR XML PATH( '')), 1, 1, '') )
FROM
(
 SELECT t3.*,CASE WHEN  t4.Num IS NULL THEN '0' ELSE t4.Num END AS Num FROM
 (  --时间表
 SELECT * FROM (SELECT  MONTH  FROM    HSE_DEV.dbo.DimDate   WHERE YEAR= YEAR (GETDATE()) GROUP by MONTH) t1,  
  --类型表
 (SELECT DETAILS_CODE,DETAILS_NAME FROM HSE_DEV.dbo.TB_HSE_DICTIONARY_DETAILS   WHERE  TYPE_CODE='11278' AND ORGCODE='10010031' ) t2) t3
 --数据表
LEFT JOIN (
SELECT
a.ZYLX,	MONTH (U_REGISTIME) as Month ,cast(COUNT(a.ZYLX) as varchar) as Num
FROM
	 (select  
     c1.U_REGISTIME,c2.ZYLX 
 from  
     (select U_REGISTIME,ZYLX=convert(xml,' <root> <v>'+replace(ZYLX,',',' </v> <v>')+' </v> </root>') from HSE_BILL.dbo.TB_HSE_BILL_ZYKP)c1 
 outer apply 
     (select ZYLX=C.v.value('.','nvarchar(100)') from c1.ZYLX.nodes('/root/v')C(v))c2  WHERE U_REGISTIME > (
		SELECT
			DateAdd(MONTH ,- 12, getdate())
	)   )  a
LEFT JOIN
   (SELECT DETAILS_CODE,DETAILS_NAME FROM HSE_DEV.dbo.TB_HSE_DICTIONARY_DETAILS   WHERE  TYPE_CODE='11278' AND orgcode='10010031') b
  ON a.ZYLX = b.DETAILS_CODE 
	GROUP BY MONTH (a.U_REGISTIME),a.ZYLX
) t4  ON t3.Month= t4.Month and t3.DETAILS_CODE=t4.ZYLX
) AS Test
GROUP BY DETAILS_CODE,DETAILS_NAME

如图