34,590
社区成员
发帖
与我相关
我的任务
分享
--借TONY哥的数据
----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-05-04 19:10:03
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [a]
IF OBJECT_ID('[a]') IS NOT NULL
DROP TABLE [a]
GO
CREATE TABLE [a] ([id] [int],[sz_id_user] [nvarchar](10),[sz_month] [int])
INSERT INTO [a]
SELECT '1','a','32' UNION ALL
SELECT '2','b','34'
--> 生成测试数据表: [b]
IF OBJECT_ID('[b]') IS NOT NULL
DROP TABLE [b]
GO
CREATE TABLE [b] ([id] [int],[sz_id_user] [nvarchar](10),[sz_month] [int])
INSERT INTO [b]
SELECT '1','b','34' UNION ALL
SELECT '2','c','35'
--SELECT * FROM [a]
--SELECT * FROM [b]
-->SQL查询如下:
select * from b where checksum(sz_id_user,sz_month) in (select checksum(sz_id_user,sz_month) from a)
/*
id sz_id_user sz_month
----------- ---------- -----------
1 b 34
(1 行受影响)
*/
select B.* from A, B where A.sz_id_user=B.sz_id_user and A.sz_month=B.sz_month
select * from b checksum(sz_id_user,sz_month) in (select checksum(sz_id_user,sz_month) from a)
----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-05-04 19:10:03
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [a]
IF OBJECT_ID('[a]') IS NOT NULL
DROP TABLE [a]
GO
CREATE TABLE [a] ([id] [int],[sz_id_user] [nvarchar](10),[sz_month] [int])
INSERT INTO [a]
SELECT '1','a','32' UNION ALL
SELECT '2','b','34'
--> 生成测试数据表: [b]
IF OBJECT_ID('[b]') IS NOT NULL
DROP TABLE [b]
GO
CREATE TABLE [b] ([id] [int],[sz_id_user] [nvarchar](10),[sz_month] [int])
INSERT INTO [b]
SELECT '1','b','34' UNION ALL
SELECT '2','c','35'
--SELECT * FROM [a]
--SELECT * FROM [b]
-->SQL查询如下:
select * from b where exists(select 1 from a where b.sz_id_user=a.sz_id_user and b.sz_month=a.sz_month)
/*
id sz_id_user sz_month
----------- ---------- -----------
1 b 34
(1 行受影响)
*/
select * from b where exists(select 1 from a where b.sz_id_user=a.sz_id_user and b.sz_month=a.sz_month)