表结构,查询条件如下,自己也写了个查询语句,但总觉得不大好,请教大家是否有更好查询办法if object_id('[CrowdMember]') is not null
drop table [CrowdMember]
go
if object_id('[MyUser]') is not null
drop table [MyUser]
go
/****** 对象: Table [dbo].[CrowdMember] 脚本日期: 12/27/2010 16:54:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyUser](
[ID] [int] NOT NULL,
[OnLineState] [int] NOT NULL CONSTRAINT [DF_User_JoinState] DEFAULT ((0)), --在线状态
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]CREATE TABLE [dbo].[CrowdMember](
[ID] [int] NOT NULL,
[CrowdID] [int] NOT NULL,
[MemberUserID] [int] NOT NULL,
[JoinState] [int] NOT NULL,
CONSTRAINT [PK_CrowdMember] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]insert into MyUser
select 1, 1 union all
select 2, 1 union all
select 3, 1 union all
select 4, 1 union all
select 5, 1
insert into [CrowdMember]
select 1, 1, 1, 0 union all
select 2, 2, 1, 0 union all
select 3, 3, 1, 0 union all
select 4, 1, 2, 0 union all
select 5, 2, 2, 0 union all
select 6, 3, 2, 0 union all
select 7, 1, 3, 0 union all
select 8, 2, 3, 0 union all
select 9, 3, 3, 0 union all
select 10, 1, 4, 0 union all
select 11, 2, 4, 0 --查找CrowdMember中与我同属于一个CrowdID的在线用户ID
SELECT distinct MyUser.ID FROM MyUser, CrowdMember AS MT, CrowdMember AS MS
WHERE MyUser.OnLineState = 1 AND MyUser.ID = MT.MemberUserID
AND MT.CrowdID = MS.CrowdID AND MS.MemberUserID != MT.MemberUserID AND MS.MemberUserID = 2/*
ID
-----------
1
3
4(3 行受影响)
*/
drop table [CrowdMember]
go
if object_id('[MyUser]') is not null
drop table [MyUser]
go
/****** 对象: Table [dbo].[CrowdMember] 脚本日期: 12/27/2010 16:54:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyUser](
[ID] [int] NOT NULL,
[OnLineState] [int] NOT NULL CONSTRAINT [DF_User_JoinState] DEFAULT ((0)), --在线状态
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]CREATE TABLE [dbo].[CrowdMember](
[ID] [int] NOT NULL,
[CrowdID] [int] NOT NULL,
[MemberUserID] [int] NOT NULL,
[JoinState] [int] NOT NULL,
CONSTRAINT [PK_CrowdMember] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]insert into MyUser
select 1, 1 union all
select 2, 1 union all
select 3, 1 union all
select 4, 1 union all
select 5, 1
insert into [CrowdMember]
select 1, 1, 1, 0 union all
select 2, 2, 1, 0 union all
select 3, 3, 1, 0 union all
select 4, 1, 2, 0 union all
select 5, 2, 2, 0 union all
select 6, 3, 2, 0 union all
select 7, 1, 3, 0 union all
select 8, 2, 3, 0 union all
select 9, 3, 3, 0 union all
select 10, 1, 4, 0 union all
select 11, 2, 4, 0 --查找CrowdMember中与我同属于一个CrowdID的在线用户ID
SELECT distinct MyUser.ID FROM MyUser, CrowdMember AS MT, CrowdMember AS MS
WHERE MyUser.OnLineState = 1 AND MyUser.ID = MT.MemberUserID
AND MT.CrowdID = MS.CrowdID AND MS.MemberUserID != MT.MemberUserID AND MS.MemberUserID = 2/*
ID
-----------
1
3
4(3 行受影响)
*/
create ind_CrowdMember_CrowdID on CrowdMember(CrowdID)
go
create ind_CrowdMember_MemberUserID on CrowdMember(MemberUserID)
go
不过我还是有个疑问,在MemberUserID列上加索引,查询条件“AND MS.MemberUserID != MT.MemberUserID”为什么没有导致索引失效?