select name from ( select distinct name from aa where tj ='a' union all select distinct name from aa where tj ='b' ) t group by name having count(1) = 2
select * from AA where tj='a' and tj='c'
create table aa(name int, tj varchar(10)) insert into aa values(1 , 'a') insert into aa values(1 , 'b') insert into aa values(1 , 'c') insert into aa values(2 , 'a') insert into aa values(2 , 'd') insert into aa values(3 , 'b') goselect name from ( select distinct name from aa where tj ='a' union all select distinct name from aa where tj ='b' ) t group by name having count(1) = 2 /* name ----------- 1(所影响的行数为 1 行) */ select name from ( select distinct name from aa where tj ='a' union all select distinct name from aa where tj ='c' ) t group by name having count(1) = 2 /* name ----------- 1(所影响的行数为 1 行) */ drop table aa
select name from ( select name from aa where tj in ('a','c') ) t group by name having count(1) = 2
--> 测试数据:@tb declare @tb table([name] int,[tj] varchar(1)) insert @tb select 1,'a' union all select 1,'b' union all select 1,'c' union all select 2,'a' union all select 2,'d' union all select 3,'b' select name from @tb t where tj='a' and exists(select 1 from @tb where name=t.name and tj='b')/*1*/
if object_id('ta') is not null drop table ta go create table ta(namE int,TJ VARCHAR(1)) insert into ta select 1, 'a' union all select 1, 'b' union all select 1, 'c' union all select 2, 'a' union all select 2, 'd' union all select 3, 'b'select distinct name from ta where name in(SELECT name from ta where TJ='a') and name in (select name from ta where TJ='c')name ----------- 1(1 行受影响)
create table aa(name int, tj varchar(10)) insert into aa values(1 , 'a') insert into aa values(1 , 'b') insert into aa values(1 , 'c') insert into aa values(2 , 'a') insert into aa values(2 , 'd') insert into aa values(3 , 'b')select name from ( select name from aa where tj in ('a','c') ) t group by name having count(1) = 2 /* name ----------- 1 */
--> 测试数据:@tb declare @tb table([name] int,[tj] varchar(1)) insert @tb select 1,'a' union all select 1,'b' union all select 1,'c' union all select 2,'a' union all select 2,'d' union all select 3,'b'
select t.name from @tb t join @tb t1 on t.name=t1.name and t.tj='a' and t1.tj='c'/*1 */
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-10-12 16:08:43 -- 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.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([name] int,[tj] varchar(1)) insert [tb] select 1,'a' union all select 1,'b' union all select 1,'c' union all select 2,'a' union all select 2,'d' union all select 3,'b' --------------开始查询-------------------------- select name from tb t where exists(select 1 from tb where name=t.name and tj='b') and tj='a' ----------------结果---------------------------- /* name ----------- 1(1 行受影响) */
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-10-12 16:08:43 -- 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.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([name] int,[tj] varchar(1)) insert [tb] select 1,'a' union all select 1,'b' union all select 1,'c' union all select 2,'a' union all select 2,'d' union all select 3,'b' --------------开始查询-------------------------- select name from tb t where exists(select 1 from tb where name=t.name and tj='c') and tj='a' ----------------结果---------------------------- /* name ----------- 1(1 行受影响) */
go CREATE TABLE DD (name varchar(5),tj varchar(5))go insert into DD values(1,'a') insert into DD values(1,'b') insert into DD values(1,'c') insert into DD values(2,'a') insert into DD values(2,'b') insert into DD values(3,'d')select * from ddselect distinct name from dd where name in(SELECT name from dd where TJ='a') and name in (select name from dd where TJ='c')go drop table dd
有一个表AA name tj type 1 a 是 1 b 嗯 1 c 好 2 a 可以 2 d 不对 3 b 正确 要求当tj字段的值既等于a又等于c时(tj = ‘a’又要tj = ‘c’) 也就是说我的查询条件是a、c,我要查的结果是name = 1,type = 是,好解决加分。O(∩_∩)O~
(
select distinct name from aa where tj ='a'
union all
select distinct name from aa where tj ='b'
) t
group by name having count(1) = 2
select * from AA where tj='a' and tj='c'
insert into aa values(1 , 'a')
insert into aa values(1 , 'b')
insert into aa values(1 , 'c')
insert into aa values(2 , 'a')
insert into aa values(2 , 'd')
insert into aa values(3 , 'b')
goselect name from
(
select distinct name from aa where tj ='a'
union all
select distinct name from aa where tj ='b'
) t
group by name having count(1) = 2
/*
name
-----------
1(所影响的行数为 1 行)
*/
select name from
(
select distinct name from aa where tj ='a'
union all
select distinct name from aa where tj ='c'
) t
group by name having count(1) = 2
/*
name
-----------
1(所影响的行数为 1 行)
*/
drop table aa
(
select name from aa where tj in ('a','c')
) t group by name having count(1) = 2
declare @tb table([name] int,[tj] varchar(1))
insert @tb
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'a' union all
select 2,'d' union all
select 3,'b' select name from @tb t where tj='a' and exists(select 1 from @tb where name=t.name and tj='b')/*1*/
if object_id('ta') is not null drop table ta
go
create table ta(namE int,TJ VARCHAR(1))
insert into ta select
1, 'a' union all select
1, 'b' union all select
1, 'c' union all select
2, 'a' union all select
2, 'd' union all select
3, 'b'select distinct name from ta
where name in(SELECT name from ta where TJ='a')
and name in (select name from ta where TJ='c')name
-----------
1(1 行受影响)
insert into aa values(1 , 'a')
insert into aa values(1 , 'b')
insert into aa values(1 , 'c')
insert into aa values(2 , 'a')
insert into aa values(2 , 'd')
insert into aa values(3 , 'b')select name from
(
select name from aa where tj in ('a','c')
) t group by name having count(1) = 2
/*
name
-----------
1
*/
declare @tb table([name] int,[tj] varchar(1))
insert @tb
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'a' union all
select 2,'d' union all
select 3,'b'
select t.name from @tb t join @tb t1 on t.name=t1.name and t.tj='a' and t1.tj='c'/*1
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-12 16:08:43
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] int,[tj] varchar(1))
insert [tb]
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'a' union all
select 2,'d' union all
select 3,'b'
--------------开始查询--------------------------
select name from tb t where exists(select 1 from tb where name=t.name and tj='b') and tj='a'
----------------结果----------------------------
/* name
-----------
1(1 行受影响)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-12 16:08:43
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] int,[tj] varchar(1))
insert [tb]
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'a' union all
select 2,'d' union all
select 3,'b'
--------------开始查询--------------------------
select name from tb t where exists(select 1 from tb where name=t.name and tj='c') and tj='a'
----------------结果----------------------------
/* name
-----------
1(1 行受影响)
*/
CREATE TABLE DD (name varchar(5),tj varchar(5))go
insert into DD values(1,'a')
insert into DD values(1,'b')
insert into DD values(1,'c')
insert into DD values(2,'a')
insert into DD values(2,'b')
insert into DD values(3,'d')select * from ddselect distinct name from dd
where name in(SELECT name from dd where TJ='a')
and name in (select name from dd where TJ='c')go
drop table dd
name tj type
1 a 是
1 b 嗯
1 c 好
2 a 可以
2 d 不对
3 b 正确
要求当tj字段的值既等于a又等于c时(tj = ‘a’又要tj = ‘c’)
也就是说我的查询条件是a、c,我要查的结果是name = 1,type = 是,好解决加分。O(∩_∩)O~