联接查询结果在网格视图中生成不同但重复的数据

本文关键字：数据 结果 查询 网格 视图 | 更新日期: 2023-09-27 18:30:25

我想从一个表中检索员工的姓名，从另一个表中检索他们的休假申请的详细信息。将员工与其上属老板区分开来的条件是manager_ID列。employee_ID作为每个用户的manager_ID列的值提供。导演在此字段中为空。现在，我已使用以下查询在网格视图中填充了此数据。

SqlConnection conn = new SqlConnection(connectionstring);
SqlCommand cmd = new SqlCommand("select distinct Employee.emp_name,Leave.leave_ID,Leave.leave_type,Leave.reason,Leave.el_start_date,Leave.el_end_date,Leave.total_days,Leave.status from Employee CROSS JOIN Leave where Employee.manager_ID='" + lbl_empid.Text + "' AND Leave.status='" + lbl_status.Text + "'", conn);
cmd.CommandType = CommandType.Text;
using (conn)
{
    conn.Open();
    rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
        dr = dt.NewRow();
        dr["Leave No."] = rdr["leave_ID"].ToString();
        dr["Requested By"] = rdr["emp_name"].ToString();
        dr["Type of Leave"] = rdr["leave_type"].ToString();
        dr["Reason"] = rdr["reason"].ToString();
        dr["Starting"] = rdr["el_start_date"].ToString();
        dr["Ending"] = rdr["el_end_date"].ToString();
        dr["No. of Days"] = rdr["total_days"].ToString();
        dr["Status"] = rdr["status"].ToString();
        dt.Rows.Add(dr);
        dt.AcceptChanges();
    }
    gv_pending_requests.DataSource = dt;
    gv_pending_requests.DataBind();
    conn.Close();
}

调试时，网格视图将填充而不会出错。它显示了由登录经理管理的员工，好吧。仅检索和显示所需的员工行。但是数据以一种奇怪的方式复制。同一行在输出中重复，并替换名称。我对网格视图中的输出进行了粗略的模仿，因为我不允许发布图像。输出通过重复显示具有相同管理器的所有用户的数据。虽然实际上我只是从 Avneesh 的帐户而不是 ravi 的帐户中提出这些请求：

Lv. No.  Employee Name     LeaveType                  Reason           Starting Date
2        Avneesh           Earned, Casual            Personal          17/11/2014   
2        Ravi              Earned, Casual            Personal          17/11/2014 
5        Avneesh           Earned, Half-Pay          Conference        17/11/2014   
5        Ravi              Earned, Half-Pay          Conference        17/11/2014

请指出我在查询中做错了什么。我尝试在子查询中使用自连接，但它显示错误：- {"System.Data.SqlClient.SqlException （0x80131904）：关键字"where"} 附近的语法不正确。查询为：

SqlCommand cmd = new SqlCommand("select distinct Employee.emp_name,Leave.leave_ID,Leave.leave_type,Leave.reason,Leave.el_start_date,Leave.el_end_date,Leave.total_days,Leave.status from Employee CROSS JOIN Leave where Employee.manager_ID=(select e.emp_ID,m.emp_ID from Employee e INNER JOIN Employee m where e.mgr_ID=m.emp_ID) AND Leave.status='" + lbl_status.Text + "'", conn);

现在，我该如何解决这种重复？请帮忙。我正在使用Visual Studio 2013和sql server 2014。编辑：以下是与此查询相关的两个表。

   CREATE TABLE [dbo].[Employee](
[emp_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[username] [nvarchar](50) NOT NULL,
[emp_name] [nvarchar](max) NOT NULL,
[password] [nvarchar](max) NOT NULL,
[pay_roll_num] [nvarchar](max) NOT NULL,
[designation] [nvarchar](max) NOT NULL,
[leaves_taken] [nvarchar](max) NOT NULL,
[primary_address] [nvarchar](max) NOT NULL,
[primary_phone] [nvarchar](max) NOT NULL,
[email_ID] [nvarchar](max) NOT NULL,
[manager_ID] [nvarchar](max) NOT NULL,
     CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED (   [username] ASC)WITH (PAD_INDEX = OFF,  
     STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    CREATE TABLE [dbo].[Leave](
[leave_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[username] [nvarchar](50) NOT NULL,
[reason] [nvarchar](max) NOT NULL,
[addrs_onLeave] [nvarchar](max) NOT NULL,
[phone_onLeave] [nvarchar](max) NOT NULL,
[alternate_email] [nvarchar](max) NULL,
[leave_type] [nvarchar](max) NOT NULL,
[earned_leave] [nvarchar](max) NULL,
[el_start_date] [nvarchar](max) NULL,
[el_end_date] [nvarchar](max) NULL,
[halfpay_leave] [nvarchar](max) NULL,
[hl_start_date] [nvarchar](max) NULL,
[hl_end_date] [nvarchar](max) NULL,
[commuted_leave] [nvarchar](max) NULL,
[com_startdate] [nvarchar](max) NULL,
[com_end_date] [nvarchar](max) NULL,
[casual_leave] [nvarchar](max) NULL,
[cl_start_date] [nvarchar](max) NULL,
[cl_end_date] [nvarchar](max) NULL,
[rh_leave] [nvarchar](max) NULL,
[rh_start_date] [nvarchar](max) NULL,
[rh_end_date] [nvarchar](max) NULL,
[total_days] [nvarchar](max) NOT NULL,
[status] [nvarchar](max) NOT NULL,
[leave_apply_date] [nvarchar](max) NOT NULL,
    CONSTRAINT [PK_Leave] PRIMARY KEY CLUSTERED (   [leave_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

联接查询结果在网格视图中生成不同但重复的数据

我同意交叉连接是问题所在。

当表或查询集具有多行时，很少需要对表或查询集进行 CROSS JOIN 操作，从而创建笛卡尔乘积。另一方面，我经常交叉联接到包含单行的表或查询，但这样做不会增加结果集中的行数，因此不会重复行。

概念化何时对多行表或多行

表或多行集使用 CROSS JOIN 的一个很好的例子是需要过帐借方和贷方的批处理（复式记账）。您可以联接到包含两行的查询，一行包含"DEBIT"，另一行包含"CREDIT"，这样做只是使批处理中的所有行加倍。 一半作为借方过账，另一半作为贷方。

解决了！谢谢大家建议摆脱交叉连接。这是正确的查询，以防其他人可能需要它。

 SqlCommand cmd = new SqlCommand("select distinct leave_ID,username,leave_type,reason,
 el_start_date,el_end_date,total_days,status from Leave where username IN 
 (select username from Employee where manager_ID='" + lbl_empid.Text + "')
 AND Leave.status='" + lbl_deny.Text + "'", conn);
相关文章：