34,597
社区成员
发帖
与我相关
我的任务
分享急求一SQL 语句！要求由Table1得到Table2？
Table1
字段1 字段2
1001 TRUE
1003 FALSE
1006 FALSE
1002 TRUE
1002 FALSE
1004 FALSE
Table2
字段1 字段2
1003 FALSE
1006 FALSE
1004 FALSE
也就是从表1中筛选出字段2从来没有为TRUE的记录
字段1 字段2
1001 TRUE
1003 FALSE
1006 FALSE
1002 TRUE
1002 FALSE
1004 FALSE
Table2
字段1 字段2
1003 FALSE
1006 FALSE
1004 FALSE
也就是从表1中筛选出字段2从来没有为TRUE的记录
...全文
请发表友善的回复…
发表回复
幸运的意外 2008-10-23
- 打赏
- 举报
select
a.*
from
表 A
where
not exists
(select 1 from 表 where 编号=A.编号1 and 状态='true')
order by
编号
a.*
from
表 A
where
not exists
(select 1 from 表 where 编号=A.编号1 and 状态='true')
order by
编号
duanlijuan 2008-10-23
- 打赏
- 举报
SELECT *
FROM table
WHERE (PhoneID NOT IN
(SELECT 字段1
FROM table
WHERE 字段2 = 'TRUE'))
go
SELECT *
FROM table AS t
WHERE NOT EXISTS
(
SELECT *
FROM table
WHERE 字段1 =t.字段1
AND 字段2='PASS'
)
FROM table
WHERE (PhoneID NOT IN
(SELECT 字段1
FROM table
WHERE 字段2 = 'TRUE'))
go
SELECT *
FROM table AS t
WHERE NOT EXISTS
(
SELECT *
FROM table
WHERE 字段1 =t.字段1
AND 字段2='PASS'
)
csdyyr 2008-10-23
- 打赏
- 举报
[Quote=引用 3 楼 csdyyr 的回复:]
SQL codef1 f2----------- -----1003FALSE1006FALSE1004FALSE
[/Quote]
SQL codef1 f2----------- -----1003FALSE1006FALSE1004FALSE
[/Quote]
declare @tb table(f1 int, f2 varchar(5))
insert @tb
SELECT 1001, 'TRUE' UNION ALL
SELECT 1003, 'FALSE' UNION ALL
SELECT 1006, 'FALSE' UNION ALL
SELECT 1002, 'TRUE' UNION ALL
SELECT 1002, 'FALSE' UNION ALL
SELECT 1004, 'FALSE'
select * from @tb as a where not exists(select 1 from @tb where f1=a.f1 and f2='TRUE')
/*
f1 f2
----------- -----
1003 FALSE
1006 FALSE
1004 FALSE
*/
CN_SQL 2008-10-23
- 打赏
- 举报
declare @t table
(
col1 int,
col2 varchar(10)
)
insert @t select 1001,'TRUE'
union all select 1003,'FALSE'
union all select 1006,'FALSE'
union all select 1002,'TRUE'
union all select 1002,'FALSE'
union all select 1004,'FALSE'
select
a.*
from @t a
where NOT exists(
select
*
from @t
where col1 = a.col1
and col2 = 'TRUE')
csdyyr 2008-10-23
- 打赏
- 举报
f1 f2
----------- -----
1003 FALSE
1006 FALSE
1004 FALSE
liangCK 2008-10-23
- 打赏
- 举报
--> liangCK小梁 于2008-10-23
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (字段1 INT,字段2 VARCHAR(5))
INSERT INTO #T
SELECT 1001,'TRUE' UNION ALL
SELECT 1003,'FALSE' UNION ALL
SELECT 1006,'FALSE' UNION ALL
SELECT 1002,'TRUE' UNION ALL
SELECT 1002,'FALSE' UNION ALL
SELECT 1004,'FALSE'
--SQL查询如下:
SELECT *
FROM #T AS t
WHERE NOT EXISTS
(
SELECT *
FROM #T
WHERE 字段1=t.字段1
AND 字段2='True'
)
/*
字段1 字段2
----------- -----
1003 FALSE
1006 FALSE
1004 FALSE
(3 行受影响)
*/
fcuandy 2008-10-23
- 打赏
- 举报
select a.* from tb a
where not exists(select 1 from tb where 字段1=a.字段1 and 字段2-'true')
