34,593
社区成员
发帖
与我相关
我的任务
分享
1.
SELECT [...],RN=ROW_NUMBER() OVER(ORDER BY [XX]) INTO [表名.这里是每一次创建一个表] FROM ( [这里是一个很大的表是一段时间内的数据,distinct] EXCEPT [一个大数据表] )
2.
SELECT TOP 1 RN FROM [表名,刚才查入数据的表]
IF OBJECT_ID('TB_20110108_tmp_1010') IS NOT NULL
DROP TABLE TB_20110108_tmp_1010
SELECT T1.[SiteId],T1.[AdId], COUNT([Ip]) AS [ShowIp],0 AS [ClickIp],RN=ROW_NUMBER() OVER(ORDER BY T1.[SiteId],T1.[AdId]) INTO TB_20110108_tmp_1010
FROM (
SELECT DISTINCT [SiteId],[AdId],[Ip] FROM TB_20110108 A WHERE [RecTime]>=@startTime AND [RecTime]<@endTime
EXCEPT
SELECT [SiteId],[AdId],[Ip] FROM TB_AbsIp_20110108
) T1
GROUP BY T1.[SiteId],T1.[AdId]
1.
SELECT [...],RN=ROW_NUMBER() OVER(ORDER BY [XX]) INTO [表名.这里是每一次创建一个表] FROM ( [这里是一个很大的表是一段时间内的数据,distinct] EXCEPT [一个大数据表] )
如何从表A(总表)中找出表B(分表)中不存在的记录组合
假设表A和表B都只有两个字段id,name
如何用一句SQL返回表A中存在的id,name结果集而在表B中不存在的id,name结果集
select A.* from A left join B on A.id=B.id and A.name=B.name where B.id is null
select * from A where not exists(select top 1 * from B where A.ID=B.ID)
这两个都可以.
--前提:表中不能有text、ntext、image、cursor 数据类型的字段。
用CheckSum()最简单:
select * from A where checksum(*) not in (select checksum(*) from B)