连接两个选择查询会导致每个值一行？

I have a table called CharUser has the following data

我有一个名为CharUser的表有以下数据

UserID UserName UserPhone UserAddress

also a table called subscriptionjob

还有一个名为subscriptionjob的表

subID userId   companyid

another table called user UsersCvs has the following data

另一个名为user UsersCvs的表具有以下数据

CVId   Companyuserid   cvpath

I want to do a query that result as the following for each row

我想做一个查询,结果如下所示每行

userid   username   userphone  useraddress usercv

the userid is given from the subcriptionjob table i tried the following the first query

userid是从subcriptionjob表中给出的,我尝试了以下第一个查询

SELECT        CharUserId, CharUserName, CharUserPassword, CharUserMail, CharUserPhone, CharUserAddress, CharUserGender, ProgId, CharUserBirthdate
FROM            CharUser
WHERE        (CharUserId IN
                             (SELECT        CompanyUserId
                                FROM            SubsciptionJob
                                WHERE        (JobId = 1)))

and this the second query

这是第二个查询

SELECT        TOP (1) CvPath
FROM            UsersCvs
WHERE        (CompanyUserId IN
                             (SELECT        CompanyUserId
                                FROM            SubsciptionJob AS SubsciptionJob_1
                                WHERE        (JobId = 1)))
ORDER BY CvId DESC

Both work alone just fine i tried to combine them putting union in between but it doesn't work

单独工作就好了我试图将它们组合在一起,但是它不起作用

i want to attach the cvpath column to the first query with a unique value for each row

我想将cvpath列附加到第一个查询,每行具有唯一值

any ideas.. ?

有任何想法吗.. ?

thank you

3 个解决方案

#1

One way to do this:

一种方法:

SELECT CharUserId
     , CharUserName
     , CharUserPassword
     , CharUserMail
     , CharUserPhone
     , CharUserAddress
     , CharUserGender
     , ProgId
     , CharUserBirthdate
     , (SELECT TOP (1) CvPath
       FROM UsersCvs
       WHERE CompanyUserId IN (SELECT CompanyUserId
                               FROM SubsciptionJob
                               WHERE JobId = 1)
       ORDER BY CvId DESC) AS CvPath    
FROM CharUser
WHERE CharUserId IN (SELECT CompanyUserId
                     FROM  SubsciptionJob
                     WHERE JobId = 1)

#2

This is more efficient than answer above:

这比上面的回答更有效:

SELECT CharUserId, 
       CharUserName, 
       CharUserPassword, 
       CharUserMail, 
       CharUserPhone, 
       CharUserAddress, 
       CharUserGender, 
       ProgId, 
       CharUserBirthdate,
       (SELECT TOP (1) CvPath FROM UsersCvs c where c.CompanyUserID=X.CharUserID
       FROM CharUser X
       WHERE (CharUserId IN 
                           (SELECT CompanyUserId FROM SubsciptionJob 
                            WHERE (JobId = 1)))

It may be even more efficient by using an inner join between the subscription table and The CharUser table.

通过在订阅表和CharUser表之间使用内部联接,可能更有效。

#3

Use ROW_NUMBER()

SELECT CharUserId, CharUserName, CharUserPassword, CharUserMail, 
    CharUserPhone, CharUserAddress, 
    CharUserGender, ProgId, CharUserBirthdate,cvpath
FROM CharUser CU
JOIN subscriptionjob SJ ON SJ.UserId = CU.UserID
INNER JOIN 
(   SELECT cvpath,Companyuserid,
    ROW_NUMBER() OVER (PARTITION BY A.Companyuserid ORDER BY A.CVID DESC) X
    FROM UsersCvs A
)R ON R.X = 1 AND CU.UserID = R.Companyuserid
WHERE JobId = 1

#1