那么怎么可以体现二者区别呢？
很简单，连接时对应字段一致。也就是：
select a.user_name,a.over,b.over
... from user1 a left join user2 b on a.user_name=b.user_name
... union
... select b.user_name,a.over,b.over
... from user1 a right join user2 b on a.user_name=b.user_name;
这样执行select后结果分别是8和9
很简单，连接时对应字段一致。也就是：
select a.user_name,a.over,b.over
... from user1 a left join user2 b on a.user_name=b.user_name
... union
... select b.user_name,a.over,b.over
... from user1 a right join user2 b on a.user_name=b.user_name;
这样执行select后结果分别是8和9
2017-09-09
为什么呢？
因为左外连接和右外连接时字段的顺序是不一样的，那么全连接时就意味着字段一是user_name 的连接而字段二是a.over和b.over的；连接字段三是b.over和a.over的连接。不存在重复值，此时union和union all等价，执行结果也是一样的
因为左外连接和右外连接时字段的顺序是不一样的，那么全连接时就意味着字段一是user_name 的连接而字段二是a.over和b.over的；连接字段三是b.over和a.over的连接。不存在重复值，此时union和union all等价，执行结果也是一样的
2017-09-09
关于union和union all用法
评论里小伙伴说的很清楚
但是关于老师使用的code
用哪个都是一样的
select a.user_name,a.over,b.over
... from user1 a left join user2 b on a.user_name=b.user_name
... union/union all
... select b.user_name,b.over,a.over
... from user1 a right join user2 b on a.user_name=b.user_name;
评论里小伙伴说的很清楚
但是关于老师使用的code
用哪个都是一样的
select a.user_name,a.over,b.over
... from user1 a left join user2 b on a.user_name=b.user_name
... union/union all
... select b.user_name,b.over,a.over
... from user1 a right join user2 b on a.user_name=b.user_name;
2017-09-09
mysql-sql> insert into user2
... values('孙悟空','成佛');
mysql-sql> insert into user2
... values('牛魔王','被降服');
mysql-sql> insert into user2
... values('蛟魔王','被降服');
mysql-sql> insert into user2
... values('鹏魔王','被降服');
mysql-sql> insert into user2
... values('狮驼王','被降服');
... values('孙悟空','成佛');
mysql-sql> insert into user2
... values('牛魔王','被降服');
mysql-sql> insert into user2
... values('蛟魔王','被降服');
mysql-sql> insert into user2
... values('鹏魔王','被降服');
mysql-sql> insert into user2
... values('狮驼王','被降服');
2017-09-09
mysql-sql> insert into user1
... values('唐僧','旃檀功德佛');
mysql-sql> insert into user1
... values('孙悟空','斗战胜佛');
mysql-sql> insert into user1
... values('猪八戒','净坛使者');
mysql-sql> insert into user1
... values('沙和尚','金身罗汉');
... values('唐僧','旃檀功德佛');
mysql-sql> insert into user1
... values('孙悟空','斗战胜佛');
mysql-sql> insert into user1
... values('猪八戒','净坛使者');
mysql-sql> insert into user1
... values('沙和尚','金身罗汉');
2017-09-09
mysql-sql> create journey_to_the_west;
mysql-sql> use journey_to_the_west;
mysql-sql> create table user1(
... user_name varchar(20),
... over varchar(20)
... );
mysql-sql> create table user2(
... user_name varchar(20),
... over varchar(20)
... );
mysql-sql> use journey_to_the_west;
mysql-sql> create table user1(
... user_name varchar(20),
... over varchar(20)
... );
mysql-sql> create table user2(
... user_name varchar(20),
... over varchar(20)
... );
2017-09-09
select d.user_name,c.timestr,kills from (
select user_id,timestr,kills,
(select count(*) from killscount b where
b.user_id = a.user_id and a.kills<=b.kills ) as cnt
from killscount a
group by user_id,timestr,kills
) c join tangtang d on c.user_id = d.id
where cnt <=2
select user_id,timestr,kills,
(select count(*) from killscount b where
b.user_id = a.user_id and a.kills<=b.kills ) as cnt
from killscount a
group by user_id,timestr,kills
) c join tangtang d on c.user_id = d.id
where cnt <=2
2017-08-18
