linux查看端口是否可达,select实用知识点

建表

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