昨天,偶然发现的一个小知识点,以后也会把小知识点累积起来:
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)
用来做测试表的,本不存在的表,个人觉得都是内存表,并不占用硬盘容量:
两种写法:
第一种:
如果要给某个字符赋值千万注意 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