select * from News where poster in (select [name] from user where group='admin')
select * from News where poster in(select [name] from user where group ='admin')
select t.* from news t join [user] r on t.poster=r.name where r.group='admin'
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2010-01-26 16:38:04 -- 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) -- ---------------------------------------------------------------- --> 测试数据:[user] if object_id('[user]') is not null drop table [user] go create table [user]([id] int,[name] varchar(6),[group] varchar(10)) insert [user] select 1,'李小军','admin' union all select 2,'张大磊','admin' union all select 3,'徐中华','superadmin' union all select 4,'田意原','member' --> 测试数据:[news] if object_id('[news]') is not null drop table [news] go create table [news]([id] int,[title] varchar(10),[content] varchar(4),[poster] varchar(6),[date] datetime) insert [news] select 1,'关于...','****','李小军','20091102' union all select 2,'公司在..','****','徐中华','20091201' union all select 3,'关于强调..','****','田意原','20091211' union all select 4,'财务结算..','****','张大磊','20091226' --------------开始查询-------------------------- select * from news t where exists(select 1 from [user] where name=t.poster and [group]='admin') ----------------结果---------------------------- /* id title content poster date ----------- ---------- ------- ------ ----------------------- 1 关于... **** 李小军 2009-11-02 00:00:00.000 4 财务结算.. **** 张大磊 2009-12-26 00:00:00.000(2 行受影响)*/
select * from News T where EXISTSI(SELECT 1 From [user] where NAME=T.poster ) AND [group]='admin'
select title from news where poster in (select poster from user where poster name in (select name from user where group='admain') )
from news t join [user] r
on t.poster=r.name
where r.group='admin'
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-01-26 16:38:04
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[user]
if object_id('[user]') is not null drop table [user]
go
create table [user]([id] int,[name] varchar(6),[group] varchar(10))
insert [user]
select 1,'李小军','admin' union all
select 2,'张大磊','admin' union all
select 3,'徐中华','superadmin' union all
select 4,'田意原','member'
--> 测试数据:[news]
if object_id('[news]') is not null drop table [news]
go
create table [news]([id] int,[title] varchar(10),[content] varchar(4),[poster] varchar(6),[date] datetime)
insert [news]
select 1,'关于...','****','李小军','20091102' union all
select 2,'公司在..','****','徐中华','20091201' union all
select 3,'关于强调..','****','田意原','20091211' union all
select 4,'财务结算..','****','张大磊','20091226'
--------------开始查询--------------------------
select * from news t where exists(select 1 from [user] where name=t.poster and [group]='admin')
----------------结果----------------------------
/* id title content poster date
----------- ---------- ------- ------ -----------------------
1 关于... **** 李小军 2009-11-02 00:00:00.000
4 财务结算.. **** 张大磊 2009-12-26 00:00:00.000(2 行受影响)*/
where EXISTSI(SELECT 1 From [user] where NAME=T.poster ) AND [group]='admin'
where poster in
(select poster from user where poster name in
(select name from user where group='admain') )