【数据库】 SQL 常用语句

时间：2022-06-19 16:09:34

【数据库】 SQL 常用语句

1.批量导入
INSERT INTO Table2(field1,field2,...) SELECT value1,value2,... FROMTable1

要求目标表Table2必须存在

2.批量导入

SELECT vale1, value2 INTO Table2 FROM Table1

要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中

3.去重并保留一项

DELETE FROM  TableName WHERE ColName IN (select  ColName
  FROM TableName GROUP BY  ColName HAVING COUNT(ColName) > 1)
  AND id NOT in(  select  MAX(id)  FROM ColName GROUP BY  ProductId HAVING COUNT(ColName) > 1)

4.批量更新

UPDATE t1

SET Field1 = Field21, Field2 = Field22

FROM Table2 t2

WHERE ((t2.Field24 >= '') AND t1.fId = t4.fId);

5.分页

--max/top

SELECT TOP  * FROM pagetest

WHERE id>(SELECT MAX(id) FROM (SELECT TOP  id FROM pagetest ORDER BY id)a)

ORDER BY id

--row_number()

SELECT * FROM

(SELECT ROW_NUMBER()OVER(ORDER BY id)rownumber,* FROM pagetest)a

WHERE rownumber>= and rownumber<=9952
-- OFFSET FETCH (需要sql2012以上)
SELECT * FROM    pagetest
ORDER BY id ASC 
OFFSET @pageIndex * @pageSize ROWS 
FETCH NEXT @pageSize ROWS ONLY

6. 表同步 MERGE

  MERGE into [MergeDemo] as m -- 目标表

  using Test as t -- 源表

  on m.[title]=t.[name] -- 匹配条件

  when matched then update set m.[num]=t.[age] -- 匹配成功

  when not matched then insert values(t.[name],t.[age]) -- 目标表与源表不一致

  when not matched by source then delete -- 源表与目标表不一致

  output $action as [action] -- 输出操作

  , inserted.[title] [ititle],inserted.[num] [inum]

  , deleted.[title] [dtitle],deleted.[num] [dnum];

7. GROUP 加 TOP（每个 GROUP 取出 TOP 数据）

Declare @currentIndex int   -- 游标

Declare @totalRows    int    -- 总的组数

Declare @time    nvarchar()     -- 第一个分组列

SET @currentIndex=

  -- 按 第一个分组列 分组， 取出组名

  SELECT row_number()over(order by [time] asc) id,[time] INTO #temp1

  FROM (SELECT DISTINCT(CONVERT(VARCHAR(),create_time,)) [time]  FROM tableName) a

  SELECT @totalRows=COUNT() FROM #temp1 -- 总的组数

WHILE @currentIndex<=@totalRows   -- 按 组名 循环

BEGIN

  SELECT @time=[time] FROM #temp1 WHERE id=@currentIndex -- 取出当前组名

  -- 取出当前 组 拥有的数据， 再按照 第二分组列 分组 取 TOP

  SELECT TOP   colName 'Url', COUNT((colName)) 'Count',CONVERT(VARCHAR(),create_time,) 'Time',GETDATE()

  FROM tableName   WHERE [time] =  @time   GROUP BY colName   ORDER BY COUNT((colName)) DESC

SET @currentIndex =@currentIndex+1

END
 
 
 

相关文章