如何在两个表上写,以便-7列形成一个表,列的值分布在N行MAtch中 [英] How to write on two tables so that --7 colums form one table and Value of a Column distributed in N Rows MAtch
问题描述
如何在两个表上写,以便--7列表形成一个表,单个列的值以N行分配并重新调整匹配数据
我的第一张桌子有 - 列
关键字1
关键字2
关键字3
关键字4
关键字5
关键字6
关键字7
和我的第二个表包含两个coulmns User_Id和Choice
,因为他可以选择尽可能多的项目,所以他尊重的选择'的Id存储在第二个表中,如 -
User_Id - 选择
2 --- 121
2 --- 122
2 --- 123
2 --- 124
2 --- 125
我可以通过
表1(Keywods 1 to Keywords7)和Table2来制作数据(选择)
创建Table1的脚本是 -
CREATE TABLE [dbo]。[JobForm](
[Job_Id] [ int ] NOT < span class =code-keyword> NULL ,
[Recruiter_id] [ int ] NULL ,
[JobTitle] [ varchar ]( 100 ) NULL ,
[位置] [ varchar ]( 50 ) NULL ,
[类别] [ varchar ]( 50 ) NULL ,
[MinExp] [ varchar ] ( 20 ) NULL ,
[MaxExp] [ varchar ]( 20 ) NULL ,
[Body] [ varchar ]( 2000 ) NULL ,
[Keyword1] [ varchar ]( 50 ) NULL ,
[Keyword2] [ varchar ]( 50 ) NULL ,
[Keyword3] [ varchar ]( 50 ) NULL ,
[Keyword4] [ varchar ]( 50 ) NULL ,
[Keyword5] [ varchar ]( 50 ) NULL ,
[Keyword6] [ varchar ]( 50 ) NULL ,
[Keyword7] [ VARCHAR 跨度>] ( 50 ) NULL ,
[Investor1] [ varchar ]( 50 ) NULL ,
[Investor2] [ varchar ]( 50 ) NULL ,
[Investor3] [ varchar ]( 50 ) NULL ,
[Investor4] [ varchar ]( 50 ) NULL ,
[Investor5] [ varchar ]( 50 ) NULL ,
[CreatedDate] [ datetime ] NULL
) ON [ PRIMARY ]
>
并创建第二脚本脚本是---
创建 TABLE [dbo]。[UserFeedSelection](
[User_Id] [ int ] NOT NULL ,
[JobFeed] [ int ] NOT NULL ,
PRIMARY KEY CLUSTERED
(
[User_Id] ASC ,
[JobFeed] ASC
) WITH (PAD_INDEX = OFF ,STATISTICS_NORECOMPUTE = OFF ,IGNORE_DUP_KEY = OFF ,ALLOW_ROW_LOCKS = ON ,ALLO W_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
i必须匹配
Keyword1
Keyword2
Keyword3
Keyword4
Keyword5
Keyword6
Keyword7
with
下面的选择列为特定的USer_Id有多个选择
User_Id - 选择
2 --- 121
2 --- 122
2 --- 123
2 --- 124
2 --- 125
请帮助..谢谢提前
尝试以下查询。
<预lang =SQL> SELECT DISTINCT A.USER_ID,A.CHoice FROM
UserFeedSelection A
INNER JOIN JobForm B ON
A.choice = B.Keyword1 OR A.Choice = B.Keyword2 OR
A.choice = B.Keyword3 OR A.Choice = B.Keyword4 OR
A.choice = B.Keyword5 OR A.Choice = B.Keyword6 OR
A.choice = B.Keyword7
WHERE A.User_ID = 2
希望有帮助
How to write on two tables so that --7 colums form one table and Value of a single Column distributed in N Rows MAtch and retun Matched Data
My First table has -- columns
Keyword1
Keyword2
Keyword3
Keyword4
Keyword5
Keyword6
Keyword7
and my second table contains two coulmns User_Id and Choice
as he can select as much items so his respected choice''s Id is stored in second table like--
User_Id--Choice
2 ---121
2 ---122
2 ---123
2 ---124
2 ---125
so can i MAtch data through
Table1 (Keywods 1 to Keywords7 ) and Table2(Choice)
create Script of Table1 is--
CREATE TABLE [dbo].[JobForm](
[Job_Id] [int] NOT NULL,
[Recruiter_id] [int] NULL,
[JobTitle] [varchar](100) NULL,
[Location] [varchar](50) NULL,
[Category] [varchar](50) NULL,
[MinExp] [varchar](20) NULL,
[MaxExp] [varchar](20) NULL,
[Body] [varchar](2000) NULL,
[Keyword1] [varchar](50) NULL,
[Keyword2] [varchar](50) NULL,
[Keyword3] [varchar](50) NULL,
[Keyword4] [varchar](50) NULL,
[Keyword5] [varchar](50) NULL,
[Keyword6] [varchar](50) NULL,
[Keyword7] [varchar](50) NULL,
[Investor1] [varchar](50) NULL,
[Investor2] [varchar](50) NULL,
[Investor3] [varchar](50) NULL,
[Investor4] [varchar](50) NULL,
[Investor5] [varchar](50) NULL,
[CreatedDate] [datetime] NULL
) ON [PRIMARY]
and create Script of Second Script is---
CREATE TABLE [dbo].[UserFeedSelection](
[User_Id] [int] NOT NULL,
[JobFeed] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[User_Id] ASC,
[JobFeed] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
i have to match
Keyword1
Keyword2
Keyword3
Keyword4
Keyword5
Keyword6
Keyword7
with
Choice column below as having N number of choices for a particular USer_Id
User_Id--Choice
2 ---121
2 ---122
2 ---123
2 ---124
2 ---125
Pls Help ..Thanks in Advance
Hi,
try below query.
SELECT DISTINCT A.USER_ID, A.CHoice FROM UserFeedSelection A INNER JOIN JobForm B ON A.choice = B.Keyword1 OR A.Choice = B.Keyword2 OR A.choice = B.Keyword3 OR A.Choice = B.Keyword4 OR A.choice = B.Keyword5 OR A.Choice = B.Keyword6 OR A.choice = B.Keyword7 WHERE A.User_ID = 2
hope it helps
这篇关于如何在两个表上写,以便-7列形成一个表,列的值分布在N行MAtch中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!