筛选具有多个变量的数据库

本文关键字：变量 数据库 筛选 | 更新日期: 2023-09-27 18:08:25

简单地说，我正在尝试根据用户的多个选择过滤数据库。使用复选框控件进行选择。表看起来像这样:

表

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

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

这当然寻找适合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();
                }
            }
        }
我的问题如下。如何构造查询，以便它在"system"列中查找常见情况，并检查哪些查询在"Eis"列中具有所需值?

筛选具有多个变量的数据库

给你:

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

注意你的第三个结果在你的谷歌表是不正确的-除非我读错了。系统必须包含所有选中的项目，还是只包含选中的项目?

编辑:根据评论，这里是另一个场景:

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

用GROUP BY而不是DISTINCT构造查询，如下所示:

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 }
相关文章：