建表
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操作