CREATE TABLE t1 (
id INT IDENTITY,
f1 VARCHAR(255),
f2 INT,
f3 NTEXT,
f4 VARCHAR(255)
)
GO添加如下数据:
id f1 f2 f3 f4
---------------------------------
1 aa 12 dfdsfd ddsfds
2 bb 17 dfdsf ddsfa
3 cc 11 dsf7d dsfds
4 dd 32 vffgfgfd ssd
5 ee 17 ddf dsdds
6 ff 4 dfdfd dfdf
7 gg 17 ssss dfdf
8 hh 12 dfd s dfdhj
9 ii 15 adpo qqq
10 jj 17 dsebb aaaaaaa
11 kk 17 wwwww dfdsfd
12 ll 6 dffd sdsad我现在有一个两个查询
query1
SELECT * FROM t1id f1 f2 f3 f4
---------------------------------
1 aa 12 dfdsfd ddsfds
2 bb 17 dfdsf ddsfa
3 cc 11 dsf7d dsfds
4 dd 32 vffgfgfd ssd
5 ee 17 ddf dsdds
6 ff 4 dfdfd dfdf
7 gg 17 ssss dfdf
8 hh 12 dfd s dfdhj
9 ii 15 adpo qqq
10 jj 17 dsebb aaaaaaa
11 kk 17 wwwww dfdsfd
12 ll 6 dffd sdsadquery2id f1 f2 f3 f4
---------------------------------
2 bb 17 dfdsf ddsfa
5 ee 17 ddf dsdds
7 gg 17 ssss dfdf
10 jj 17 dsebb aaaaaaa
11 kk 17 wwwww dfdsfdSELECT * FROM t1 WHERE f2 = '17'
我想做query1 - query2差运算。在SQL Server 2000使用EXISTS关键字实现。得到id f1 f2 f3 f4
---------------------------------
1 aa 12 dfdsfd ddsfds
3 cc 11 dsf7d dsfds
4 dd 32 vffgfgfd ssd
6 ff 4 dfdfd dfdf
8 hh 12 dfd s dfdhj
9 ii 15 adpo qqq
12 ll 6 dffd sdsad请问如何做?这只是一个示例(在这个例子里面SELECT * FROM t1 WHERE f2 <> '17'就可以解决),实际代码比较复杂,必须使用EXISTS关键字,请避免使用IN关键字,因为涉及多个字段。尝试过
SELECT * FROM t1 WHERE NOT EXISTS
(SELECT * FROM t1 WHERE f2 = '17')SELECT * FROM t1 WHERE EXISTS
(SELECT NULL FROM t1 WHERE f2 = '17')
都不行谢谢!
id INT IDENTITY,
f1 VARCHAR(255),
f2 INT,
f3 NTEXT,
f4 VARCHAR(255)
)
GO添加如下数据:
id f1 f2 f3 f4
---------------------------------
1 aa 12 dfdsfd ddsfds
2 bb 17 dfdsf ddsfa
3 cc 11 dsf7d dsfds
4 dd 32 vffgfgfd ssd
5 ee 17 ddf dsdds
6 ff 4 dfdfd dfdf
7 gg 17 ssss dfdf
8 hh 12 dfd s dfdhj
9 ii 15 adpo qqq
10 jj 17 dsebb aaaaaaa
11 kk 17 wwwww dfdsfd
12 ll 6 dffd sdsad我现在有一个两个查询
query1
SELECT * FROM t1id f1 f2 f3 f4
---------------------------------
1 aa 12 dfdsfd ddsfds
2 bb 17 dfdsf ddsfa
3 cc 11 dsf7d dsfds
4 dd 32 vffgfgfd ssd
5 ee 17 ddf dsdds
6 ff 4 dfdfd dfdf
7 gg 17 ssss dfdf
8 hh 12 dfd s dfdhj
9 ii 15 adpo qqq
10 jj 17 dsebb aaaaaaa
11 kk 17 wwwww dfdsfd
12 ll 6 dffd sdsadquery2id f1 f2 f3 f4
---------------------------------
2 bb 17 dfdsf ddsfa
5 ee 17 ddf dsdds
7 gg 17 ssss dfdf
10 jj 17 dsebb aaaaaaa
11 kk 17 wwwww dfdsfdSELECT * FROM t1 WHERE f2 = '17'
我想做query1 - query2差运算。在SQL Server 2000使用EXISTS关键字实现。得到id f1 f2 f3 f4
---------------------------------
1 aa 12 dfdsfd ddsfds
3 cc 11 dsf7d dsfds
4 dd 32 vffgfgfd ssd
6 ff 4 dfdfd dfdf
8 hh 12 dfd s dfdhj
9 ii 15 adpo qqq
12 ll 6 dffd sdsad请问如何做?这只是一个示例(在这个例子里面SELECT * FROM t1 WHERE f2 <> '17'就可以解决),实际代码比较复杂,必须使用EXISTS关键字,请避免使用IN关键字,因为涉及多个字段。尝试过
SELECT * FROM t1 WHERE NOT EXISTS
(SELECT * FROM t1 WHERE f2 = '17')SELECT * FROM t1 WHERE EXISTS
(SELECT NULL FROM t1 WHERE f2 = '17')
都不行谢谢!
go
create table [tb]([id] int,[f1] varchar(2),[f2] int,[f3] varchar(8),[f4] varchar(7))
insert [tb]
select 1,'aa',12,'dfdsfd','ddsfds' union all
select 2,'bb',17,'dfdsf','ddsfa' union all
select 3,'cc',11,'dsf7d','dsfds' union all
select 4,'dd',32,'vffgfgfd','ssd' union all
select 5,'ee',17,'ddf','dsdds' union all
select 6,'ff',4,'dfdfd','dfdf' union all
select 7,'gg',17,'ssss','dfdf' union all
select 8,'hh',12,'dfd s','dfdhj' union all
select 9,'ii',15,'adpo','qqq' union all
select 10,'jj',17,'dsebb','aaaaaaa' union all
select 11,'kk',17,'wwwww','dfdsfd' union all
select 12,'ll',6,'dffd','sdsad'select *
from tb t
where not exists(select 1 from tb where id=t.id and f2 = '17')
--测试结果:
/*
id f1 f2 f3 f4
----------- ---- ----------- -------- -------
1 aa 12 dfdsfd ddsfds
3 cc 11 dsf7d dsfds
4 dd 32 vffgfgfd ssd
6 ff 4 dfdfd dfdf
8 hh 12 dfd s dfdhj
9 ii 15 adpo qqq
12 ll 6 dffd sdsad(7 行受影响)*/
(SELECT * FROM t1 WHERE t.id=t1.id and f2 = '17')
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-14 23:37:24
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[query1]
if object_id('[query1]') is not null drop table [query1]
go
create table [query1]([id] int,[f1] varchar(2),[f2] int,[f3] varchar(8),[f4] varchar(7),[C6] varchar(5))
insert [query1]
select 1,'aa',12,'dfdsfd','ddsfds',null union all
select 2,'bb',17,'dfdsf','ddsfa',null union all
select 3,'cc',11,'dsf7d','dsfds',null union all
select 4,'dd',32,'vffgfgfd','ssd',null union all
select 5,'ee',17,'ddf','dsdds',null union all
select 6,'ff',4,'dfdfd','dfdf',null union all
select 7,'gg',17,'ssss','dfdf',null union all
select 8,'hh',12,'dfd','s','dfdhj' union all
select 9,'ii',15,'adpo','qqq',null union all
select 10,'jj',17,'dsebb','aaaaaaa',null union all
select 11,'kk',17,'wwwww','dfdsfd',null union all
select 12,'ll',6,'dffd','sdsad',null
--> 测试数据:[query2]
if object_id('[query2]') is not null drop table [query2]
go
create table [query2]([id] int,[f1] varchar(2),[f2] int,[f3] varchar(5),[f4] varchar(7))
insert [query2]
select 2,'bb',17,'dfdsf','ddsfa' union all
select 5,'ee',17,'ddf','dsdds' union all
select 7,'gg',17,'ssss','dfdf' union all
select 10,'jj',17,'dsebb','aaaaaaa' union all
select 11,'kk',17,'wwwww','dfdsfd'
--------------开始查询--------------------------
select * from query1 t where not exists(select id from query2 where id=t.id )
----------------结果----------------------------
/* id f1 f2 f3 f4 C6
----------- ---- ----------- -------- ------- -----
1 aa 12 dfdsfd ddsfds NULL
3 cc 11 dsf7d dsfds NULL
4 dd 32 vffgfgfd ssd NULL
6 ff 4 dfdfd dfdf NULL
8 hh 12 dfd s dfdhj
9 ii 15 adpo qqq NULL
12 ll 6 dffd sdsad NULL(7 行受影响)*/
(SELECT * FROM t1 WHERE f2 = '17' and T.id=id )
(SELECT * FROM t1 WHERE t.id=t1.id and f2 = '17')
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-14 23:37:24
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[query1]
if object_id('[query1]') is not null drop table [query1]
go
create table [query1]([id] int,[f1] varchar(2),[f2] int,[f3] varchar(8),[f4] varchar(7),[C6] varchar(5))
insert [query1]
select 1,'aa',12,'dfdsfd','ddsfds',null union all
select 2,'bb',17,'dfdsf','ddsfa',null union all
select 3,'cc',11,'dsf7d','dsfds',null union all
select 4,'dd',32,'vffgfgfd','ssd',null union all
select 5,'ee',17,'ddf','dsdds',null union all
select 6,'ff',4,'dfdfd','dfdf',null union all
select 7,'gg',17,'ssss','dfdf',null union all
select 8,'hh',12,'dfd','s','dfdhj' union all
select 9,'ii',15,'adpo','qqq',null union all
select 10,'jj',17,'dsebb','aaaaaaa',null union all
select 11,'kk',17,'wwwww','dfdsfd',null union all
select 12,'ll',6,'dffd','sdsad',null
--> 测试数据:[query2]
if object_id('[query2]') is not null drop table [query2]
go
create table [query2]([id] int,[f1] varchar(2),[f2] int,[f3] varchar(5),[f4] varchar(7))
insert [query2]
select 2,'bb',17,'dfdsf','ddsfa' union all
select 5,'ee',17,'ddf','dsdds' union all
select 7,'gg',17,'ssss','dfdf' union all
select 10,'jj',17,'dsebb','aaaaaaa' union all
select 11,'kk',17,'wwwww','dfdsfd'
--------------开始查询--------------------------
select * from query1 t where not exists(select id from query2 where id=t.id and f2=17 )
----------------结果----------------------------
/* id f1 f2 f3 f4 C6
----------- ---- ----------- -------- ------- -----
1 aa 12 dfdsfd ddsfds NULL
3 cc 11 dsf7d dsfds NULL
4 dd 32 vffgfgfd ssd NULL
6 ff 4 dfdfd dfdf NULL
8 hh 12 dfd s dfdhj
9 ii 15 adpo qqq NULL
12 ll 6 dffd sdsad NULL(7 行受影响)*/
请回复下 http://topic.csdn.net/u/20091014/23/a6442489-ca28-45f7-a332-27c0a877e633.html
可以再给你们一些分。