同时满足中间影射表的多种条件查询
分类类型表:
CREATE TABLE [dbo].[Web_CategoryType](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_Web_CategoryParent] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
分类表:
CREATE TABLE [dbo].[Web_Category](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[HeadID] [int] NULL,
[LV] [int] NULL,
[OrderID] [int] NULL,
[Family] [nvarchar](255) NULL,
[Title] [nvarchar](255) NULL,
[Keywords] [nvarchar](255) NULL,
[Description] [nvarchar](255) NULL,
[IsShow] [int] NULL,
[SrcDetail] [nvarchar](200) NULL,
[SrcList] [nvarchar](200) NULL,
[TypeID] [int] NULL,
[EngLishName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
中间影射表:
CREATE TABLE [dbo].[Con_CategoryTypeCategory](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CategoryTypeID] [int] NULL,
[CategoryID] [int] NULL,
CONSTRAINT [PK_Con_CategoryParentCategory] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
中间影射表数据例如:
ID | CategoryTypeID | CategoryID |
1 | 1 | 31 |
2 | 2 | 31 |
3 | 3 | 31 |
4 | 5 | 31 |
5 | 3 | 260 |
6 | 3 | 260 |
7 | 7 | 260 |
8 | 8 | 260 |
现在要查询同时满足分类类型为1和5的分类,可用以下SQL语句查询
select * from web_category where id in
(
select categoryid from Con_CategoryTypeCategory where categorytypeid = 5
and categoryid in
(
select categoryid from Con_CategoryTypeCategory where categorytypeid = 1
)
)