查询字段等于字段中的值等于字段Field2的位置 [英] Query Where Column is Like Value From Field Equals Value Field2

问题描述

我目前正在从事一个项目，该项目需要我们查询各种代码并按照行业标准提取/提交数据.

我创建了一个交叉引用表，该表存储了将在操作系统中找到的源值映射到要提交的目标值.

在某些情况下，我们必须在源字段上执行 LIKE 语句，因为我们并不总是知道确切的值，并且可能会有一些具有相同含义的组合需要包含在其中.例如，我们想要所有带有科学"一词的记录.我想包括科学1"，科学2"，科学暑期学校"等，它们都将被映射(见下文)到常规"的 TargetValue .

表映射–交叉引用表

MapName	SourceValue	TargetValue
Class_Taken	Science	General
Class_Taken	1345	General
Class_Taken	数学	常规
Class_Taken	阅读	常规
Class_Taken	23041	General

Table StudentCourses – Source Table

ID	CourseDate	课程名称
111111	2010年10月1日	代数
311112	2011年10月10日	科学101
131211	01/10/2009	英语
112111	2010年4月1日	数学基础
121311	08/15/2010	科学
211131	01/10/2010	阅读

这是我希望做的事情，以保持查询的通用性，并且只有一个查询，以避免必须连接到相同的表15次，因为我们必须执行许多不同类型的映射.我想使用 Like 比较来获得结果，其中 StudentCourses.CourseName 就像 Mapping.SourceValue 值(想要避免对实际的 LIKE 条件进行硬编码，因为那样的话我们就必须为每个规则编写查询)，如果我找到匹配项将 CourseName 值设置为相关的 TargetValue ，这样我就知道将其包括在该组中.

I am currently working on a project that requires us to query on various codes and extract/submit the data following industry standards.

I created a cross reference table that stores the source values that will be found in the operational system mapped to the target value for submission.

In some cases we have to perform a LIKE statement on the source field because we will not always know the exact value and there may be several combinations with the same meaning that we want to include. For example, we want all the records with the word "science". I want to include "science 1", "science 2", "science summer school", etc. and they will all be mapped (see below) to a TargetValue of "General".

Table Mapping – Cross Reference Table

MapName	SourceValue	TargetValue
Class_Taken	Science	General
Class_Taken	1345	General
Class_Taken	Math	General
Class_Taken	Reading	General
Class_Taken	23041	General

Table StudentCourses – Source Table

ID	CourseDate	CourseName
111111	01/10/2010	Algebra
311112	01/10/2011	Science 101
131211	01/10/2009	English
112111	04/01/2010	Math Basics
121311	08/15/2010	Science
211131	01/10/2010	Reading

This is what I am hoping to do to keep my query generic and only have one query to avoid having to join to the same tables 15 times since there are many different types of mappings we have to perform. I want to get the results using a LIKE comparison where the StudentCourses.CourseName is like the Mapping.SourceValue value (want to avoid hard coding the actual LIKE condition because then we would have to write a query for every single rule) then if I get a match set the CourseName value to the correlating TargetValue so I know to include it in that group.

SELECT
    *
FROM StudentCourses 
INNER JOIN Mapping Map 
    ON
        CASE
            WHEN '%StudentCourses.CourseName%' LIKE 'Map.Source_Value' THEN 'Map.Target_Value'
            ELSE null
        END = Map.Target_Value

这就是我被困住的地方；我不知道如何在 CourseName 字段上执行 LIKE 语句.

而且我想知道其余的方法是否可以工作和/或以更好的方式提出任何建议.

This is where I am getting stuck; I don''t know how to perform a LIKE statement on the CourseName field.

And I''m wondering if the rest would work and/or any recommendations of a better way.

推荐答案

您的问题对我来说不是很清楚(会帮助，如果您显示了您想要的输出)，但这看起来就像您想要的一样:

Your question isn''t very clear to me (would help if you showed the output you were after), but this seems like what you are after:

-- Sample data.
DECLARE @Mapping table (MapName varchar(100), SourceValue varchar(100), TargetValue varchar(100))
DECLARE @StudentCourses table (ID int, CourseDate datetime, CourseName varchar(100))
INSERT INTO @Mapping VALUES('Class_Taken', 'Science', 'General')
INSERT INTO @Mapping VALUES('Class_Taken', '1345', 'General')
INSERT INTO @Mapping VALUES('Class_Taken', 'Math', 'General')
INSERT INTO @Mapping VALUES('Class_Taken', 'Reading', 'General')
INSERT INTO @Mapping VALUES('Class_Taken', '23041', 'General')
INSERT INTO @StudentCourses VALUES (111111, GetDate(), 'Algebra')
INSERT INTO @StudentCourses VALUES (311112, GetDate(), 'Science 101')
INSERT INTO @StudentCourses VALUES (131211, GetDate(), 'English')
INSERT INTO @StudentCourses VALUES (112111, GetDate(), 'Math Basics')
INSERT INTO @StudentCourses VALUES (121311, GetDate(), 'Science')
INSERT INTO @StudentCourses VALUES (211131, GetDate(), 'Reading')

-- Query.
SELECT
	ID,
	CourseDate,
	CASE
		WHEN TargetValue IS NULL THEN CourseName
		ELSE TargetValue
	END AS NewCourseName
FROM @StudentCourses AS Courses
LEFT JOIN @Mapping AS Map
	ON Courses.CourseName LIKE '%' + Map.SourceValue + '%'

/* Output:

111111	2011-06-16 14:50:21.233	Algebra
311112	2011-06-16 14:50:21.233	General
131211	2011-06-16 14:50:21.233	English
112111	2011-06-16 14:50:21.233	General
121311	2011-06-16 14:50:21.233	General
211131	2011-06-16 14:50:21.233	General

*/

这篇关于查询字段等于字段中的值等于字段Field2的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！