在SQL列上使用几种过滤条件进行查询 [英] Query with several filter conditions on column in SQL

问题描述

我关注的数据包含培训，过滤器和用户记录.以下脚本将创建愿望表，需要根据某些规则应用过滤条件.

I’ve following data which contain Training, Filter and User records. Following script will create desire tables, need to apply filter conditions based on certain rules.

CREATE TABLE #Training (Id INT, Topic VARCHAR(50), [Status] VARCHAR(50));
INSERT INTO #Training SELECT 1, 'International Sales Training', 'Active';
INSERT INTO #Training SELECT 2, 'AMAR Sales Training', 'Active';
INSERT INTO #Training SELECT 3, 'APAC Sales Training', 'Active';
INSERT INTO #Training SELECT 4, 'General Training', 'Active';
INSERT INTO #Training SELECT 5, 'Manager Training', 'Active';

 CREATE TABLE #Filter (Id INT, Condition VARCHAR(50), Parameter VARCHAR(50), TrainingId int);
 INSERT INTO #Filter SELECT 1, 'Department', 'Sales', 1;
 INSERT INTO #Filter SELECT 2, 'Division', 'International Sales', 2;
 INSERT INTO #Filter SELECT 3, 'Place', 'AMAR', 2;
 INSERT INTO #Filter SELECT 4, 'Designation', 'Manager', 1;
 INSERT INTO #Filter SELECT 5, 'Designation', 'Developer', 4;

 CREATE TABLE #User (Id INT, Name VARCHAR(50), Place VARCHAR(50), Country 
 VARCHAR(50), Department VARCHAR(50), Division VARCHAR(50), SubDivision 
 VARCHAR(50), [Location] VARCHAR(50), Designation VARCHAR(50));
 INSERT INTO #User SELECT 1, 'John', 'EMEA', 'UK', 'Sales', 'International 
 Sales', '---', 'London', 'Manager';
 INSERT INTO #User SELECT 2, 'Jim', 'AMAR', 'USA', 'Sales', 'International Sales', '---', 'New York', 'Manager';
 INSERT INTO #User SELECT 3, 'Sally', 'AMAR', 'USA', 'Sales', 'International Sales', '---', 'Chicago', 'Sr. Manager';
 INSERT INTO #User SELECT 4, 'Molly', 'AMAR', 'USA', 'Sales', 'International Sales', '---', 'New York', 'Manager';
 INSERT INTO #User SELECT 5, 'Gorge', 'AMAR', 'USA', 'Sales', 'International Sales', 'FR relations', 'New York', 'Developer';
 INSERT INTO #User SELECT 6, 'Kramer', 'APAC', 'Singapore', 'Sales', 'International Sales', '---', 'Singapore', 'Manager';

我正在努力获取符合条件的过滤器培训记录，即以下示例(针对UserId 2)应在仅与部门"和指定"相匹配的地方提供培训"详细信息. TrainingID 1.但由于OR条件，我得到3条记录.

What I’m struggling to get filter training records for matching criteria, i.e. following example (for UserId 2) it should give Training details where it matches Department and Designation only .i.e. TrainingID 1. But I’m getting 3 record due to OR condition.

 Declare @Department VARCHAR(50),@Division VARCHAR(50), @Designation VARCHAR(50)

 SELECT @Department = Department, @Division = Division,@Designation=Designation from #User where id=2

 SELECT DISTINCT TR.ID
       FROM #Training TR
       LEFT OUTER JOIN #Filter F ON TR.ID = F.Id AND TR.Status = 'Active'
    WHERE 
    (
                (
                    (F.Condition='Department' and F.Parameter = @Department )                       
                )
                OR
                (
                     (F.Condition='Division' and F.Parameter = @Division)   
                )
                OR
                (
                     (F.Condition='Designation' and F.Parameter = @Designation) 
                )
    )

推荐答案

如果要同时匹配所有三个，请使用group by和having:

If you want to match all three, then use group by and having:

SELECT TR.ID
FROM #Training TR JOIN
     #Filter F
     ON TR.ID = F.Id AND TR.Status = 'Active'
WHERE (F.Condition = 'Department' and F.Parameter = @Department) OR 
      (F.Condition = 'Division' and F.Parameter = @Division) OR
      (F.Condition = 'Designation' and F.Parameter = @Designation) 
GROUP BY TR.ID
HAVING COUNT(DISTINCT F.Condition) = 3;

如果您不希望所有三个都匹配，则可以更改HAVING子句.

You can change the HAVING clause if you don't want all three to match.

这篇关于在SQL列上使用几种过滤条件进行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！