用于过滤SubSet数据的SQL

Question

我有一个数组列表，其中包含在我的数据库中找到的名称的子集。我需要写一个查询来获得某些部分的数组列表中的人数，例如，在我的数据库中有一个字段"City“，来自数组列表中的人，我想知道他们中有多少人住在芝加哥，有多少人住在纽约等。

有人能帮我设置一条sql语句来处理这个问题吗？我想我必须以某种方式将名称的子集传递给sql。

下面是我如何在代码中编写sql的示例

Public Shared Function GetCAData(ByVal employeeName As String) As DataTable
        Dim strQuery As String = "SELECT EMPLID, EMPLNME, DISP_TYPE, BEGIN_DTE FROM Corr WHERE (EMPLNME = @name)"
        Dim cmd As New SqlCommand(strQuery)
        cmd.Parameters.Add("@name", SqlDbType.VarChar)
        cmd.Parameters("@name").Value = employeeName
        Dim dt As DataTable = GenericDataAccess.GetData(cmd)
        Return dt
End Function

我需要一种方法来创建一个函数，使用您的sql语句返回一个名称的datatable对象，参数是城市和名称列表。

上面的例子不是我正在寻找的sql，它只是我想要创建的函数的骨架。

因此，您可以通过遍历所有城市来使用该函数，每次在fron末尾传递相同的名称集。

Answer 1

最好的方法取决于名称的子集有多大。

最简单，也可能是最糟糕的方法是创建一些动态sql，如下所示。

"select city,count(*) from table where user in ("name1","name2","name3") GROUP BY city"

其中in段是从arraylist生成的，可能如下所示

private string CreateSQL(List<string> names)
{
    var sb = new StringBuilder();
    sb.Append("select city,count(*) from table where user in ('");
    foreach ( var name in name)
    {
       sb.Append("'");
       sb.Append(name);
       sb.Append("',");
    }
    sb.Remove(sb.Length-1,1); //remove trailing ,
    sb.Append(") GROUP BY city");

    return sb.ToString();
}

我相信where in子句有100个条目的限制。如果您的子集是这样的长度，那么您确实需要一种更好的方法，比如将用户放到另一个表中并进行连接。事实上，如果用户的子集是由另一个查询从同一数据库中获取的，那么post它，我们将对单个查询进行正确的处理。

编辑:实际上我不知道如何将SQL型查询作为参数化命令，小心where user in注入！

Answer 2

您的Sql语句需要如下所示：

select city, count(*)
from table
where city in ('Chicago', 'New York')
group by city

其中城市列表是您的数组列表。您可以将其作为变量传递到存储过程中，也可以在代码中动态构建Sql字符串。

Answer 3

如果您使用的是SQL Server 2008，则可以使用表变量作为输入参数来创建存储的proc。然后，在proc中，您只需连接到输入参数表，即可获得您想要的内容。