34,590
社区成员
发帖
与我相关
我的任务
分享
declare @t table(name char(1),type nvarchar(5),num int)
insert @t
select 'A','在线',1 union all
select 'B','修复',2 union all
select 'A','修复',3 union all
select 'B','在线',4 union all
select 'D','在线',5 union all
select 'D','修复',6 union all
select 'F','修复',7 union all
select 'C','在线',8 union all
select 'C','在线',9 union all
select 'E','在线',10 union all
select 'E','修复',11 union all
select 'E','修复',12 union all
select 'F','修复',13
select * from @t A
where not exists(select * from @t where name=A.name and type<>'在线')
name type num
---- ----- -----------
C 在线 8
C 在线 9
(2 行受影响)
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( NAME varchar(10), TYPE varchar(10) , NUM int )
go
insert tb SELECT
'A' , '在线', 1 UNION ALL SELECT
'B' ,'修复' , 2 UNION ALL SELECT
'A' , '修复' , 3 UNION ALL SELECT
'B' ,'在线' , 4 UNION ALL SELECT
'D' , '在线' , 5 UNION ALL SELECT
'D' , '修复' , 6 UNION ALL SELECT
'F' ,'修复' , 7 UNION ALL SELECT
'C' , '在线' , 8 UNION ALL SELECT
'C' , '在线' , 9 UNION ALL SELECT
'E' , '在线' , 10 UNION ALL SELECT
'E' , '修复' , 11 UNION ALL SELECT
'E' , '修复' , 12
go
SELECT * FROM TB WHERE TYPE='在线'
AND NAME NOT IN
(SELECT NAME FROM TB WHERE TYPE<>'在线')
NAME TYPE NUM
---------- ---------- -----------
C 在线 8
C 在线 9
(所影响的行数为 2 行)
SELECT * FROM tb AS A WHERE NOT EXISTS(SELECT * FROM tb WHERE A.name = name AND type <> '在线')
declare @t table(name char(1),type nvarchar(5),num int)
insert @t
select 'A','在线',1 union all
select 'B','修复',2 union all
select 'A','修复',3 union all
select 'B','在线',4 union all
select 'D','在线',5 union all
select 'D','修复',6 union all
select 'F','修复',7 union all
select 'C','在线',8 union all
select 'C','在线',9 union all
select 'E','在线',10 union all
select 'E','修复',11 union all
select 'E','修复',12 union all
select 'F','修复',13
------------------------
select * from @t A
where name not in (select name from @t where type<>'在线')
--------------------
(所影响的行数为 13 行)
name type num
---- ----- -----------
C 在线 8
C 在线 9
(所影响的行数为 2 行)
-------------------------------
--瘦狼阿亮 | 钱不是问题,问题是没钱
-------------------------------
--建立测试环境表
declare @tmptb table
(NAME varchar(10),TYPE varchar(10),NUM smallint)
--建立测试数据
insert into @tmptb
select 'A','在线',1 union all
select 'B','修复',2 union all
select 'A','修复',3 union all
select 'B','在线',4 union all
select 'D','在线',5 union all
select 'D','修复',6 union all
select 'F','修复',7 union all
select 'C','在线',8 union all
select 'C','在线',9 union all
select 'E','在线',10 union all
select 'E','修复',11 union all
select 'E','修复',12 union all
select 'F','修复',13
/*
(13 行受影响)
*/
--查看测试数据
select * from @tmptb
/*
NAME TYPE NUM
---------- ---------- ------
A 在线 1
B 修复 2
A 修复 3
B 在线 4
D 在线 5
D 修复 6
F 修复 7
C 在线 8
C 在线 9
E 在线 10
E 修复 11
E 修复 12
F 修复 13
(13 行受影响)
*/
select name,type,num from @tmptb a
where not exists(select 1 from @tmptb where a.name=name and type<>'在线')
/*
name type num
---------- ---------- ------
C 在线 8
C 在线 9
(2 行受影响)
*/
DECLARE @T TABLE (NAME VARCHAR(1),TYPE VARCHAR(4),NUM INT)
INSERT INTO @T
SELECT 'A','在线',1 UNION ALL
SELECT 'B','修复',2 UNION ALL
SELECT 'A','修复',3 UNION ALL
SELECT 'B','在线',4 UNION ALL
SELECT 'D','在线',5 UNION ALL
SELECT 'D','修复',6 UNION ALL
SELECT 'F','修复',7 UNION ALL
SELECT 'C','在线',8 UNION ALL
SELECT 'C','在线',9 UNION ALL
SELECT 'E','在线',10 UNION ALL
SELECT 'E','修复',11 UNION ALL
SELECT 'E','修复',12
select [name] from @t t group by [name]
having count(type)=(select count(*) from @T where name=t.name and type='在线' )
/*
name
c
*/
--如果类型只有2种 还可以这样
-- =========================================
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( NAME varchar(10), TYPE varchar(10) , NUM int )
go
insert tb SELECT
'A' , '在线', 1 UNION ALL SELECT
'B' ,'修复' , 2 UNION ALL SELECT
'A' , '修复' , 3 UNION ALL SELECT
'B' ,'在线' , 4 UNION ALL SELECT
'D' , '在线' , 5 UNION ALL SELECT
'D' , '修复' , 6 UNION ALL SELECT
'F' ,'修复' , 7 UNION ALL SELECT
'C' , '在线' , 8 UNION ALL SELECT
'C' , '在线' , 9 UNION ALL SELECT
'E' , '在线' , 10 UNION ALL SELECT
'E' , '修复' , 11 UNION ALL SELECT
'E' , '修复' , 12
go
select *
from tb a
where not exists(select * from tb where a.name=name and [type]='修复')
go
NAME TYPE NUM
---------- ---------- -----------
C 在线 8
C 在线 9
----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-25 16:48:48
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([NAME] varchar(1),[TYPE] varchar(4),[NUM] int)
insert [tb]
select 'A','在线',1 union all
select 'B','修复',2 union all
select 'A','修复',3 union all
select 'B','在线',4 union all
select 'D','在线',5 union all
select 'D','修复',6 union all
select 'F','修复',7 union all
select 'C','在线',8 union all
select 'C','在线',9 union all
select 'E','在线',10 union all
select 'E','修复',11 union all
select 'E','修复',12 union all
select 'F','修复',13
--------------开始查询--------------------------
select
*
from
tb t
where
not exists(select * from tb where name=t.name and [type]<>'在线')
----------------结果----------------------------
/*NAME TYPE NUM
---- ---- -----------
C 在线 8
C 在线 9
(2 行受影响)
*/
-------------------------------------------
-- Author : liangCK 小梁
-- Comment: 小梁 爱 兰儿
-- Date : 2009-08-25 16:49:05
-------------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (NAME VARCHAR(1),TYPE VARCHAR(4),NUM INT)
INSERT INTO @T
SELECT 'A','在线',1 UNION ALL
SELECT 'B','修复',2 UNION ALL
SELECT 'A','修复',3 UNION ALL
SELECT 'B','在线',4 UNION ALL
SELECT 'D','在线',5 UNION ALL
SELECT 'D','修复',6 UNION ALL
SELECT 'F','修复',7 UNION ALL
SELECT 'C','在线',8 UNION ALL
SELECT 'C','在线',9 UNION ALL
SELECT 'E','在线',10 UNION ALL
SELECT 'E','修复',11 UNION ALL
SELECT 'E','修复',12
--SQL查询如下:
SELECT * FROM @T AS A
WHERE NOT EXISTS(SELECT * FROM @T WHERE A.name = name AND type <> '在线')
/*
NAME TYPE NUM
---- ---- -----------
C 在线 8
C 在线 9
(2 row(s) affected)
*/
-- =========================================
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( NAME varchar(10), TYPE varchar(10) , NUM int )
go
insert tb SELECT
'A' , '在线', 1 UNION ALL SELECT
'B' ,'修复' , 2 UNION ALL SELECT
'A' , '修复' , 3 UNION ALL SELECT
'B' ,'在线' , 4 UNION ALL SELECT
'D' , '在线' , 5 UNION ALL SELECT
'D' , '修复' , 6 UNION ALL SELECT
'F' ,'修复' , 7 UNION ALL SELECT
'C' , '在线' , 8 UNION ALL SELECT
'C' , '在线' , 9 UNION ALL SELECT
'E' , '在线' , 10 UNION ALL SELECT
'E' , '修复' , 11 UNION ALL SELECT
'E' , '修复' , 12
go
select *
from tb a
where not exists(select * from tb where a.name=name and [type]<>'在线')
go
NAME TYPE NUM
---------- ---------- -----------
C 在线 8
C 在线 9
SELECT * FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE A.name = name AND type <> '在线')
select *
from tb a
where not exists(select * from tb where a.name=name and [type]<>'在线')