常用sql汇总
查询重复数据
SELECT * FROM projects
WHERE name IN (SELECT name FROM projects GROUP BY name HAVING count(name) > 1)
--效率更高
SELECT *
FROM projects AS a
WHERE (name IN (SELECT name FROM
projects AS B WHERE a.id <> b.id))
批量数据处理 psql
use cattery
GO
CREATE TABLE RollbackDB.dbo.cattery_rollback
(
MasterId BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
id int NOT NULL,
title nvarchar(255),
subtitle nvarchar(255)
)
GO
INSERT INTO RollbackDB.dbo.cattery_rollback WITH(TABLOCK)
SELECT
c.id,
c.title,
c.subtitle
FROM dbo.projects c WITH(NOLOCK)
WHERE c.subtitle<>'' AND c.subtitle is not null;
GO
PRINT 'Patching legacy data to exchange project's title&subtitle in batch'
DECLARE @ImpactRows BIGINT=1,@batch_size INT=5000,@CurrentRow BIGINT=1
WHILE @ImpactRows>0
BEGIN
UPDATE c
SET c.title = list.subtitle,
c.subtitle = list.title
FROM dbo.projects c
INNER JOIN RollbackDB.dbo.cattery_rollback list ON c.id = list.id
WHERE list.MasterId>=@CurrentRow AND list.MasterId<@CurrentRow+@batch_size
SET @ImpactRows=@@rowcount
SET @CurrentRow=@CurrentRow+@batch_size
END
PRINT 'Patched legacy data to exchange project's title&subtitle in batch'
GO
