根据匹配条件进行搜索

一尘不染

根据匹配条件进行搜索

sql

我正在使用以下查询返回至少两个条件匹配的所有记录。

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

问题在于，它在搜索街道和城镇（返回具有请求的街道和城镇的所有匹配属性）时似乎有效，但是在搜索街道和邮政编码（不返回任何结果）时却不起作用。为了使Street和Postcode起作用（返回结果），我不得不删除以下几行；

        UNION ALL
        SELECT  id
        FROM    VWTenantPropertiesResults
        WHERE   ContentTown = 'Hull'

但是很明显，Town and Postcode或Town and
Street搜索无法正常工作，因为我删除了上述4行以使Street和Postcode搜索正常工作。

我想知道是否有人可以为此提供帮助？

谢谢你。

阅读 139

2021-05-16

共1个答案

一尘不染

我不确定您是否应该在数据库中强制执行“至少两个条件”条件，因为您可能永远都不知道已经填写了两个条件。相反，这可能对您有用-
这是我使用的一种模式经常并且应该应付条件的任意组合（我假设这在存储的proc中！）：

DECLARE PROCEDURE PropertyList
@StreetName NVARCHAR(50) = NULL,
@Town NVARCHAR(50) = NULL,
@Postcode NVARCHAR(10) = NULL
AS

SET NOCOUNT ON

SELECT 
    *
FROM 
    VWTenantPropertiesResults
WHERE
    ContentBedrooms BETWEEN 1 AND 4
AND
    ContentPrice BETWEEN 50 AND 500
AND
    (@ContentStreet IS NULL OR ContentStreet = @ContentStreet)
AND 
    (@ContentTown IS NULL OR ContentTown = @ContentTown)
AND
    (@ContentPostcode IS NULL OR ContentTown = @ContentTown)
ORDER BY
    ContentPrice

要从您的ASP页面调用此代码，您需要一些类似以下的代码（这可能需要一些调试，我的ASP的ADO和VBScript非常生锈！）：

Dim cnn 'As ADODB.Connection
Dim cmd 'As ADODB.Command
Dim prmStreet 'As ADODB.Parameter
Dim prmTown 'As ADODB.Parameter
Dim prmPostcode 'As ADODB.Parameter
Dim rstProperty 'As ADODB.RecordSet
Dim i 'As Integer

Set cnn = Server.CreateObject("ADODB.Connection")

cnn.ConnectionString = MyConnectionString

Set cmd = Server.CreateObject("ADODB.Command")

Set cmd.ActiveConnection = cnn

'Set the CommandText property to the name of the stored proc we want to call
cmd.CommandText = "PropertyList"
cmd.CommandType = 4 'or adCmdStoredProc if you're using ADOVBS.inc

If Request.Form("StreetTextBox") = "" Then
    'No street entered so don't pass it to the stored proc
Else
    'A street has been entered so create a parameter...
    Set prmStreet = cmd.CreateParameter("@StreetName", 203, 1, 50, Request.Form("StreetTextBox"))

    ' and add it to the Parameters collection of the Command object
    cmd.Parameters.Add(prmStreet)
End If

If Request.Form("TownTextBox") = "" Then
    'No town entered so don't pass it to the stored proc
Else
    'A town has been entered so create a parameter...
    Set prmTown = cmd.CreateParameter("@Town", 203, 1, 50, Request.Form("TownTextBox"))

    ' and add it to the Parameters collection of the Command object
    cmd.Parameters.Add(prmTown)
End If

If Request.Form("PostcodeTextBox") = "" Then
    'No postcode entered so don't pass it to the stored proc
Else
    'A postcode has been entered so create a parameter...
    Set prmPostcode = cmd.CreateParameter("@Postcode", 203, 1, 10, Request.Form("PostcodeTextBox"))

    ' and add it to the Parameters collection of the Command object
    cmd.Parameters.Add(prmPostcode)
End If

cnn.Open

'This is the line that'll actually call the stored procedure
Set rstProperty = cmd.Execute()

cnn.Close

If rstProperty.BOF And rstProperty.EOF Then
    'If BOF And EOF are true then this is an empty recordset - we got no records back
    Response.Write "No records returned"
Else
    'We have records so write them out into a table
    Response.Write "<table><tr>"
    For i = 0 To rstProperty.Fields.Count - 1
        Response.Write "<td>"
        Response.Write rstProperty.Fields(i).Name
        Response.Write "</td>"
        Response.Write "<td>&nbsp;</td>"
    Next

    Response.Write "</tr>"

    Do While rstProperty.Eof = False
        Response.Write "<tr>"
        For i = 0 To rstProperty.Fields.Count - 1
            Response.Write "<td>"
            Response.Write rstProperty.Fields(i).Value
            Response.Write "</td>"
        Next
        Response.Write "<td>"
        Response.Write "<a href='ViewDetails.asp?id='" & rstProperty.Fields("PropertyId").Value & "'>View Details for this property</a>"
        Response.Write "</td>"
        Response.Write "</tr>"
        rstProperty.MoveNext
    Loop

    Response.Write "</table>"
End If

这应该适用于任何参数组合，无论您是不输入参数，输入部分参数还是全部输入！

2021-05-16