CREATE TABLE `fruit_month_price` (
  `name` char(20) NOT NULL,
  `year` int(11) NOT NULL,
  `month` int(11) NOT NULL,
  `price` float NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

insert fruit_month_price(name, year, month, price)
select 'apple', 2017, 11, 4.3
union
select 'apple', 2015, 8, 5.7
union
select 'apple', 2017, 9, 1.2
union
select 'apple', 2015, 10, 2.37
union
select 'orange', 2011, 11, 5.368
union
select 'orange', 2015, 11, 14.3
union
select 'orange', 2014, 11, 8.5
union
select 'banana', 2016, 11, 2.3
union
select 'banana', 1993, 11, 1.68
...
select main.*
from fruit_month_price main
where id =
(
	select id from fruit_month_price sub
    where sub.name = main.name and main.year = sub.year
    order by price desc
	limit 1
) 
order by name,year,month
/*加字段*/
alter table fruit_month_price add column num float not null default 0;
/*創建存儲過程，更新字段
每個語句結束後都必須加';'
set語句必須位於所有的declare語句的後面，不然會報語法錯誤
declare i int(20);
set i = 0;
declare finished int(2);//報錯
declare語句好像只能在只能在存儲過程以及函數裡面使用（cursor確實是，這個好像是）
(也就是說享用cursor遍歷一個表必須創建一個存儲過程或函數？)
declare聲明變量時不帶@符號，可以用set @i = 3直接聲明變量@i
由於不帶@所以變量名盡量不要與查詢的表的列名相同
賦值符號有兩種=和:=，set是=即可，select賦值時使用:=（此時=被用作比較運算符）
set @i = 2;
select @i = 3;#0
select @i = 2;#1
select @i;    #2
select @i := 3;#3
select @i;    #3
*/
drop procedure if exists test;
delimiter //
create procedure test()
begin

  declare i int(20);
  declare finished int(2);
  declare c cursor for select id from fruit_month_price;
  declare continue handler for not found set finished = 1;
  set finished = 0;
  set i = 0;

  open c;
  fetch c into i;
  while finished <= 0 do
	update fruit_month_price set num = round(rand() * 100, 0) where id = i;
	fetch c into i;
  end while;
  close c;
end//
delimiter ;
/*調用存儲過程*/
call test()
/*按水果名+年份統計
where條件中子查詢加了limit之後報錯
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
在這裡in和=效果差不多好像*/
select main.*,sumA.amount
from fruit_month_price main
left join (
    select name,year,sum(num * price) as amount
	from fruit_month_price
	group by name,year
) sumA on sumA.name = main.name and sumA.year = main.year
where main.id = 
(
	select id from fruit_month_price sub
    where sub.name = main.name and sub.year = main.year
    order by price desc
    limit 1
)
order by main.name,main.year,main.month;

select main.*
from fruit_month_price main
order by name,year;
/*具體作用不太清楚，創建語句時出錯，一直在查詢不結束時，重連數據庫
運行語句，用id殺掉第一條*/
show processlist;
kill 74

CREATE TABLE [dbo].[fruit_month_price](
	[name] [varchar](20) NOT NULL,
	[year] [int] NOT NULL,
	[month] [int] NOT NULL,
	[price] [float] NOT NULL,
	[id] [int] IDENTITY(1,1) NOT NULL,
	primary key clustered(id asc)
)
--drop table fruit_month_price
--查詢每種水果每年的最高價格
select main.*
from fruit_month_price main
where id = 
(
	select top 1 id from fruit_month_price sub
    where sub.name = main.name and sub.year = main.year
    order by price desc
)
order by name,year,month
--添加數量字段
alter table fruit_month_price add num float not null default 0
--為數量字段賦隨機值
declare @i int, @c cursor;
set @c = cursor for select id from fruit_month_price
set @i = 0
open @c
fetch next from @c into @i
while(@@FETCH_STATUS = 0)
begin
	update fruit_month_price set num = round((rand() * 100), 0)
	where id = @i;
	fetch next from @c into @i
end
close @c
deallocate @c
--查詢每種水果的最高價格所在行的信息並統計出每種水果的銷售總額
select main.*,sumA.amount
from fruit_month_price main
left join (
    select name,sum(num * price) as amount
	from fruit_month_price
	group by name
) sumA on sumA.name = main.name
where id in 
(
	select top 1 id from fruit_month_price sub
    where sub.name = main.name
    order by price desc
)
order by name,year,month
--按水果名+年份統計
select main.*,sumA.amount
from fruit_month_price main
left join (
    select name,year,sum(num * price) as amount
	from fruit_month_price
	group by name,year
) sumA on sumA.name = main.name and sumA.year = main.year
where id in 
(
	select top 1 id from fruit_month_price sub
    where sub.name = main.name and sub.year = main.year
    order by price desc
)
order by name,year,month

select main.*
from fruit_month_price main
order by name,year;
select name,year,num*price
from fruit_month_price main
order by name,year