select实用知识点

2019独角兽企业重金招聘Python工程师标准>>>

建表

create table waw_school
(
 id int identity(1,1) primary key,
 name nvarchar(60),
 age tinyint,
 phone bigint,
 hobby nvarchar(512),
 note nvarchar(1024),
 utime datetime default getdate()
);

create table waw_class
(
 id int identity(1,1) primary key ,
 school_id int,
 name nvarchar(60),
 note nvarchar(1024),
 utime datetime default getdate()
)

----------------------------------------------------------------

《一》distinct在查询时候用到，用于对查询结果排重
select distinct school_id from waw_class
等同于
select school_id from waw_class
group by school_id

《二》子查询：在查询中，子查询结果集用于外围查询的限制条件
select * from waw_class a
where a.school_id in (select id from waw_school where name = 'zsq')
或
select * from waw_class a
where exists (select * from waw_school b where a.school_id = b.id and b.id = 1)
或
select * from waw_class a
where not exists (select * from waw_school b where a.school_id = b.id and b.id = 2)

《三》基于列的逻辑表达式
select a.id ,
a.school_id ,
case when a.school_id = 1 then 'zsq' when a.school_id = 2 then 'zxn' end as school,
a.name,a.note,a.utime
from waw_class a

《四》关于分组（group by）对于需要根据不同纬度统计时，只用group by就会很麻烦，例如：

下边我结合我在实际开发中的例子，提出新的解决方案：

--a,b,c      abc  ab  a

SELECT CAST(a.采集时间 AS DATE) AS 发布时间,a.正负面,a.信息类型,COUNT(1) FROM 监测执行表 a WITH(NOLOCK)

WHERE a.事件ID = 2014095560 AND a.采集时间 > '2017-05-10'

GROUP BY ROLLUP (CAST(a.采集时间 AS DATE),a.正负面,a.信息类型)

--a,b,c      abc  ab  ac  bc  a  b  c

SELECT CAST(a.采集时间 AS DATE) AS 发布时间,ISNULL(a.正负面,'中性') AS 正负面,ISNULL(a.信息类型,4) 信息类型,COUNT(1) FROM 监测执行表 a WITH(NOLOCK)

WHERE a.事件ID = 2014095560 AND a.采集时间 > '2017-05-10'

GROUP BY CUBE (CAST(a.采集时间 AS DATE),ISNULL(a.正负面,'中性'),ISNULL(a.信息类型,4) )

--a,b,c   a   bc 

SELECT CAST(a.采集时间 AS DATE) AS 发布时间,ISNULL(a.正负面,'中性') AS 正负面,ISNULL(a.信息类型,4) 信息类型,COUNT(1) FROM 监测执行表 a WITH(NOLOCK)

WHERE a.事件ID = 2014095560 AND a.采集时间 > '2017-05-10'

GROUP BY GROUPING SETS  (CAST(a.采集时间 AS DATE),(ISNULL(a.正负面,'中性'),ISNULL(a.信息类型,4) ) )

注意： 使用GROUPING SETS仅仅是一个可以少写些代码的语法糖.但实际情况是，GROUPING SETS在遇到多个条件时，聚合是一次性从数据库中取出所有需要操作的数据，在内存中对数据库进行聚合操作并生成结果。而UNION ALL是多次扫描表，将返回的结果进行UNION操作

 

 

转载于:https://my.oschina.net/u/3420885/blog/1624634