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