假装不是奶茶精
2021-10-27 15:05
采纳率: 66.7%
浏览 80

c#操作access查询更改简单问题请教

img

img


如图access数据库表1表2,我想实现查询“Xid”在表2出现的次数以更改表1中对应的字段“出现次数”,现在的想法是将表1数据存在Datatable中(dt),遍历dt,查询字段Xid在表2出现的次数count,同时将count去update道表1中,代码实现写到了这里,请教一下接下来如何实现存count啊


  private void Button_Click(object sender, RoutedEventArgs e)
        {
            string path = "D:\\data";
            string strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + "\\test.mdb";

            OleDbConnection conn = new OleDbConnection(strConnect);
        
            //OleDbCommand cmd = new OleDbCommand("select * from 表2", conn);
            OleDbCommand acmd = new OleDbCommand("select * from 表1", conn);
            conn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter(acmd);
            OleDbCommandBuilder odb = new OleDbCommandBuilder(da);
            odb.QuotePrefix = "[";
            odb.QuotePrefix = "]";
            DataSet ads = new DataSet();
            da.Fill(ads,"表1");
            DataTable dt = ads.Tables["表1"];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string str = "select count(*) as count from 表2 where Xid =" + dt.Rows[i]["Xid"].ToString();
                OleDbCommand c = new OleDbCommand(str, conn);
            }
            conn.Close();
        }
  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • bekote 2021-10-27 15:32
    已采纳
    
    update1 set 出现次数 = (case when cnt is null then 0 else cnt end) from1 a left join (select Xid, count(1) as cnt from2 group by Xid) b on a.Xid = b.Xid
    

    不用那么麻烦,执行这1个sql语句就可以啦

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题