SQL复合查询&存储过程笔记

1.各子句执行顺序

执行顺序 ：from > on > where > Group by > having > select > DISTINCT > order by > TOP

(8)SELECT (9)DISTINCT (11)
(1)FROM [left_table]
(3) JOIN
(2)ON
(4)WHERE
(5)GROUP BY
(6)WITH
(7)HAVING
(10)ORDER BY

2.内连接（不是innerjoin，类似自连接？以外表数据为依据查询內表）
查询各科目分数前两名的学生
select * from Stu S1 where StuNo in(select top 2 StuNo from Stu where CouNo=S1.CouNo order by Gra DESC)

3.内外、左右连接
内连接：严格按照join on的条件进行筛选，无null
左连接：以左表为主表，返回条数与左表总数相同（join on条件判断右表无对应值的补null）
右连接：与左连接相反

4.SQL内置函数及综合应用
DATEDIFF(mm,GETDATE(),min(日期))
len([出生年月])>=4
ISNUMERIC(Left([出生年月],4))=1

是否首次消费后大于6个月
select B.[病人编号]
,[消费编号]
,[项目编号]
,[项目名称]
,[日期]
,是否首次消费后大于6个月
from [newdata].[dbo].[收费记录正常] A
right join
(SELECT [病人编号]
,( case when DATEDIFF(mm,GETDATE(),min(日期))>6 then ‘是’ else ‘否 ’ end) as ‘是否首次消费后大于6个月’
FROM [newdata].[dbo].[收费记录正常] group by [病人编号] ) B
on A.[病人编号]=B .病人编号

5.动态分组统计

静态
根据门诊及付款方式分类统计（付款方式有确定集合）
select 门诊,
sum(case when 付款方式=’现金’then 合计 ELSE 0 END) 现金,
sum(case when 付款方式=’代金卷’then 合计 ELSE 0 END) 代金卷,
sum(case when 付款方式=’到店团购’ then 合计 ELSE 0 END) 到店团购,
sum(case when 付款方式=’调账’then 合计 ELSE 0 END) 调账,
sum(case when 付款方式=’团购抵用’then 合计 ELSE 0 END) 团购抵用,
sum(case when 付款方式=’银行卡’then 合计 ELSE 0 END) 银行卡,
sum(case when 付款方式=’余额支付’then 合计 ELSE 0 END) 余额支付
from
(SELECT
sum([实收金额]) 合计
,[付款方式]
,[门诊]
FROM [newdata].[dbo].[收费清单表]
where [日期] between ‘2015-01-01 00:00:00’ and ‘2016-01-01 00:00:00’
group by [付款方式]
,[门诊])A group by 门诊

SELECT 门诊, 现金,代金券,到店团购,调账,团购抵用,银行卡,余额支付
FROM (SELECT
sum([实收金额]) 合计
,[付款方式]
,[门诊]
FROM [newdata].[dbo].[收费清单表]
where [日期] between ‘2015-01-01 00:00:00’ and ‘2016-01-01 00:00:00’
group by [付款方式]
,[门诊])X
pivot( sum(合计) FOR [付款方式] IN (现金,代金券,到店团购,调账,团购抵用,银行卡,余额支付))a

动态 (付款方式未知)
CREATE PROCEDURE proc_ClassifiedStatistic
AS

BEGIN
DECLARE @PLANTS VARCHAR(500)=”
DECLARE @SQL NVARCHAR(1000)
DECLARE @STime NVARCHAR(1000) =’2015-01-01 00:00:00’
DECLARE @ETime NVARCHAR(1000) =’2015-02-01 00:00:00’

SELECT @PLANTS = @PLANTS + '[' + 付款方式 + '],'  
FROM (select  distinct [付款方式] from [收费清单表] where [日期] between @STime and @ETime  group by [付款方式]
             ,[门诊] having sum([实收金额])>0) T  

SET @PLANTS= LEFT(@PLANTS, LEN(@PLANTS)-1)  

SET @SQL=  
'SELECT *
     FROM (SELECT 
              sum([实收金额]) 合计   
              ,[付款方式]     
              ,[门诊]   
          FROM [newdata].[dbo].[收费清单表]
          where [日期] between '''+@STime+''' and  '''+@ETime+'''  group by [付款方式]
             ,[门诊])X
pivot( sum(合计) FOR [付款方式] IN ({0}))T'  
SET @SQL= REPLACE(@SQL,'{0}',@PLANTS)  
EXEC sp_executesql @SQL  

END

exec proc_ClassifiedStatistic

drop proc proc_ClassifiedStatistic

---

DECLARE @列 NVARCHAR(200)
DECLARE @sql NVARCHAR(4000)

SELECT @列 = LEFT(列, Len(列) - 1)
FROM (SELECT (SELECT DISTINCT [付款方式] + ‘,’
FROM [收费清单表]
WHERE [日期] BETWEEN ‘2015-01-01 00:00:00’ AND ‘2016-01-01 00:00:00’
–AND 门诊 = ‘宝安门诊’
GROUP BY [付款方式],
[门诊]
HAVING ( Sum(实收金额) > 0 )
FOR xml path(”)) AS 列) AS tb2

---

SET @sql=’
select * from (
SELECT Sum([实收金额]) 合计,
[付款方式],
[门诊]
FROM [收费清单表]
WHERE [日期] BETWEEN ”2015-01-01 00:00:00” AND ”2016-01-01 00:00:00”
–and 门诊=”宝安门诊”
GROUP BY [付款方式],
[门诊]
) as tb pivot(max(合计) for 付款方式 in (
’ + @列 + ’
)) a’

EXEC(@sql)

（比上图少一个分类）

6.行变列（多行的某一列变为一行的某一列）
select B.字典分类2 +’：’
+Left(step,len(step)-1) as result from
(select 字典分类2,
(select 字典内容1 +IsNull(‘,’+字典分类5,”)+’|’ from 公共字典表 where 字典分类2=A.字典分类2
for xml path (”))
as step
from 公共字典表 A where 字典名称=’病程名称’ group by 字典分类2) B

---