那么怎么可以體現二者區別呢？
很簡單，連接時對應字段一致。也就是：
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
