|
|
我在SQL有两个表已经填满了C＃中的DataTables中。它们的SQL不同的服务器和不同的表位DB上。
. R" G( M& H6 D9 L在第一个表中，我有五列+ y" {$ t" c4 S7 d! _2 [
Name(string),AgentStatus(string),TimeInState(double),TaskHandled(double),Region(string)第二张表我有3列
) ^& C; [" U) R( hName(string),CChats(double),AChats(double)我一直在使用这个链接合并C＃中的表
2 f; K* M, }2 e7 L! R$ zC＃中的DataTables的内部联接! X8 G* j% ~: ]' q& w, H
但这是我遇到的问题。6 Z. h0 ]4 \0 J0 `5 T
表1有59行，每个用户1行。
6 Z7 x8 n6 ?, y$ z表2有25行，每个用户都有2行聊天账户。
6 t: M6 \ j" k0 C$ r8 @当我在C＃当它们合并时，我只能从表1中得到与表2中25行相匹配的项目。我需要显示表1中的所有行。如果它们在表2中有一行，则显示数据，否则显示0。( L! t8 c9 D5 \$ }& c& ?
我知道问题出在哪里，在上面的链接中select但我不确定如何在句子中C＃中修复它3 D$ M: d' C" k3 B
这是我的代码不起作用......列表框只是为了查看调试输出.....
0 ^' v! {: T, T) ~6 R- y DataTable dt1 = new DataTable()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；DataTable dt2 = new DataTable()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；dt1.Columns.Add("Name",typeof(string)); dt1.Columns.Add("Status",typeof(string)); dt1.Columns.Add("Time",typeof(double)); dt1.Columns.Add("Calls",typeof(double)); dt1.Columns.Add("Region",typeof(string)); dt2.Columns.Add("Name",typeof(string)); dt2.Columns.Add("CChats",typeof(double)); dt2.Columns.Add("AChats",typeof(double)); foreach(DataRow dr in _agentStates.Rows) DataRow row = dt1.NewRow()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；row["Name"] = dr[0].ToString()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；row["Status"] = dr[1].ToString()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；row["Time"] = Convert.ToDouble(dr[2].ToString()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；row["Calls"] = Convert.ToDouble(dr[3].ToString()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；row["Region"] = dr[4].ToString()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；dt1.Rows.Add(row); foreach(DataRow dr in _chatCount.Rows) DataRow row = dt2.NewRow()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；row["Name"] = dr[0].ToString()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；row["CChats"] = Convert.ToDouble(dr[1].ToString()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；row["AChats"] = Convert.ToDouble(dr[2].ToString()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；dt2.Rows.Add(row); var result = from table1 in dt1.AsEnumerable() join table2 in dt2.AsEnumerable() on (string)table1["Name"] equals (string)table2["Name"] select new Name = (string)table2["Name"], Status = (string)table1["Status"], Time = (double)table1["Time"], Calls = (double)table1["Calls"], Region = (string)table1["Region"], CChats = (double)table2["CChats"] foreach (var item in result) listBox1.Items.Add(item.Name " " item.CChats.ToString()；
& W0 m2 I9 d5 Z3 K 解决方案:
% P3 e6 N3 G/ M% U 您要在LEFT JOIN操作概念(这是概念)OUTER JOIN，而不是INNER JOIN）。8 \7 D% I- f8 }
表A和B左外连接(或简称左连接)的结果总是包含左表（A）即使连接条件没有在右表中找到任何匹配的记录（B）。! G3 _) B) D- ~+ ^- G9 L: r
来源：http：//zh.wikipedia.org/wiki/Join_（SQL）#Left_outer_join
- Y. D/ p9 [: L+ i& l+ D7 a) b杰夫·阿特伍德（Jeff4 N. w7 Z/ T/ J2 ]" y5 ~/ K7 p
Atwood）对不同的连接也有很好的视觉解释。/ t7 Z+ K3 f) r( w
在LINQ中，这样做比较SQL笨拙得多，类似于：2 C+ b( `9 T" Y' B9 ?
var LeftJoin = from user in Usersjoin chat in Chatson user.Name equals user.Name into JoinedTablesfrom row in JoinedTables.DefaultIfEmpty()select new Name, AgentStatus, TimeInState, TaskHandled, Region, CChats = chat != null ? chat.CChats : 0 AChats = chat != null ? chat.AChats 来源：http： //codingsense.wordpress.com/2009/03/08/left-join-right-join-
& f% K) x. H$ W1 v+ yusing-linq/
. f2 j8 q: Y0 Y8 u当然，加入Name不是理想的选择-6 M$ A4 U/ z( Y: E
实际上希望你ID在现实世界中有一个，或者真的可以保证名字是唯一的，并且会被一致地提供(例如，没有随空间或者大小写的区别)。
" R; |- X; }6 U//编辑，处理更新的代码示例
" u' I& X9 a* f) E6 L. m. g试试这个：
/ {( p& N7 ^3 W8 w9 P$ {var result = from table1 in dt1.AsEnumerable() join table2 in dt2.AsEnumerable() on (string)table1["Name"] equals (string)table2["Name"] into joinedDt from table2 in joinedDt.DefaultIfEmpty() select new Name = (string)table1["Name"], Status = (string)table1["Status"], Time = (double)table1["Time"], Calls = (double)table1["Calls"], Region = (string)table1["Region"], CChats = (table2 != null ? (double)table2["CChats"] :0) ； |
|
技术问答
237 人阅读
|
0 人回复
|
2023-09-12
