select count(1) from table where name in('a','b') 如果结果等于2就是同时存在了
A--name1 这个是一个字段还是2个啊
SELECT A,B FROM TB GROUP BY A,B HAVING COUNT(1)>=2?
-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-03-29 11:13:02 -- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) -- Mar 29 2009 10:27:29 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2) -------------------------------------------------------------------------- --> 生成测试数据表:tableIF NOT OBJECT_ID('[table]') IS NULL DROP TABLE [table] GO CREATE TABLE [table]([col1] NVARCHAR(10),[col2] NVARCHAR(10)) INSERT [table] SELECT N'A','name1' UNION ALL SELECT N'B','name2' UNION ALL SELECT N'C','name3' UNION ALL SELECT N'D','name4' UNION ALL SELECT N'E','name5' UNION ALL SELECT N'F','name6' UNION ALL SELECT N'G','name7' UNION ALL SELECT N'H','name8' GO --SELECT * FROM [table]-->SQL查询如下: select * from [table] t where exists(select 1 from [table] where col1<>t.col1 and col1 in('A','B')) and col1 in('A','B') /* col1 col2 ---------- ---------- A name1 B name2(2 行受影响) */delete [table] where col1='B' select * from [table] t where exists(select 1 from [table] where col1<>t.col1 and col1 in('A','B')) and col1 in('A','B') /* col1 col2 ---------- ----------(0 行受影响) */
---trygroup by having(count(*)>1)...
select * from tb where col1 in('A','B') and col1 in(select col1 from tb group by col1 having count(1)>=2)
如果结果等于2就是同时存在了
这个是一个字段还是2个啊
-- Author : htl258(Tony)
-- Date : 2010-03-29 11:13:02
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tableIF NOT OBJECT_ID('[table]') IS NULL
DROP TABLE [table]
GO
CREATE TABLE [table]([col1] NVARCHAR(10),[col2] NVARCHAR(10))
INSERT [table]
SELECT N'A','name1' UNION ALL
SELECT N'B','name2' UNION ALL
SELECT N'C','name3' UNION ALL
SELECT N'D','name4' UNION ALL
SELECT N'E','name5' UNION ALL
SELECT N'F','name6' UNION ALL
SELECT N'G','name7' UNION ALL
SELECT N'H','name8'
GO
--SELECT * FROM [table]-->SQL查询如下:
select * from [table] t
where exists(select 1 from [table] where col1<>t.col1 and col1 in('A','B'))
and col1 in('A','B')
/*
col1 col2
---------- ----------
A name1
B name2(2 行受影响)
*/delete [table] where col1='B'
select * from [table] t
where exists(select 1 from [table] where col1<>t.col1 and col1 in('A','B'))
and col1 in('A','B')
/*
col1 col2
---------- ----------(0 行受影响)
*/
---trygroup by having(count(*)>1)...
*
from
tb
where
col1 in('A','B')
and
col1 in(select col1 from tb group by col1 having count(1)>=2)