概要说明
- JOIN: 如果表中有至少一个匹配,则返回行
- LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
- RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
- FULL JOIN: 只要其中一个表中存在匹配,就返回行
注:本表:蓝色:左边的表(left(本)),黄色:右边表(right(本)),绿色:共有的数据(表关系体现)
合并表:left = jion+ left独有的 right = jion + right独有的 full join = jion+ left独有的 +right独有的
运行效果
代码
using System;
using System.Collections.Generic;
namespace Join模拟
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Hello World!");
for(int i = 0; i < 10; i++)
{
TableA.insert("name_a" + i);
}
TableA.selectAll();
for (int i = 0; i <14; i++)
{
int id = i;
if (i >= 10)
{
id = -1;
}
if (i % 2 == 0)
{
TableB.insert(id, "name_b" + i);
}
}
TableB.selectAll();
Join.full();
Join.join();
Join.Leftjoin();
Join.rightJoin();
Console.ReadLine();
}
}
class Join
{
public static void full()
{
Console.WriteLine("---full join----------------");
foreach (TableA tableA in TableA.tableAs)
{
TableB tableB = TableB.getWhere(tableA.id);
string nameb = "";
if (tableB != null)
{
nameb = tableB.nameb;
}
Console.WriteLine("id:" + tableA.id + " namea:" + tableA.namea+ " nameb:" + nameb);
}
foreach(TableB tableB in TableB.tableBs)
{
if (tableB.id >= 5)
{
Console.WriteLine("id:" + " " + " namea:" + " " + " nameb:" + tableB.nameb);
}
}
}
public static void join()
{
Console.WriteLine("---join----------------");
foreach (TableA tableA in TableA.tableAs)
{
TableB tableB = TableB.getWhere(tableA.id);
string nameb = "";
if (tableB != null)
{
nameb = tableB.nameb;
Console.WriteLine("id:" + tableA.id + " namea:" + tableA.namea + " nameb:" + nameb);
}
}
}
public static void Leftjoin()
{
Console.WriteLine("---left join----------------");
foreach (TableA tableA in TableA.tableAs)
{
TableB tableB = TableB.getWhere(tableA.id);
string nameb = "";
if (tableB != null)
{
nameb = tableB.nameb;
}
Console.WriteLine("id:" + tableA.id + " namea:" + tableA.namea + " nameb:" + nameb);
}
}
public static void rightJoin()
{
Console.WriteLine("---right join----------------");
foreach(TableB tableB in TableB.tableBs)
{
TableA tableA = TableA.getWhere(tableB.aId);
string aid = " ";
string namea = " ";
if (tableA != null)
{
aid = tableA.id.ToString();
namea = tableA.namea;
}
Console.WriteLine("id:" + aid + " namea:" + namea + " nameb:" + tableB.nameb);
}
}
}
// 思考:静态部分的冗余代码如何简化(除了类型差别,其他都是相同的)
class Table
{
public int id;
}
class TableA: Table
{
static int index = 0;
static public List<TableA> tableAs = new List<TableA>();
private TableA()
{
id = index++;
}
public static void insert(string name)
{
TableA tableA = new TableA();
tableA.namea = name;
tableAs.Add(tableA);
}
public String namea;
public static void selectAll()
{
Console.WriteLine("---TableA data----------------");
foreach (TableA tableA in tableAs)
{
Console.WriteLine("id:" + tableA.id + " namea:" + tableA.namea);
}
}
public static TableA getWhere(int id)
{
foreach (TableA tableA in tableAs)
{
if(tableA.id == id)
{
return tableA;
}
}
return null;
}
}
class TableB : Table
{
static int index = 0;
static public List<TableB> tableBs = new List<TableB>();
private TableB()
{
id = index++;
}
public int aId;
public string nameb;
public static void insert(int aid,string name)
{
TableB tableB = new TableB();
tableB.aId = aid;
tableB.nameb = name;
tableBs.Add(tableB);
}
public static void selectAll()
{
Console.WriteLine("---TableB data----------------");
foreach (TableB tableB in tableBs)
{
Console.WriteLine("id:" + tableB.id + " aid:" + tableB.aId + " nameb:" + tableB.nameb);
}
}
public static TableB getWhere(int aid)
{
foreach (TableB tableB in tableBs)
{
if(tableB.aId == aid)
{
return tableB;
}
}
return null;
}
}
}
代码优化(类的id自增加都放到了基类里)
using System;
using System.Collections.Generic;
namespace Join模拟
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Hello World!");
for(int i = 0; i < 10; i++)
{
TableA.insert("name_a" + i);
}
TableA.selectAll();
for (int i = 0; i <14; i++)
{
int id = i;
if (i >= 10)
{
id = -1;
}
if (i % 2 == 0)
{
TableB.insert(id, "name_b" + i);
}
}
TableB.selectAll();
Join.full();
Join.join();
Join.Leftjoin();
Join.rightJoin();
Console.ReadLine();
}
}
class Join
{
public static void full()
{
Console.WriteLine("---full join----------------");
foreach (TableA tableA in TableA.tableAs)
{
TableB tableB = TableB.getWhere(tableA.id);
string nameb = "";
if (tableB != null)
{
nameb = tableB.nameb;
}
Console.WriteLine("id:" + tableA.id + " namea:" + tableA.namea+ " nameb:" + nameb);
}
foreach(TableB tableB in TableB.tableBs)
{
if (tableB.id >= 5)
{
Console.WriteLine("id:" + " " + " namea:" + " " + " nameb:" + tableB.nameb);
}
}
}
public static void join()
{
Console.WriteLine("---join----------------");
foreach (TableA tableA in TableA.tableAs)
{
TableB tableB = TableB.getWhere(tableA.id);
string nameb = "";
if (tableB != null)
{
nameb = tableB.nameb;
Console.WriteLine("id:" + tableA.id + " namea:" + tableA.namea + " nameb:" + nameb);
}
}
}
public static void Leftjoin()
{
Console.WriteLine("---left join----------------");
foreach (TableA tableA in TableA.tableAs)
{
TableB tableB = TableB.getWhere(tableA.id);
string nameb = "";
if (tableB != null)
{
nameb = tableB.nameb;
}
Console.WriteLine("id:" + tableA.id + " namea:" + tableA.namea + " nameb:" + nameb);
}
}
public static void rightJoin()
{
Console.WriteLine("---right join----------------");
foreach(TableB tableB in TableB.tableBs)
{
TableA tableA = TableA.getWhere(tableB.aId);
string aid = " ";
string namea = " ";
if (tableA != null)
{
aid = tableA.id.ToString();
namea = tableA.namea;
}
Console.WriteLine("id:" + aid + " namea:" + namea + " nameb:" + tableB.nameb);
}
}
}
class Index
{
public Index()
{
index = 0;
}
public int index;
}
// 思考:静态部分的冗余代码如何简化(除了类型差别,其他都是相同的)
class Table
{
protected Table(string subName)
{
id = Table.getIndex(subName);
}
public int id;
public static SortedList<string, Index> keyValuePairs = new SortedList<string, Index>();
public static void AddIndex(string keyName)
{
keyValuePairs.Add(keyName, new Index());
}
public static int getIndex(string keyName)
{
keyValuePairs.TryGetValue(keyName, out Index index);
if (index != null)
{
return index.index++;
}
return 0;
}
}
class TableA: Table
{
static TableA()
{
Table.AddIndex(tbName);
}
//static int index = 0;
static string tbName = "TableA";
static public List<TableA> tableAs = new List<TableA>();
private TableA():base(tbName)
{
//id = index++;
//id = Table.getIndex("TableA");
}
public static void insert(string name)
{
TableA tableA = new TableA();
tableA.namea = name;
tableAs.Add(tableA);
}
public String namea;
public static void selectAll()
{
Console.WriteLine("---TableA data----------------");
foreach (TableA tableA in tableAs)
{
Console.WriteLine("id:" + tableA.id + " namea:" + tableA.namea);
}
}
public static TableA getWhere(int id)
{
foreach (TableA tableA in tableAs)
{
if(tableA.id == id)
{
return tableA;
}
}
return null;
}
}
class TableB : Table
{
static TableB()
{
Table.AddIndex(tbName);
}
//static int index = 0;
static string tbName = "TableB";
static public List<TableB> tableBs = new List<TableB>();
private TableB():base(tbName)
{
//id = index++;
//id = Table.getIndex("TableB");
}
public int aId;
public string nameb;
public static void insert(int aid,string name)
{
TableB tableB = new TableB();
tableB.aId = aid;
tableB.nameb = name;
tableBs.Add(tableB);
}
public static void selectAll()
{
Console.WriteLine("---TableB data----------------");
foreach (TableB tableB in tableBs)
{
Console.WriteLine("id:" + tableB.id + " aid:" + tableB.aId + " nameb:" + tableB.nameb);
}
}
public static TableB getWhere(int aid)
{
foreach (TableB tableB in tableBs)
{
if(tableB.aId == aid)
{
return tableB;
}
}
return null;
}
}
}