有这么两表table1
deskid drinkid drinknum drinksaccount
1001 102 1 10
1002 110 1 10
table2
deskid dishesid dishesnum dishesaccount
1001 1001 1 50
1001 1008 1 45
1002 1009 1 80
我是想得到这样的一个数据集
deskid drinkid drinknum drinksaccount dishesid dishesnum dishesaccount
1001 102 1 10 1001 1 50
1001 110 1 10 1008 1 45
1002 null null null 1009 1 80用了很多方法,都都没啥用了,唉,期待着吧
deskid drinkid drinknum drinksaccount
1001 102 1 10
1002 110 1 10
table2
deskid dishesid dishesnum dishesaccount
1001 1001 1 50
1001 1008 1 45
1002 1009 1 80
我是想得到这样的一个数据集
deskid drinkid drinknum drinksaccount dishesid dishesnum dishesaccount
1001 102 1 10 1001 1 50
1001 110 1 10 1008 1 45
1002 null null null 1009 1 80用了很多方法,都都没啥用了,唉,期待着吧
FROM Emp, Dept
WHERE Emp.Deptno(+) = Dept.Deptno
也可以写成:
SELECT ename , dname
FROM Emp RIGHT JOIN Dept
ON Emp.Deptno = Dept.Deptno
此SQL文使用了右连接,即“(+)”所在位置的另一侧为连接的方向,右连接说明等号右侧的所有记录均会被显示,无论其在左侧是否得到匹配,也就是说上例中无论会不会出现某个部门没有一个员工的情况,这个部门的名字都会在查询结果中出现。 反之:
查看如下语句:
SELECT ename , dname
FROM Emp, Dept
WHERE Emp.Deptno = Dept.Deptno(+)
也可以写成:
SELECT ename , dname
FROM Emp LEFT JOIN Dept
ON Emp.Deptno = Dept.Deptno 则是左连接,无论这个员工有没有一个能在Department表中得到匹配的部门号,这个员工的记录都会被显示
select table1.*,table2.* from table1 right join table2 on table1.deskid=table2.deskid
//第一个表的字段有:ID Name
DataTable dt1 = new DataTable();
dt1.Columns.Add( "ID ", typeof(int));
dt1.Columns.Add( "Name ", typeof(string));
dt1.PrimaryKey = new DataColumn[] { dt1.Columns[0]};
dt1.Rows.Add(1, "Age ");
dt1.Rows.Add(2, "Apple ");
dt1.Rows.Add(3, "Orange ");
//第二个表的字段有: ID Price
DataTable dt2 = new DataTable();
dt2.Columns.Add( "ID ", typeof(int));
dt2.Columns.Add( "Price ", typeof(decimal));
//dt2.PrimaryKey = new DataColumn[] { dt2.Columns[0] };
dt2.Rows.Add(1, 10);
dt2.Rows.Add(2, 6);
dt2.Rows.Add(3, 7);
dt2.Rows.Add(2, 9);//第三個表,ID Name Price
DataTable dt3 = dt1.Copy();
dt3.Merge(dt2);
FROM TABLE1 a left join table2 b on a.deskid =b.deskid
如果它的字段有的是空的话,显示出来是null呵呵
表A table1
deskid drinkid drinknum drinksaccount
1001 102 1 10
1002 110 1 10表B table2
deskid dishesid dishesnum dishesaccount
1001 1001 1 50
1001 1008 1 45
1002 1009 1 80SQL:
select
B.deskid,
B.dishesid ,
B.dishesnum ,
B.dishesaccount,
A.dishesid ,
A.dishesnum ,
A.dishesaccount
from
table1 B
left join table A
on A.deskid = B.deskid
where
......
CREATE TABLE table1
(
deskid VARCHAR(20),
drinkid VARCHAR(20),
drinknum VARCHAR(20),
drinksaccount VARCHAR(20)
)CREATE TABLE table2
(
deskid VARCHAR(20),
dishesid VARCHAR(20),
dishesnum VARCHAR(20),
dishesaccount VARCHAR(20)
)INSERT INTO table1 SELECT '1001','102','1','10' UNION SELECT '1002','110','1','10' INSERT INTO table2
SELECT '1001','1001','1','50' UNION
SELECT '1001','1008','1','45' UNION
SELECT '1002','1009','1','80'
goselect b.deskid,a.drinkid,a.drinknum,a.drinksaccount,b.dishesid,b.dishesnum,b.dishesaccount
from(select rn = row_number() over (order by getdate()),* from table1) a
right join
(select rn = row_number() over (order by getdate()),* from table2) b
on a.rn = b.rndrop table table1,table2deskid drinkid drinknum drinksaccount dishesid dishesnum dishesaccount
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
1001 102 1 10 1001 1 50
1001 110 1 10 1008 1 45
1002 NULL NULL NULL 1009 1 80(3 行受影响)--SQL版的,过来凑凑热闹!
deskid drinkid drinknum drinkaccount dishesid dishesnum dishesaccount
1001 102 1 10 1001 1 50
1001 NULL NULL NULL 1008 1 45
1002 110 1 10 1009 1 80你们提供的方法,我也有试过,可都不OK,本以为10楼的可以了,后来仔细一看,第二行的110,1 10应该是1002桌的数据,哎,真麻烦
或者这样说,是不是想要下面这样的效果?table1
deskid drinkid drinknum drinksaccount
1001 102 1 10
1002 110 1 10
1003 111 1 10table2
deskid dishesid dishesnum dishesaccount
1001 1001 1 50
1001 1008 1 45
1001 1010 1 46
1002 1009 1 80
1002 1011 1 81
1002 1012 1 82
1003 1013 1 83
1003 1014 1 84正确的数据
deskid drinkid drinknum drinkaccount dishesid dishesnum dishesaccount
1001 102 1 10 1001 1 50
1001 NULL NULL NULL 1008 1 45
1001 NULL NULL NULL 1010 1 46
1002 110 1 10 1009 1 80
1002 NULL NULL NULL 1011 1 81
1002 NULL NULL NULL 1012 1 82
1003 111 1 10 1013 1 83
1003 NULL NULL NULL 1014 1 84