--加查詢條件是為了顯示效果，去掉沒用的記錄
--兩個表的信息 圖一
select * from author where author_id in (1, 2, 3);
select * from book where author_id in (1, 2, 3) 
order by author_id,price desc;
--outer apply實現 圖二 上
select main.*, isnull(sub.book_name, '') as book_name, isnull(sub.price, 0) as price
from author main
outer apply (select top 1 book_name, price 
           from book s 
		   where s.author_id = main.author_id
		   order by price
) sub
where isnull(sub.price, 0) > 0;
--left join實現 圖二下
select main.*, isnull(sub.book_name, '') as book_name, isnull(sub.price, 0) as price
from author main
left join book sub on sub.author_id = main.author_id
                      and sub.id = (select top 1 s.id 
					                from book s
									where s.author_id = main.author_id)
where isnull(sub.price, 0) > 0;
--錯誤語句 
select main.*,sub.book_name
from author main
left join (select top 1 book_name 
           from book s 
		   where s.author_id = main.author_id --无法绑定由多个部分组成的标识符 "main.author_id"。可看出在這個位置獲取不到主表的數據
) sub on (1=1);
--在outer apply的別名後也不能再進行過濾了
select main.*, isnull(sub.book_name, '') as book_name, isnull(sub.price, 0) as price
from author main
outer apply (select top 1 book_name, price 
           from book s 
		   where s.author_id = main.author_id
		   order by price
) sub on (1=1)--該處有語法錯誤
where isnull(sub.price, 0) > 0;

--參照表更新表數據的方式 雖然下面語句參照完全沒有必要
update main
set main.age = main.age + 1
from author main
inner join book sub on sub.author_id in (1, 2, 3) 
           and main.author_id = sub.author_id;

create table fruit(
fruit_name char(40) not null,
fruit_id int(5) not null,
id int not null auto_increment,
primary key(id)
)
/*關閉安全更新模式，同時參照子表更新主表*/
set sql_safe_updates = 0;
update fruit_month_price main
inner join fruit sub on sub.fruit_name = main.name
set main.fruit_id = sub.fruit_id;
/*查詢水果表，並參照出價格價格最高所在記錄的數量和價格*/
select main.*, ifnull(sub.num, 0) as num, ifnull(sub.price, 0) as price
from fruit main
left join fruit_month_price sub on main.fruit_id = sub.fruit_id
								   and sub.id = (select id from fruit_month_price s
                                                  where s.fruit_id = main.fruit_id
                                                  order by price desc
                                                  limit 1)