那么怎么可以體現(xiàn)二者區(qū)別呢？
很簡(jiǎn)單，連接時(shí)對(duì)應(yīng)字段一致。也就是：
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;
這樣執(zhí)行select后結(jié)果分別是8和9
很簡(jiǎn)單，連接時(shí)對(duì)應(yīng)字段一致。也就是：
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;
這樣執(zhí)行select后結(jié)果分別是8和9
2017-09-09
為什么呢？
因?yàn)樽笸膺B接和右外連接時(shí)字段的順序是不一樣的，那么全連接時(shí)就意味著字段一是user_name 的連接而字段二是a.over和b.over的；連接字段三是b.over和a.over的連接。不存在重復(fù)值，此時(shí)union和union all等價(jià)，執(zhí)行結(jié)果也是一樣的
因?yàn)樽笸膺B接和右外連接時(shí)字段的順序是不一樣的，那么全連接時(shí)就意味著字段一是user_name 的連接而字段二是a.over和b.over的；連接字段三是b.over和a.over的連接。不存在重復(fù)值，此時(shí)union和union all等價(jià)，執(zhí)行結(jié)果也是一樣的
2017-09-09
關(guān)于union和union all用法
評(píng)論里小伙伴說的很清楚
但是關(guān)于老師使用的code
用哪個(gè)都是一樣的
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;
評(píng)論里小伙伴說的很清楚
但是關(guān)于老師使用的code
用哪個(gè)都是一樣的
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
也不要?jiǎng)硬粍?dòng)就說老師不行 非要現(xiàn)成的送到嘴邊？建一個(gè)小db分分鐘好伐 模板不謝
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('孫悟空','斗戰(zhàn)勝佛');
mysql-sql> insert into user1
... values('豬八戒','凈壇使者');
mysql-sql> insert into user1
... values('沙和尚','金身羅漢');
... values('唐僧','旃檀功德佛');
mysql-sql> insert into user1
... values('孫悟空','斗戰(zhàn)勝佛');
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
對(duì)于 JOIN語(yǔ)句，會(huì)先級(jí)聯(lián)，然后執(zhí)行WHERE過濾，所以WHERE B.ID IS NULL才能實(shí)現(xiàn)NOT IN的效果！
|
|
2017-08-21
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
