备忘录--给自己

昨天，偶然发现的一个小知识点，以后也会把小知识点累积起来：

Declare @object_id int,

                @object_schema_name varchar(30)=object_schema_name(@object_id)

一直是有错误提示，原来在一个declare里面，未被识别，分开decalre就好了

-- 就 逗号跟 join 的 区别 做了一点测试：

;WITH a AS
(
  SELECT 'a' AS col
   UNION all 
  SELECT 'a' AS col
   UNION all
  SELECT 'b' AS col
   UNION all
  SELECT 'c' AS col
   UNION all
  SELECT 'b' AS col
   UNION all
  SELECT 'd' AS col
   UNION all
  SELECT 'f' AS col
)


SELECT  a.col AS acol,b.col AS bcol FROM a a , a b  

--没有加上 where 条件做筛选的时候应该是 笛卡尔积

WHERE a.col=b.col --加上 这个 筛选条件应该是 跟 inner join 等效。

Declare  @object_id int

Declare  @object_schema_name varchar(30)=object_schema_name(@object_id)

用来做测试表的，本不存在的表，个人觉得都是内存表，并不占用硬盘容量：

两种写法：

第一种：

--类似于通用表表达式

;WITH cte (NAME)

AS

(

SELECT 'Tom'

UNION

SELECT 'Jim'

UNION

SELECT 'Anna'

)

SELECT * FROM cte

第二种：

--莫名的表,有人说是DB2的写法，应该类似于ORACLE的dual表，说穿了是个子查询，所以应该是内嵌视图

SELECT * FROM

(VALUES

('Tom'),

('Jim'),

('Anna')

) a (Name)

如果要给某个字符赋值千万注意 NULL+str的情况： null 连接任何东东都是null，所以打印也打印不出的。

IF (NULL+'abc')IS NULL PRINT 'null please don''t USE'

查询出各科目成绩第一，第二名，不通过排名函数，类似于查找各部门工资最高，第二高的。。。

object:  test就是一张学生成绩表

SELECT stu_subject,MAX(score) FROM test GROUP BY stu_subject
UNION 
SELECT a.stu_subject,MAX(a.score) AS score FROM test a INNER JOIN (SELECT stu_subject,MAX(score) AS score FROM test  GROUP BY stu_subject) AS b 
ON a.score!=b.score AND a.stu_subject=b.stu_subject
GROUP BY a.stu_subject

Merge into 目的表 t

using 源表 s on t.col=s.col

when matched then 

update set t.col5=s.col5

when not matched by target （此处默认为by target） then

insert values(s.col1,s.col2,s.col3)

利用这个取代之前 insert where not exists ， update where exists  的写法。

下面是个例子：

DECLARE @test_tb TABLE(stu_name VARCHAR(30),stu_subject VARCHAR(30),score INT , Row_ID INT IDENTITY(1,1))
INSERT  INTO 
 @test_tb
SELECT 'Mary','Earth',80
UNION 
SELECT 'test','PE',200
MERGE INTO test t
USING 
(SELECT stu_name,stu_subject,score FROM @test_tb) tb ON (t.stu_name=tb.stu_name)
WHEN MATCHED THEN 
UPDATE SET t.score=tb.score
WHEN NOT MATCHED BY TARGET THEN 
INSERT VALUES(tb.stu_name,tb.stu_subject,tb.score);

SELECT * FROM test