我正在尝试查询,但它是不正确的。
我要做的是在case
中应用joinmy query is
SELECT LEFT(Student_First_Name,LEN(Student_First_Name)-LEN(Name_Lookup_Table.Dirty_Name)),Name_Lookup_Table.Dirty_Name, Name_Lookup_Table.Standard_Name
case
when Transformed_All_Student.Student_First_Name like '% '+Name_Lookup_Table.Dirty_Name
then
from Transformed_All_Student left join Name_Lookup_Table
on Transformed_All_Student.Student_First_Name like '% '+Name_Lookup_Table.Dirty_Name
when Transformed_All_Student.Student_First_Name like '% '+Name_Lookup_Table.Dirty_Name+'%'
then from Transformed_All_Student left join Name_Lookup_Table on Transformed_All_Student.Student_First_Name like '% '+Name_Lookup_Table.Dirty_Name+'%'
when Transformed_All_Student.Student_First_Name like Name_Lookup_Table.Dirty_Name+'% '
then from Transformed_All_Student left join Name_Lookup_Table on Transformed_All_Student.Student_First_Name like Name_Lookup_Table.Dirty_Name+'% '
有人能帮忙吗??
我不认为你可以把FROM放在万一。一个可能的解决方案是使用UNION。你可能可以优化这个:
SELECT LEFT(Student_First_Name
, LEN(Student_First_Name)-LEN(nlt.Dirty_Name))
, nlt.Dirty_Name
, nlt.Standard_Name
FROM Transformed_All_Student tas
LEFT JOIN Name_Lookup_Table nlt
ON tas.Student_First_Name like '% '+nlt.Dirty_Name
WHERE tas.Student_First_Name like '% '+nlt.Dirty_Name
UNION
SELECT LEFT(Student_First_Name
, LEN(Student_First_Name)-LEN(nlt.Dirty_Name))
, nlt.Dirty_Name
, nlt.Standard_Name
FROM Transformed_All_Student tas
LEFT JOIN Name_Lookup_Table nlt
ON tas.Student_First_Name like '% '+nlt.Dirty_Name+'%'
WHERE tas.Student_First_Name like '% '+nlt.Dirty_Name+'%'
UNION
SELECT LEFT(Student_First_Name
, LEN(Student_First_Name)-LEN(nlt.Dirty_Name))
, nlt.Dirty_Name
, nlt.Standard_Name
FROM Transformed_All_Student tas
LEFT JOIN Name_Lookup_Table nlt
ON tas.Student_First_Name like nlt.Dirty_Name+'% '
WHERE tas.Student_First_Name like nlt.Dirty_Name+'% '
既然您尝试这样做,我认为性能不是主要考虑的问题。此外,我假设这是MS SQL,因为没有另行指定。一般形式如下:
SELECT 1
FROM foo f
JOIN bar b
ON CASE WHEN f.col1 = 'X' then 'Y' ELSE END = b.col1
获得相同结果的另一种可能方法是使用子查询:
SELECT 1
FROM (
SELECT *, CASE WHEN f.col1 = 'X' then 'Y' ELSE END JoinCol
FROM foo
) f
JOIN bar b
ON f.JoinCol = b.col1
可能还有其他一些方法。如果您能给我们一个快速定义所涉及的表和您想要输出的列,那将是最好的。