使用多个变量筛选数据库

Question

简单地说，我正在尝试根据用户的多个选择筛选一个数据库。选择是使用复选框列表控件进行的。这张桌子是这样的：

表格

其目的是让用户单击一个或多个'Eis'，然后检查数据库(确切地说，是'Eisen‘表)，查看哪个'Systeem’拥有所有数据库。当选择多个 'Eis‘时，我遇到的问题就是。这是由于我不知道如何正确构造以下查询：

SELECT DISTINCT Systeem 
FROM Eisen 
WHERE Eis = {first value from the checkboxlist} AND Eis = {second value from the checkboxlist}

当然，这会查找适合Eis的两个值的单独行，Eis从来不适合任何内容。这样做的代码如下：

string conString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string query = "SELECT DISTINCT Systeem FROM Eisen";

        string condition = string.Empty;
        foreach (ListItem item in CheckBoxList1.Items)
        {
            condition += item.Selected ? string.Format("Eis = '{0}' AND ", item.Value) : "";
        }
        if (!string.IsNullOrEmpty(condition))
        {
            condition = string.Format(" where ({0})", condition.Substring(0, condition.Length - 5));
        }
        SqlCommand cmd = new SqlCommand(query + condition);
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;

                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds);
                    GridView1.DataSource = ds;
                    GridView1.DataBind();
                }
            }
        }

我的问题如下。如何构造查询，以便它将查找'Systeem‘列中的常见情况，并检查哪些查询在'Eis’列中具有所需的值？

Answer 1

给你：

SELECT Systeem, count(*)
FROM dbo.Eisen
WHERE Eis IN (1)
GROUP BY Systeem
HAVING count(*) = 1

SELECT Systeem, count(*)
FROM dbo.Eisen
WHERE Eis IN (1, 2)
GROUP BY Systeem
HAVING count(*) = 2

SELECT Systeem, count(*)
FROM dbo.Eisen
WHERE Eis IN (2, 3)
GROUP BY Systeem
HAVING count(*) = 2

注意，你在Google表格上的第三个结果是不正确的--除非我看错了。Systeem必须包含所有选定的项，还是只包含选定的项？

编辑:根据注释，下面是另一个场景：

SELECT Systeem, count(*)
FROM dbo.Eisen
WHERE Eis IN (2, 3)
GROUP BY Systeem
ORDER BY count(*) DESC

Answer 2

使用组来构造查询，而不是像下面这样的不同：

SELECT
    Systeem 
FROM
    Eisen 
WHERE
    Eis = {first value from the checkboxlist} 
    OR Eis = {second value from the checkboxlist}
GROUP BY
    Systeem
HAVING
    COUNT(*) = { number of checked checkboxes }