sql - 在 where 子句中使用逗号分隔值并与 in 子句进行比较

Question

这是经过编辑的问题，包含完整的问题。以下是表结构。 (下面仅显示必要的列。)

表名称:tblQualificationMaster。

Qualiid  QualiName
-------  ---------
1        S.S.C  
2        H.S.C  
3        B.Sc  
4        M.C.A  
5        M.Sc(IT)  
6        B.E  
7        M.B.A  
8        B.Com  
9        M.E  
10       C.S  
12       M.Com

表名称:tblAppResumeMaster。

AppId  FirstName  LastName  TotalExpYears  TotalExpMonths  
-----  ---------  --------  -------------  --------------
1      Rahul      Patel     7              0  
2      Ritesh     Shah      0              0  
3      Ajay       shah      7              6  
4      Ram        Prasad    7              6  
5      Mohan      Varma     5              0  
6      Gaurav     Kumar     8              0  

表名称:tblAppQualificationDetail。 (为了更好地阅读，我为除第一行之外的所有行编写逗号分隔值，但在我的数据库中，所有值都像 appid=1 一样存储。即每个 qualificationid 一行.)

Appid  QualiId  
-----  -------
1      1  
1      2  
1      3  
1      4  
2      1,2,3  
3      1,2,6  
4      1,2,3,5  
5      1,2,3,4  
6      1,2,6,9  

表名称:tblVacancyMaster

VacId  Title           Criteria  Req.Exp  KeySkills
-----  --------------  --------  -------  ---------------
1      Programmer      4,5,6     4        .net,java,php
2      TL              4,5       3        .net,java,php
3      Project Mngr.   4,6,9     4        .net,java,php,sql
4      Java Developer  4,5,6     0        java,oracle,sql
5      Manager         7,9       7        bussiness management
6      Supervisior     3,8       3        marketing
7      PHP Developer   4,5       0        php,mysql,send

现在，基于此详细信息，我想创建应具有以下字段的 View 。 (它是针对 VacId=1 显示的，但我需要为所有空缺职位提供此信息，以便我可以在此 View 上触发 where 子句，例如 select * from view where VacId=3。)

AppId  FirstName  LastName  QualiName  QualiId  TotalExp  VacId  VacTitle
-----  ---------  --------  ---------  -------  --------  -----  ----------
1      Rahul      Patel     M.C.A      4        7         1      Programmer
3      Ajay       Shah      B.E.       6        7         1      Programmer
5      Mohan      Verma     M.C.A      4        5         1      Programmer
6      Gaurav     Kumar     B.E        6        8         1      Programmer
6      Gaurav     Kumar     M.E        9        8         1      Programmer

此 View 显示 AppId 1、3、5 和 6 符合空缺 3 的条件，但显示应用 6 的重复条目。如何获取唯一记录？

我在数据库设计方面可能是错误的，因为这是我的第一个项目，而且我正在学习数据库，所以如果有违反数据库标准的地方，请告诉我并纠正。

我之前的查询(注意:早些时候，我使用了一个中间表 tblVacancyCriteriaDetail ，其中包含列 VacId 和 QualiId 以及我的表 tblVacancyMaster没有列标准)

select
  ARM.AppId,
  ARM.AppFirstName,
  ARM.AppLastName,
  ARM.AppMobileNo,
  AQD.QualiId,
  VacQualiDetail.QualiName,
  ARM.AppEmailId1,
  VacQualiDetail.VacID,
  ARM.TotalExpYear,
  VacQualiDetail.VacTitle,
  VacQualiDetail.DeptId,
  VacQualiDetail.CompId,
  CM.CompName  
from
  tblAppResumeMaster ARM,
  tblAppQualificationDetail AQD,
  tblCompanyMaster CM,
  (
    select
      VM.VacID,
      VM.VacTitle,
      VM.CompId,
      VM.DeptId,
      vcd.QualificationID,
      QM.QualiName,
      VM.RequiredExperience as Expe
    from
      tblVacancyCriteriaDetail VCD,
      tblVacancyMaster VM,
      tblQualificationMaster QM
    where VCD.VacID=VM.VacID
      and VCD.QualificationID=QM.QualificationId
      and VM.Status=0
  ) as VacQualiDetail
where AQD.AppId=arm.AppId
  and aqd.QualiId=VacQualiDetail.QualificationID
  and ARM.TotalExpYear>=Expe
  and cm.CompId=VacQualiDetail.CompId

Answer 1

create view vAppList as
select AppId, 
       FirstName, 
       LastName, 
       QualiName, 
       Qualiid, 
       TotalExpYears, 
       VacId, 
       Title
from (select ARM.AppId,
             ARM.FirstName,
             ARM.LastName,
             QM.QualiName,
             QM.Qualiid,
             ARM.TotalExpYears,
             VM.VacId,
             VM.Title,
             row_number() over(partition by ARM.AppId, VM.VacId order by QM.Qualiid) as rn
      from tblAppResumeMaster as ARM
        inner join tblAppQualificationDetail as AQD
          on ARM.AppId = AQD.Appid
        inner join tblQualificationMaster as QM
          on AQD.QualiId = QM.Qualiid  
        inner join tblVacancyMaster as VM
          on  ','+VM.Criteria+',' like '%,'+cast(QM.Qualiid as varchar(10))+',%'    
     ) as V
where V.rn = 1

当一名申请人匹配多于一项资格时，子查询将出现重复。在这种情况下，QualiName 将具有最低 Qualiid 的值。

如果您返回使用 tblVacancyCriteriaDetail(我认为您应该这样做)， View 将如下所示。

create view vAppList as
select AppId, 
       FirstName, 
       LastName, 
       QualiName, 
       Qualiid, 
       TotalExpYears, 
       VacId, 
       Title
from (select ARM.AppId,
             ARM.FirstName,
             ARM.LastName,
             QM.QualiName,
             QM.Qualiid,
             ARM.TotalExpYears,
             VM.VacId,
             VM.Title,
             row_number() over(partition by ARM.AppId, VM.VacId order by QM.Qualiid) as rn
      from tblAppResumeMaster as ARM
        inner join tblAppQualificationDetail as AQD
          on ARM.AppId = AQD.Appid
        inner join tblQualificationMaster as QM
          on AQD.QualiId = QM.Qualiid
        inner join tblVacancyCriteriaDetail as VCD    
          on QM.Qualiid = VCD.QualiID
        inner join tblVacancyMaster as VM
          on  VCD.VacId = VM.VacId
     ) as V
where V.rn = 1