SQL查询可根据用户输入查找匹配值 [英] SQL Query to find matching values based on user input

问题描述

我正在为房地产经纪人和租户建立一个网站.租户可以注册并填写所需的物业位置，包括街道，城镇和邮政编码.一旦他们注册，它将自动通过电子邮件发送给那些具有与那些搜索条件相匹配的属性的代理.

I'm building a website for property agents and tenants. Tenants can sign up and fill in their desired locations for properties, including Street, Town and Postcode. Once they sign up, this automatically emails agents who have properties that match those search criteria.

目前，我对查询的设置如下，以便它与街道，城镇或邮政编码匹配.

At present I have the query set up as follows so that it matches on either the Street, Town or Postcode.

<%
Dim rspropertyresults
Dim rspropertyresults_numRows

Set rspropertyresults = Server.CreateObject("ADODB.Recordset")
rspropertyresults.ActiveConnection = MM_dbconn_STRING
rspropertyresults.Source = "SELECT * FROM VWTenantPropertiesResults "

'WHERE     (ContentStreet = 'Holderness Road') OR (ContentTown = 'Hull') OR (ContentPostCode = 'HU')

rspropertyresults.Source = rspropertyresults.Source& "WHERE (ContentStreet = '" & Replace(rspropertyresults__varReqStreet, "'", "''") & "'"

rspropertyresults.Source = rspropertyresults.Source& "OR ContentTown = '" & Replace(rspropertyresults__varReqTown, "'", "''") & "' "
rspropertyresults.Source = rspropertyresults.Source& "OR ContentTrimmedPostCode = '" & Replace(varPostcode, "'", "''") & "' ) "

rspropertyresults.Source = rspropertyresults.Source& "AND (( ContentBedRooms >= " & Replace(rspropertyresults__varBedroomsNoMin, "'", "''") & " "
rspropertyresults.Source = rspropertyresults.Source& "AND ContentBedRooms <= " & Replace(rspropertyresults__varBedroomsNoMax, "'", "''") & " ) "

rspropertyresults.Source = rspropertyresults.Source& "AND ( ContentPrice > = " & Replace(rspropertyresults__varPriceMin, "'", "''") & " "
rspropertyresults.Source = rspropertyresults.Source& "AND ContentPrice <= " & Replace(rspropertyresults__varPriceMax, "'", "''") & " )) " & varSQL & " "

rspropertyresults.Source = rspropertyresults.Source& "ORDER BY ContentPrice " & Replace(rspropertyresults__varSortWay, "'", "''") & " "

rspropertyresults.CursorType = 0
rspropertyresults.CursorLocation = 2
rspropertyresults.LockType = 1
rspropertyresults.Open()

rspropertyresults_numRows = 0
%>

但是，客户要求的是，不仅要匹配其中一个值，还需要以某种方式工作，即如果说街道"和城镇"匹配，则通过电子邮件将该房地产经纪人发送，或者如果城镇和邮政编码"匹配，然后通过电子邮件发送邮件.那个房地产经纪人.

However, the client has asked that instead of just matching on one of the values, it needs to work in such a way that if say Street and Town match, then email that property agent or if Town and Postcode match, then email that property agent.

您可以想象，我认为查询将变得非常复杂，但是我不确定如何最好地设计这样的查询.

As you can imagine, I think the query would become quite complex, but i'm unsure how to best design a query like this.

我想知道是否有人可以提供帮助或为我指明正确的方向?

I wondered if anyone might be able to help or point me in the right direction?

推荐答案

SELECT  *
FROM    (
        SELECT  id
        FROM    (
                SELECT  id
                FROM    VWTenantPropertiesResults
                WHERE   ContentStreet = 'Holderness Road'
                UNION ALL
                SELECT  id
                FROM    VWTenantPropertiesResults
                WHERE   ContentTown = 'Hull'
                UNION ALL
                SELECT  id
                FROM    VWTenantPropertiesResults
                WHERE   ContentPostCode = 'HU'
                ) qi
        GROUP BY
                id
        HAVING  COUNT(*) >= 2
        ) q
JOIN    VWTenantPropertiesResults r
ON      r.id = q.id
WHERE   ContentBedrooms BETWEEN 1 AND 4
        AND ContentPrice BETWEEN 50 AND 500
ORDER BY
        ContentPrice

这将返回至少符合条件的所有记录.

This will return you all records where at least 2 conditions match.

此解决方案是索引友好的:与OR子句不同，它将在ContentStreet，ContentTown和ContentPostCode上使用索引.

This solution is index friendly: unlike OR clauses, it will use indexes on ContentStreet, ContentTown and ContentPostCode.

有关性能的详细信息，请参阅我的博客中的该条目:

See this entry in my blog for performance detail:

• 匹配3之4

为了获得最佳性能和安全性，请使用绑定参数替换替换的参数值.

For best performance and security, replace substituted parameter values with bound parameters.

这将节省您解析查询的时间，并保护您免受SQL注入.

This will save you time on query parsing and will protect you against SQL injection.

这篇关于SQL查询可根据用户输入查找匹配值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！