/*
EXPLAIN
*/
/*---------------------------------新增名單(不同DB版、同DB請刪除`bf-2019-04-23_C`.) -------------------------------------------------*/
INSERT INTO `bf-2019-04-23_C`.signup_list (
#list_ID,
maitm_id,
Mem_ID,
open_id,
`name`,
tel,
MF_Groups,
wechat_ID,
city,
#signup_datetime,
#checkin_validityperiod,
#complete_datetime,
#signup_method,
stage,
`status`  
) 
SELECT 
1,c.Mem_ID,c.open_id,b.name,b.tel,b.MF_Groups,b.wechat_ID,b.city,'1','1'
FROM championship AS a
LEFT JOIN player AS b ON b.champ_id = a.champ_id
LEFT JOIN Member AS c ON c.Mem_ID = b.Mem_ID
WHERE a.end_datetime IS NOT NULL 
AND YEAR(a.end_datetime) = "2019" 
AND a.champ_type = '1'
AND (b.deledted = '0' OR b.deledted  IS NULL) 
AND b.Advanced >= '1'
ORDER BY b.champ_id ASC ,b.player_ID ASC;


/*---------------------------------名單查詢-------------------------------------------------*/
SELECT * FROM signup_list WHERE list_ID = 256


/*--------------------------------- 名單初始化-------------------------------------------------*/
UPDATE signup_list 
SET stage = 1,
checkin_validityperiod = NULL,
signup_datetime = NULL,
signup_method = NULL
/*WHERE list_ID = 256*/

/*---------------------------------名單更改 -------------------------------------------------*/
UPDATE signup_list 
SET signup_datetime = NOW(),
checkin_validityperiod = "2019-07-23 19:13:26",
complete_datetime = NULL,
signup_method = NULL,  
stage = 2
WHERE list_ID = 256

/*---------------------------------大中華盃2019年度海選賽 -------------------------------------------------*/
SELECT c.PROVINCE_NAME,a.champ_id,a.MC_SerialNO,a.end_datetime 
FROM championship AS a ,StoreInfo AS b ,SMT_AREA AS c
WHERE a.ST_ID = b.ST_ID AND b.AREA_ID = c.AREA_ID AND 
      a.end_datetime IS NOT NULL AND YEAR(a.end_datetime) = '2019' AND a.end_datetime < '2019-07-026 11:00:00' AND
      a.champ_type = '1'
ORDER BY a.begin_datetime DESC
        
/*---------------------------------新增參賽基本人員 -------------------------------------------------*/
SELECT e.Mem_ID,e.open_id,d.name,d.tel,d.MF_Groups,d.wechat_ID,d.city
FROM championship AS a
LEFT JOIN StoreInfo AS b ON b.ST_ID = a.ST_ID 
LEFT JOIN SMT_AREA AS c ON c.AREA_ID = b.AREA_ID
LEFT JOIN player AS d ON d.champ_id = a.champ_id
LEFT JOIN Member AS e ON e.Mem_ID = d.Mem_ID
WHERE 
YEAR(a.end_datetime) = '2019'
AND
a.end_datetime < '2019-07-026 11:00:00'
AND
a.champ_type = '1'
AND 
b.ST_ID IS NOT NULL 
AND
c.AREA_ID IS NOT NULL 
AND 
d.player_ID IS NOT NULL	
AND 
(d.deledted = '0' OR d.deledted  IS NULL) 
AND 
d.Advanced >= '1'
AND e.Mem_ID NOT IN (SELECT Mem_ID FROM `bf-2019-04-23_C`.signup_list WHERE maitm_id = 1)
ORDER BY a.champ_id ASC

/*---------------------------------查出不符合的參賽基本人員(不同DB版、同DB請刪除`bf-2019-04-23_C`.) -------------------------------------------------*/
SELECT list_ID,Mem_ID,open_id FROM `bf-2019-04-23_C`.signup_list 
WHERE maitm_id = 1 AND Mem_ID NOT IN (
    SELECT e.Mem_ID
    FROM championship AS a
    LEFT JOIN StoreInfo AS b ON b.ST_ID = a.ST_ID 
    LEFT JOIN SMT_AREA AS c ON c.AREA_ID = b.AREA_ID
    LEFT JOIN player AS d ON d.champ_id = a.champ_id
    LEFT JOIN Member AS e ON e.Mem_ID = d.Mem_ID
    WHERE 
    YEAR(a.end_datetime) = '2019'
    AND
    a.end_datetime < '2019-07-026 11:00:00'
    AND
    a.champ_type = '1'
    AND 
    b.ST_ID IS NOT NULL 
    AND
    c.AREA_ID IS NOT NULL 
    AND 
    d.player_ID IS NOT NULL	
    AND 
    (d.deledted = '0' OR d.deledted  IS NULL) 
    AND 
    d.Advanced >= '1'
)
ORDER BY list_ID ASC

/*---------------------------------刪除不符合的參賽基本人員(不同DB版、同DB請刪除`bf-2019-04-23_C`.) -------------------------------------------------*/
DELETE FROM `bf-2019-04-23_C`.signup_list 
WHERE list_ID IN('1','2')	
/*---------------------------------刪除各項用道的表 -------------------------------------------------*/ 
DELETE FROM match_player WHERE pla_id > 76;
DELETE FROM championship WHERE champ_id>86;
DELETE FROM player WHERE player_ID >663;
DELETE FROM sbc_score WHERE wec_id >122264;
DELETE FROM champmachines WHERE champ_id > 86;
DELETE FROM match_schedule WHERE matlis_id > 20;
/*---------------------------------模擬得分 -------------------------------------------------*/
INSERT INTO sbc_score(DATETIME,MC_ID,MC_SerialNO,mach_id,open_id,nickname,score,champion,update_datetime,upload_datetime,CS_ID,ST_ID)
SELECT NOW(),c.MC_ID,a.MC_SerialNO,'01',d.open_id,d.nickname,(SELECT FLOOR(0 + RAND() * 800)),'1',NOW(),NOW(),c.CS_ID,c.ST_ID
FROM championship AS a
LEFT JOIN player AS b ON b.champ_id = a.champ_id 
LEFT JOIN machines AS c ON c.MC_SerialNO = a.MC_SerialNO
LEFT JOIN member AS d ON d.Mem_ID = b.Mem_ID
WHERE a.champ_id = 86
/*---------------------------------模擬第幾輪第幾組得分-----------------------------------------------------------------------------*/
INSERT INTO sbc_score(DATETIME,MC_ID,MC_SerialNO,mach_id,open_id,nickname,score,champion,update_datetime,upload_datetime,CS_ID,ST_ID)
SELECT NOW(),d.MC_ID,b.MC_SerialNO,b.mach_id,b.open_id,c.name,(SELECT FLOOR(0 + RAND() * 800)),'1',NOW(),NOW(),d.CS_ID,d.ST_ID
FROM match_schedule AS a
LEFT JOIN match_player_wheel AS b ON(b.matlis_id = a.matlis_id AND b.status = 2)
LEFT JOIN player AS c ON (c.player_ID = b.player_ID)
LEFT JOIN machines AS d ON d.MC_SerialNO = b.MC_SerialNO
WHERE a.champ_id = 93 AND grouping = "A" AND a.status = 1 AND wheel = 1

/*---------------------------------晉級的排行 -------------------------------------------------*/
/*
Rank : 晉級的排行
champ_id : 賽事ID
p.datetime : 賽時結束日期時間
o.MC_SerialNO : 賽事所用的籃球機編號
p.champion = '1' 狀態 1:開啟
HAVING Rank <= 16 要晉級的人數
r.sex = 1 性別 依照賽事組別決定 男: 1 、女:2、混合則不寫使用 r.sex = 1 拿掉
*/
SELECT a.rank AS 'Rank', a.nickname, a.score ,a.datetime,a.ST_ID,a.open_id,
                      a.champion,Mem_ID 
                      FROM (SELECT champion,Mem_ID,open_id,nickname, score,DATETIME,ST_ID, 
                                  @prev := @curr, @curr := score, 
                                  @rank := IF(@prev = @curr, @rank, @rank + @tempCnt) AS rank, 
                                  @cnt := IF(@prev = @curr, @cnt + 1, 1) AS cnt, 
                                  @tempCnt := IF(@prev > @curr, 1, @cnt) AS rankCnt 
                            FROM( SELECT Mem_ID,open_id,champion,nickname,SUM(score) AS score,
                                        DATETIME,ST_ID 
                                  FROM (SELECT p.*,r.Mem_ID FROM champmachines AS o INNER JOIN sbc_score 
                                               AS p ON o.MC_SerialNO = p.MC_SerialNO AND 
                                               o.mach_id = p.mach_id ,championship AS q,
                                               Member AS r,player AS s 
                                        WHERE o.champ_id = q.champ_id AND
                                              q.champ_id = 86 AND 
                                              p.datetime >= q.begin_datetime AND 
                                              p.datetime < '2019-08-15 16:50:00' AND
                                              o.MC_SerialNO = '120004' AND 
                                              p.champion = '1' AND
                                              p.open_id = r.open_id 
                                              AND r.sex = 1
                                              AND s.champ_id = q.champ_id
                                              AND s.Mem_ID = r.Mem_ID
                                              AND (s.deledted IS NULL OR s.deledted = 0)      
                                              ORDER BY score DESC) X 
                                  GROUP BY x.open_id ORDER BY score DESC ) t 
                            ,(SELECT @curr := NULL, @prev := NULL, @rank := 1,@cnt:=0,@tempcnt:=0) s  ) a
                            HAVING Rank <= 16
     
/*---------------------------------查詢賽事的選手名單 -------------------------------------------------*/
SELECT 
champ_id,Mem_ID,NAME,tel,wechat_ID,MF_Groups,city 
FROM player
WHERE champ_id = 86
AND (deledted IS NULL OR deledted = 0)
/*---------------------------------晉級功能 -------------------------------------------------*/
UPDATE 
  player 
SET
  Advanced = (
    CASE
      WHEN Mem_ID = :Mem_ID_1 THEN 1 
      WHEN Mem_ID = :Mem_ID_2 THEN 2 
      WHEN Mem_ID = :Mem_ID_3 THEN 3 
      WHEN Mem_ID = :Mem_ID_4 THEN 4 
      WHEN Mem_ID = :Mem_ID_5 THEN 5 
      WHEN Mem_ID = :Mem_ID_6 THEN 6 
      WHEN Mem_ID = :Mem_ID_7 THEN 7 
      WHEN Mem_ID = :Mem_ID_8 THEN 8 
      ELSE 9 
    END
  ) 
WHERE champ_id = :champ_id 
  AND Mem_ID IN (
    :Mem_ID_1,
    :Mem_ID_2,
    :Mem_ID_3,
    :Mem_ID_4,
    :Mem_ID_5,
    :Mem_ID_6,
    :Mem_ID_7,
    :Mem_ID_8,
    :Mem_ID_9,
    :Mem_ID_10,
    :Mem_ID_11,
    :Mem_ID_12,
    :Mem_ID_13,
    :Mem_ID_14,
    :Mem_ID_15,
    :Mem_ID_16
  )
  
  Array ( [:champ_id] => 96 [:Mem_ID_1] => 2591 [:Mem_ID_2] => 3910 [:Mem_ID_3] => 6397 [:Mem_ID_4] => 33006 [:Mem_ID_5] => 15134 [:Mem_ID_6] => 36867 [:Mem_ID_7] => 2746 [:Mem_ID_8] => 15508 [:Mem_ID_9] => 47690 
  [:Mem_ID_10] => 111 [:Mem_ID_11] => 2083 [:Mem_ID_12] => 3173 [:Mem_ID_13] => 26444 [:Mem_ID_14] => 914 [:Mem_ID_15] => 86104 [:Mem_ID_16] => 75208 ) 
/*---------------------目前報到人員名單詳細---------------------------------------------------------*/
SELECT a.*,b.headimgurl,b.nickname,b.sex,b.open_appid ,c.jerseyNumber
FROM signup_list AS a 
LEFT JOIN Member AS b ON b.open_id = a.open_id 
LEFT JOIN match_jerseyNumber AS c ON c.open_id = a.open_id AND c.groupMode = a.MF_Groups
WHERE a.maitm_id = 1 AND a.status = 1
AND a.stage = 2 ORDER BY a.signup_datetime DESC LIMIT 100 OFFSET 0 ;

/*---------------------------------初始晉級人員 -------------------------------------------------*/
UPDATE player SET Advanced = NULL,deledted = NULL WHERE champ_id = 86
/*  
SELECT * FROM player WHERE champ_id = 86
*/  
/*----------------------------------查詢分組組別---------------------------------------------------------------------------------------------------------------------------------------------*/
SELECT * FROM match_schedule WHERE champ_id = 92 AND `status` = 1
ORDER BY grouping asc;

SELECT * FROM match_schedule WHERE champ_id = 92 AND `status` = 1 and matlis_id >= 60
ORDER BY grouping ASC;
/*----------------------------------查詢賽事晉級的名單---------------------------------------------------------------------------------------------------------------------------------------------*/
SELECT a.*,b.open_id FROM player as a
LEFT JOIN member AS b ON b.Mem_ID = a.Mem_ID 
WHERE a.champ_id = 86 AND (a.deledted IS NULL OR a.deledted = 0) AND a.Advanced >= 1
/*-----------------------------------查詢賽事分組後選手的名單--------------------------------------------------------------------------------------------------------------------------------------------*/
select 
a.champ_id,a.listsource,a.groupmode,a.advance,b.*,c.*
from championship as a 
LEFT JOIN match_schedule AS b ON b.champ_id = a.champ_id and b.status = 1
LEFT JOIN match_player AS c ON b.matlis_id = c.matlis_id
WHERE a.champ_id = 89
/*-----------------------------------查詢賽事選手的名單--------------------------------------------------------------------------------------------------------------------------------------------*/
SELECT a.player_ID,a.`name`,a.tel,a.wechat_ID,a.MF_Groups ,a.city,a.update_datetime,a.deledted,a.Advanced,b.nickname,b.sex,b.headimgurl,b.open_id
from player as a ,Member as b where a.Mem_ID = b.Mem_ID  and a.champ_id = 89  ORDER BY a.update_datetime DESC
/*-----------------------------------查詢賽事分組後選手的名單--------------------------------------------------------------------------------------------------------------------------------------------*/
SELECT 
*
/*
b.machine_list,b.grouping,
c.MC_SerialNO,c.mach_id,
d.player_ID,d.`name`,d.tel,d.wechat_ID,d.MF_Groups,d.city,d.update_datetime,d.deledted,d.Advanced,e.nickname,e.sex,e.headimgurl,e.open_id
*/
FROM championship AS a 
LEFT JOIN match_schedule AS b ON b.champ_id = a.champ_id AND b.status = 1
LEFT JOIN match_player AS c ON b.matlis_id = c.matlis_id
LEFT JOIN player as d ON d.player_ID = c.player_ID
LEFT JOIN Member as e ON e.open_id = c.open_id
WHERE a.champ_id = 90
ORDER BY d.update_datetime DESC

SELECT 
a.matlis_id,a.machine_list,a.grouping,
b.pla_id,b.MC_SerialNO,b.mach_id,
c.`name`,c.tel,c.wechat_ID,c.MF_Groups,c.city,c.update_datetime,c.deledted,d.nickname,d.sex,d.headimgurl,d.open_id
FROM match_schedule as a
LEFT JOIN match_player AS b ON b.matlis_id = a.matlis_id
LEFT JOIN player AS c ON c.player_ID = b.player_ID
LEFT JOIN Member AS d ON d.open_id = b.open_id
WHERE a.champ_id = 92 AND a.status = 1
ORDER BY a.matlis_id asc

SELECT 
a.matlis_id,a.machine_list,a.grouping,count(*) as cou
FROM match_schedule AS a
LEFT JOIN match_player AS b ON b.matlis_id = a.matlis_id
LEFT JOIN player AS c ON c.player_ID = b.player_ID
LEFT JOIN Member AS d ON d.open_id = b.open_id
WHERE a.champ_id = 92 AND a.status = 1
GROUP BY a.matlis_id
ORDER BY a.matlis_id ASC
/*-----------------------------------查驗重新分組的名單(過濾掉要重新分組選手的部分)[測試版]--------------------------------------------------------------------------------------------------------------------------------------------*/
select a.pla_id,b.*,c.open_id 
from match_player as a
LEFT JOIN player as b on b.player_ID = a.player_ID 
LEFT JOIN member AS c ON c.Mem_ID = b.Mem_ID
where a.pla_id in ("177","179") 
and b.champ_id = 92 AND (b.deledted IS NULL OR b.deledted = 0)

SELECT a.matlis_id,b.*
FROM match_player AS a
LEFT JOIN match_schedule AS b ON b.matlis_id = a.matlis_id
WHERE b.champ_id and a.pla_id not IN ("177","179") 
GROUP BY a.matlis_id
/*-----------------------------------查驗重新分組的名單(過濾掉要重新分組選手的部分)[完整版]--------------------------------------------------------------------------------------------------------------------------------------------*/
SELECT 
a.matlis_id,a.grouping,SUM(IF(b.pla_id,1,0)) as cou, 
sum(if(b.pla_id not IN ("177","178","179","186"),1,0)) as chacou,
if((SUM(IF(b.pla_id,1,0)) > SUM(IF(b.pla_id NOT IN ("177","178","179","186"),1,0))),1,0) as drift
FROM match_schedule as a 
LEFT JOIN match_player as b ON b.matlis_id = a.matlis_id
WHERE a.champ_id = 92 and a.status = 1
GROUP BY a.matlis_id
ORDER BY a.matlis_id ASC
/*-----------------------------------更新分組的狀態(1:開、2:關)--------------------------------------------------------------------------------------------------------------------------------------------*/
UPDATE match_schedule SET `status` = 1 WHERE matlis_id IN(62);

UPDATE match_schedule set `status` = 2 where matlis_id in(62);
/*-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
select a.matlis_id,a.grouping,b.pla_id,b.open_id,c.*
from match_schedule as a
LEFT JOIN match_player AS b ON b.matlis_id = a.matlis_id
LEFT JOIN player AS c ON c.player_ID = b.player_ID
WHERE a.champ_id = 92 AND a.status = 1 and a.matlis_id >= 57
AND (c.deledted IS NULL OR c.deledted = 0)
ORDER BY a.matlis_id ASC

SELECT a.pla_id,b.*,c.open_id 
FROM match_player AS a
LEFT JOIN player AS b ON b.player_ID = a.player_ID 
LEFT JOIN member AS c ON c.Mem_ID = b.Mem_ID
WHERE a.pla_id IN ("177","179") 
AND b.champ_id = 92 AND (b.deledted IS NULL OR b.deledted = 0)
/*---------------------------找出大中華盃賽事已結束的賽事----------------------------------------------------------------------------------------------------------------------------------------------------*/
SELECT champ_id,champ_name FROM championship WHERE champ_type = 1
AND maitm = 1 AND maitm_id = 1 
and end_datetime < NOW()
ORDER BY champ_id DESC
/*---------------------------查詢賽事的選手名單詳細----------------------------------------------------------------------------------------------------------------------------------------------------*/                    
SELECT a.player_ID,a.`name`,a.tel,a.wechat_ID,a.MF_Groups ,a.city,a.update_datetime,a.deledted,a.Advanced,b.nickname,b.sex,b.headimgurl,b.open_id
from player as a ,Member as b where a.Mem_ID = b.Mem_ID  and a.champ_id = 86  ORDER BY a.update_datetime DESC                   
/*---------------------------查詢賽事的晉級名單數量----------------------------------------------------------------------------------------------------------------------------------------------------*/                   
SELECT a.champ_id,a.champ_name,a.advance,SUM(IF(b.player_ID,1,0)) as actual
FROM championship as a
LEFT JOIN player AS b ON b.champ_id = a.champ_id   
where a.champ_type = 1 and a.maitm = 1 AND a.maitm_id = 1 
AND a.end_datetime < NOW()
AND (b.deledted IS NULL OR b.deledted = 0)
AND b.Advanced >= 1
GROUP BY a.champ_id 
HAVING a.advance = sum(if(b.player_ID,1,0))
ORDER BY a.champ_id DESC
/*-------------------------查詢報名選手名單，ID=1------------------------------------------------------------------------------------------------------------------------------------------------------*/
select * from `signup_list` where list_ID = 1
/*-------------------------查詢賽事選手名單(有效的)------------------------------------------------------------------------------------------------------------------------------------------------------*/     
select * from player where champ_id = 87 AND (deledted IS NULL OR deledted = 0)   
/*-------------------------查詢分組後的報到選手名單------------------------------------------------------------------------------------------------------------------------------------------------------*/
SELECT	* 
from match_schedule as a
LEFT JOIN match_player AS b ON b.matlis_id = a.matlis_id  
LEFT JOIN signup_list as c on c.open_id = b.open_id
WHERE a.champ_id = 86 and a.status = 1 
and c.status = 1

SELECT	*
FROM signup_list AS a
LEFT JOIN match_player AS b ON b.open_id = a.open_id
LEFT JOIN match_schedule as c ON c.matlis_id = b.matlis_id
WHERE a.list_ID = 1 and 
/*----------------------查詢分組的選手名單是否有重複(有效的)---------------------------------------------------------------------------------------------------------------------------------------------------------*/
select a.open_id,count(*)as cou 
from match_player as a
LEFT JOIN match_schedule as b ON b.matlis_id = a.matlis_id
where b.status = 1
GROUP BY a.open_id 
/*----------------------報到分組查詢這位報到的選手詳細、賽事ID---------------------------------------------------------------------------------------------------------------------------------------------------------*/
SELECT a.champ_id,b.*,c.open_id
FROM championship as a
LEFT JOIN player AS b ON b.champ_id = a.champ_id 
LEFT JOIN signup_list As c ON c.Mem_ID = b.Mem_ID
WHERE 
a.maitm_id = 1 
and a.champ_type = 1 AND a.matchmode = 1
AND (b.deledted IS NULL OR b.deledted = 0)
and c.list_ID = 1 and c.status
/*---------------------報到分組查詢目前分組的機台數是多少、每組目前幾人-------------------------------------------------------------------------------------------------------------------*/

EXPLAIN
SELECT a.*,COUNT(b.pla_id) AS cou , IFNULL(CONVERT(((LENGTH(a.machine_list) - LENGTH(REPLACE(a.machine_list,',','')))/ LENGTH(',')),SIGNED) +1 ,0) AS times

FROM match_schedule AS a

LEFT JOIN match_player AS b ON b.matlis_id = a.matlis_id 

LEFT JOIN signup_list AS c ON (c.open_id = b.open_id AND c.status = 1)

WHERE a.champ_id = 86 AND a.status = 1 

GROUP BY a.matlis_id
/*----------------------報到分組查詢個人資料---------------------------------------------------------------------------------------------------------------------------------------------------------*/

EXPLAIN
SELECT b.* 
FROM match_schedule AS a
LEFT JOIN match_player AS b ON b.matlis_id = a.matlis_id  
LEFT JOIN signup_list AS c ON c.open_id = b.open_id
WHERE a.champ_id = 86 AND a.status = 1 AND c.open_id = "okVO-v6H_EgRvjA92kJJn3uafLDE"
AND c.status = 1

/*----------------------------測試用------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
UPDATE signup_list SET signup_datetime = NULL,checkin_validityperiod = NULL,complete_datetime = NULL,signup_method = NULL,stage = 1
WHERE MF_Groups = 1

DELETE FROM player WHERE champ_id >= 86;

DELETE FROM championship WHERE champ_id >= 86;

DELETE FROM champmachines WHERE champ_id >= 86;

DELETE FROM signup_list WHERE list_ID >= 39;

DELETE FROM match_player

DELETE FROM match_schedule

SELECT * FROM player WHERE champ_id = 86


SELECT a.champ_id,b.*,c.open_id
FROM championship AS a
LEFT JOIN player AS b ON b.champ_id = a.champ_id 
LEFT JOIN signup_list AS c ON c.Mem_ID = b.Mem_ID
WHERE a.maitm_id = 1 
AND a.champ_type = 1 AND a.matchmode = 1
AND (b.deledted IS NULL OR b.deledted = 0)
AND c.list_ID = 1 AND c.status = 1;




SELECT a.champ_id,b.*,c.open_id
FROM championship AS a
LEFT JOIN player AS b ON b.champ_id = a.champ_id 
LEFT JOIN signup_list AS c ON c.Mem_ID = b.Mem_ID
WHERE a.maitm_id = 1 
AND a.champ_type = 1 AND a.matchmode = 1
AND (b.deledted IS NULL OR b.deledted = 0)
#AND c.list_ID = 1 
AND c.status = 1;

EXPLAIN
SELECT	a.* ,c.*
FROM match_schedule AS a
LEFT JOIN match_player AS b ON b.matlis_id = a.matlis_id  
LEFT JOIN signup_list AS c ON c.open_id = b.open_id
WHERE a.champ_id = 86 AND a.status = 1
AND c.status = 1




EXPLAIN
SELECT a.grouping,COUNT(*) AS cou
FROM match_schedule AS a
LEFT JOIN match_player AS b ON b.matlis_id = a.matlis_id  
LEFT JOIN signup_list AS c ON c.open_id = b.open_id
WHERE a.champ_id = 86 AND a.status = 1
AND c.status = 1
GROUP BY a.grouping





EXPLAIN
SELECT	* 
FROM match_schedule AS a
LEFT JOIN match_player AS b ON b.matlis_id = a.matlis_id  
LEFT JOIN signup_list AS c ON c.open_id = b.open_id
WHERE a.champ_id = 86 AND a.status = 1 
AND c.status = 1



EXPLAIN
SELECT a.*,COUNT(b.pla_id) AS cou , IFNULL(CONVERT(((LENGTH(a.machine_list) - LENGTH(REPLACE(a.machine_list,',','')))/ LENGTH(',')),SIGNED) +1 ,0) AS times

FROM match_schedule AS a

LEFT JOIN match_player AS b ON b.matlis_id = a.matlis_id 

LEFT JOIN signup_list AS c ON (c.open_id = b.open_id AND c.status = 1)

WHERE a.champ_id = 86 AND a.status = 1 

GROUP BY a.matlis_id

EXPLAIN
SELECT b.* 
FROM match_schedule AS a
LEFT JOIN match_player AS b ON b.matlis_id = a.matlis_id  
LEFT JOIN signup_list AS c ON c.open_id = b.open_id
WHERE a.champ_id = 86 AND a.status = 1 AND c.open_id = "okVO-v6H_EgRvjA92kJJn3uafLDE"
AND c.status = 1


SELECT * FROM match_schedule WHERE champ_id = 86 AND `status` = 1 ORDER BY grouping ASC

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

/*----------------------------抓取正在比賽的賽事------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
EXPLAIN
SELECT * FROM championship 
WHERE champ_type = 1 AND maitm = 1 AND maitm_id = 1 AND end_datetime <= "2019-09-22 16:12:00" ORDER BY end_datetime DESC LIMIT 1 ;

/*----------------------------抓取正在比賽的賽事的組別，紀錄中的組別與下一組--------------------------------------------------------------------------------------------------------------------------------------------*/
SELECT * FROM match_schedule WHERE champ_id = 94 AND grouping >= 'C' AND `status` = 1 ORDER BY grouping ASC LIMIT 2;

/*----------------------------抓取第幾輪抽籤的資料------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
SELECT * FROM match_player_wheel WHERE matlis_id = :matlis_id AND wheel = :wheel

/*----------------------------刊版左邊顯示---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

EXPLAIN
SELECT a.*,b.wec_id,b.score
FROM match_player_wheel AS a 
LEFT JOIN sbc_score AS b ON (a.matlis_id = 1 AND a.wheel = 1 AND a.status = 1 AND b.datetime > a.dt_update AND b.open_id = a.open_id AND b.MC_SerialNO = a.MC_SerialNO AND b.mach_id = a.mach_id AND b.champion = 1) 
WHERE
a.matlis_id = 1 AND a.wheel = 1 AND
(a.status = 2
 OR(
 a.status = 1 AND (( 
	 b.wec_id = (  
			SELECT wec_id FROM sbc_score WHERE `datetime` >= a.dt_update AND
			open_id = a.open_id AND MC_SerialNO = a.MC_SerialNO AND mach_id = a.mach_id AND champion = 1
			ORDER BY wec_id ASC LIMIT 1 OFFSET 1 
		    )
    ) OR b.wec_id IS NULL)
 )				          
)

/*
EXPLAIN
SELECT a.*,b.wec_id,b.score FROM match_player_wheel AS a 
LEFT JOIN sbc_score AS b ON (a.matlis_id = 13 AND a.wheel = 1 
        AND a.status = 1 AND b.datetime > a.dt_update AND b.open_id = a.open_id 
        AND b.MC_SerialNO = a.MC_SerialNO AND b.mach_id = a.mach_id AND b.champion = 1)
WHERE a.matlis_id = 13 AND a.wheel = 1 AND 
        (a.status = 2 OR(a.status = 1 AND ((b.wec_id = (  
            SELECT wec_id FROM sbc_score WHERE `datetime` >= a.dt_update AND open_id = a.open_id
            AND MC_SerialNO = a.MC_SerialNO AND mach_id = a.mach_id AND champion = 1
            ORDER BY wec_id ASC LIMIT 1 OFFSET 1
)) OR b.wec_id IS NULL
)));
*/

/*分數來源
*
SELECT * FROM sbc_score WHERE DATETIME >= "2019-08-06 00:00:00" AND
open_id = "okVO-v-4RYdCEAqtrUqVJ8kqQpSM" AND MC_SerialNO = "123456" AND mach_id = "0A" AND champion = 1
ORDER BY wec_id ASC LIMIT 1 OFFSET 1 
*/
/*----------------------------刊版左邊完整顯示---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

SELECT a.*,b.wec_id,b.score,c.name,d.grouping,e.jerseyNumber,f.MCS_ID
FROM match_player_wheel AS a 
LEFT JOIN sbc_score AS b ON (a.matlis_id = 13 AND a.wheel = 1 
AND a.status = 1 AND b.datetime > a.dt_update AND b.open_id = a.open_id 
AND b.MC_SerialNO = a.MC_SerialNO AND b.mach_id = a.mach_id AND b.champion = 1) 
LEFT JOIN player AS c ON (c.player_ID = a.player_ID)
LEFT JOIN match_schedule AS d ON (d.matlis_id = a.matlis_id AND d.status = 1)
LEFT JOIN match_jerseyNumber AS e ON (e.maitm_id = 1 AND e.open_id = a.open_id AND e.groupMode = 1)
LEFT JOIN Machines_status AS f ON (f.MC_SerialNO = a.MC_SerialNO AND f.mach_id = a.mach_id AND f.champion = 1)
WHERE a.matlis_id = 13 AND a.wheel = 1 AND 
    (a.status = 2 OR(a.status = 1 AND ((b.wec_id = (  
	SELECT wec_id FROM sbc_score WHERE `datetime` >= a.dt_update AND open_id = a.open_id
	AND MC_SerialNO = a.MC_SerialNO AND mach_id = a.mach_id AND champion = 1
	ORDER BY wec_id ASC LIMIT 1 OFFSET 1
	)) OR b.wec_id IS NULL
     )))
ORDER BY f.MCS_ID ASC;
                                                    
/*-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

/*----------------------------刊版右邊顯示---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
SELECT * 
FROM championship AS a 
LEFT JOIN player AS b ON (b.champ_id = 28 AND (b.deledted IS NULL OR b.deledted = 0))
LEFT JOIN Member AS c ON (c.Mem_ID = b.Mem_ID)
RIGHT JOIN sbc_score AS d ON (
(CASE WHEN a.end_datetime IS NULL 
THEN d.datetime > a.begin_datetime 
ELSE d.datetime > a.begin_datetime AND d.datetime < a.end_datetime
END) AND d.open_id = c.open_id AND d.MC_SerialNO = a.MC_SerialNO AND d.champion = 1 AND d.ST_ID = a.ST_ID)
WHERE a.champ_id = 28
/*-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

/*----------------------------刊版右邊顯示---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

EXPLAIN
SELECT b.grouping,d.jerseyNumber,e.name
#,f.wec_id,f.score
#,(sum(f.score)/count(*)) as cou
,ROUND((SUM(f.score)/COUNT(f.wec_id)),2) AS average
FROM championship AS a 
LEFT JOIN match_schedule AS b ON (b.champ_id = 94 AND b.status = 1)
LEFT JOIN match_player AS c ON (c.matlis_id = b.matlis_id)
LEFT JOIN match_jerseyNumber AS d ON (d.maitm_id = a.maitm_id AND d.open_id = c.open_id AND d.groupMode = a.groupMode)
LEFT JOIN player AS e ON (e.player_ID = c.player_ID)
RIGHT JOIN sbc_score AS f ON (
(CASE WHEN a.end_datetime IS NULL 
THEN f.datetime > a.begin_datetime 
ELSE f.datetime > a.begin_datetime AND f.datetime < a.end_datetime
END) AND f.open_id = c.open_id AND f.MC_SerialNO = a.MC_SerialNO AND f.champion = 1 AND f.ST_ID = a.ST_ID)
WHERE a.champ_id = 94
GROUP BY c.open_id 
ORDER BY average DESC;

/*----------------------------刊版右邊顯示計算數量-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

SELECT COUNT(*) AS cou
FROM championship AS a 
LEFT JOIN match_schedule AS b ON (b.champ_id = 94 AND b.status = 1)
LEFT JOIN match_player AS c ON (c.matlis_id = b.matlis_id)
LEFT JOIN match_jerseyNumber AS d ON (d.maitm_id = a.maitm_id AND d.open_id = c.open_id AND d.groupMode = a.groupMode)
LEFT JOIN player AS e ON (e.player_ID = c.player_ID)
RIGHT JOIN sbc_score AS f ON (
(CASE WHEN a.end_datetime IS NULL 
THEN f.datetime > a.begin_datetime 
ELSE f.datetime > a.begin_datetime AND f.datetime < a.end_datetime
END) AND f.open_id = c.open_id AND f.MC_SerialNO = a.MC_SerialNO AND f.champion = 1 AND f.ST_ID = a.ST_ID)
WHERE a.champ_id = 94
GROUP BY c.open_id ;

/*-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

/*----------------------------刊版右邊完整顯示(增加排名版)(同分排名會相同)-------------------------------------------------------------------------------------------------------------------------------------------------------*/
SELECT rank,grouping,jerseyNumber,`name`,average
FROM (

	SELECT p.grouping,p.jerseyNumber,p.name,p.average,
	@curRank := IF(@prevRank = p.average, @curRank, @incRank) AS rank, 
	@incRank := @incRank + 1, 
	@prevRank := p.average
	FROM(
		SELECT b.grouping,d.jerseyNumber,e.name
		,ROUND((SUM(f.score)/COUNT(f.wec_id)),2) AS average
		FROM championship AS a 
		LEFT JOIN match_schedule AS b ON (b.champ_id = 94 AND b.status = 1)
		LEFT JOIN match_player AS c ON (c.matlis_id = b.matlis_id)
		LEFT JOIN match_jerseyNumber AS d ON (d.maitm_id = a.maitm_id AND d.open_id = c.open_id AND d.groupMode = a.groupMode)
		LEFT JOIN player AS e ON (e.player_ID = c.player_ID)
		RIGHT JOIN sbc_score AS f ON (
		(CASE WHEN a.end_datetime IS NULL 
		THEN f.datetime > a.begin_datetime 
		ELSE f.datetime > a.begin_datetime AND f.datetime < a.end_datetime
		END) AND f.open_id = c.open_id AND f.MC_SerialNO = a.MC_SerialNO AND f.champion = 1 AND f.ST_ID = a.ST_ID)
		WHERE a.champ_id = 94
		GROUP BY c.open_id 
		ORDER BY average DESC
	) AS p ,(SELECT @curRank :=0, @prevRank := NULL, @incRank := 1) r 

) AS s

/*----------------------------刊版右邊完整顯示(增加排名版)(同分也會往下排)-------------------------------------------------------------------------------------------------------------------------------------------------------*/
SELECT rank,grouping,jerseyNumber,`name`,average
FROM (

	SELECT p.grouping,p.jerseyNumber,p.name,p.average,
	@curRank := IF(@prevRank = p.average, @curRank+1, @incRank) AS rank, 
	@incRank := @incRank + 1, 
	@prevRank := p.average
	FROM(
		SELECT b.grouping,d.jerseyNumber,e.name
		,ROUND((SUM(f.score)/COUNT(f.wec_id)),2) AS average
		FROM championship AS a 
		LEFT JOIN match_schedule AS b ON (b.champ_id = 94 AND b.status = 1)
		LEFT JOIN match_player AS c ON (c.matlis_id = b.matlis_id)
		LEFT JOIN match_jerseyNumber AS d ON (d.maitm_id = a.maitm_id AND d.open_id = c.open_id AND d.groupMode = a.groupMode)
		LEFT JOIN player AS e ON (e.player_ID = c.player_ID)
		RIGHT JOIN sbc_score AS f ON (
		(CASE WHEN a.end_datetime IS NULL 
		THEN f.datetime > a.begin_datetime 
		ELSE f.datetime > a.begin_datetime AND f.datetime < a.end_datetime
		END) AND f.open_id = c.open_id AND f.MC_SerialNO = a.MC_SerialNO AND f.champion = 1 AND f.ST_ID = a.ST_ID)
		WHERE a.champ_id = 94
		GROUP BY c.open_id 
		ORDER BY average DESC
	) AS p ,(SELECT @curRank :=0, @prevRank := NULL, @incRank := 1) r 

) AS s

/*----------------------------刊版右邊完整顯示(排名版)(計算數量)-------------------------------------------------------------------------------------------------------------------------------------------------------*/

SELECT rank,grouping,jerseyNumber,`name`,average,COUNT(*) AS cou
FROM (

	SELECT p.grouping,p.jerseyNumber,p.name,p.average,
	@curRank := IF(@prevRank = p.average, @curRank, @incRank) AS rank, 
	@incRank := @incRank + 1, 
	@prevRank := p.average
	FROM(
		SELECT b.grouping,d.jerseyNumber,e.name
		,ROUND((SUM(f.score)/COUNT(f.wec_id)),2) AS average
		FROM championship AS a 
		LEFT JOIN match_schedule AS b ON (b.champ_id = 94 AND b.status = 1)
		LEFT JOIN match_player AS c ON (c.matlis_id = b.matlis_id)
		LEFT JOIN match_jerseyNumber AS d ON (d.maitm_id = a.maitm_id AND d.open_id = c.open_id AND d.groupMode = a.groupMode)
		LEFT JOIN player AS e ON (e.player_ID = c.player_ID)
		RIGHT JOIN sbc_score AS f ON (
		(CASE WHEN a.end_datetime IS NULL 
		THEN f.datetime > a.begin_datetime 
		ELSE f.datetime > a.begin_datetime AND f.datetime < a.end_datetime
		END) AND f.open_id = c.open_id AND f.MC_SerialNO = a.MC_SerialNO AND f.champion = 1 AND f.ST_ID = a.ST_ID)
		WHERE a.champ_id = 94
		GROUP BY c.open_id 
		ORDER BY average DESC
	) AS p ,(SELECT @curRank :=0, @prevRank := NULL, @incRank := 1) r 

) AS s

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

/*-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

        ---------------------------------
        |在MySQL中实现Rank高级排名函数  |
        ---------------------------------
        
MySQL中没有Rank排名函数，当我们需要查询排名时，只能使用MySQL数据库中的基本查询语句来查询普通排名。尽管如此，可不要小瞧基础而简单的查询语句，我们可以利用其来达到Rank函数一样的高级排名效果。

在这里我用一个简单例子来实现排名的查询：

首先我们先创建一个我们需要进行高级排名查询的players表，

CREATE TABLE `players` (
  `pid` int(2) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(2) NOT NULL,
  PRIMARY KEY (`pid`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

INSERT INTO `players` (`pid`, `name`, `age`) VALUES
(1, 'Samual', 25),
(2, 'Vino', 20),
(3, 'John', 20),
(4, 'Andy', 22),
(5, 'Brian', 21),
(6, 'Dew', 24),
(7, 'Kris', 25),
(8, 'William', 26),
(9, 'George', 23),
(10, 'Peter', 19),
(11, 'Tom', 20),
(12, 'Andre', 20);

1、在MySQL中实现Rank普通排名函数
在这里，我们希望获得一个排名字段的列，以及age的升序排列。所以我们的查询语句将是：

SELECT pid, name, age, @curRank := @curRank + 1 AS rank
FROM players p, (
SELECT @curRank := 0
) q
ORDER BY age

/----------------------------------------------------------------------
|MYSQL 查詢結果              |
|----------------------------|
| PID |    NAME | AGE | RANK |
|-----|---------|-----|------|
|  10 |   Peter |  19 |    1 |
|  12 |   Andre |  20 |    2 |
|   2 |    Vino |  20 |    3 |
|   3 |    John |  20 |    4 |
|  11 |     Tom |  20 |    5 |
|   5 |   Brian |  21 |    6 |
|   4 |    Andy |  22 |    7 |
|   9 |  George |  23 |    8 |
|   6 |     Dew |  24 |    9 |
|   7 |    Kris |  25 |   10 |
|   1 |  Samual |  25 |   11 |
|   8 | William |  26 |   12 |
----------------------------------------------------------------------/

要在mysql中声明一个变量，你必须在变量名之前使用@符号。FROM子句中的(@curRank := 0)部分允许我们进行变量初始化，而不需要单独的SET命令。当然，也可以使用SET，但它会处理两个查询：

SET @curRank := 0;
SELECT pid, name, age, @curRank := @curRank + 1 AS rank
FROM players
ORDER BY age
2、查询以降序排列
首要按age的降序排列，其次按name进行排列，只需修改查询语句加上ORDER BY和 DESC以及列名即可。

SELECT pid, name, age, @curRank := @curRank + 1 AS rank
FROM players p, (
SELECT @curRank := 0
) q
ORDER BY age DESC, name

/----------------------------------------------------------------------
|MYSQL 查詢結果              |
|----------------------------|
| PID |    NAME | AGE | RANK |
|-----|---------|-----|------|
|   8 | William |  26 |    1 |
|   7 |    Kris |  25 |    2 |
|   1 |  Samual |  25 |    3 |
|   6 |     Dew |  24 |    4 |
|   9 |  George |  23 |    5 |
|   4 |    Andy |  22 |    6 |
|   5 |   Brian |  21 |    7 |
|  12 |   Andre |  20 |    8 |
|   3 |    John |  20 |    9 |
|  11 |     Tom |  20 |   10 |
|   2 |    Vino |  20 |   11 |
|  10 |   Peter |  19 |   12 |
----------------------------------------------------------------------/

3、在MySQL中实现Rank普通并列排名函数
现在，如果我们希望为并列数据的行赋予相同的排名，则意味着那些在排名比较列中具有相同值的行应在MySQL中计算排名时保持相同的排名(例如在我们的例子中的age)。为此，我们使用了一个额外的变量。

SELECT pid, name, age, 
CASE 
WHEN @prevRank = age THEN @curRank 
WHEN @prevRank := age THEN @curRank := @curRank + 1
END AS rank
FROM players p, 
(SELECT @curRank :=0, @prevRank := NULL) r
ORDER BY age

/----------------------------------------------------------------------
|MYSQL 查詢結果              |
|----------------------------|
| PID |    NAME | AGE | RANK |
|-----|---------|-----|------|
|  10 |   Peter |  19 |    1 |
|  12 |   Andre |  20 |    2 |
|   2 |    Vino |  20 |    2 |
|   3 |    John |  20 |    2 |
|  11 |     Tom |  20 |    2 |
|   5 |   Brian |  21 |    3 |
|   4 |    Andy |  22 |    4 |
|   9 |  George |  23 |    5 |
|   6 |     Dew |  24 |    6 |
|   7 |    Kris |  25 |    7 |
|   1 |  Samual |  25 |    7 |
|   8 | William |  26 |    8 |
----------------------------------------------------------------------/

如上所示，具有相同数据和排行的两行或多行，它们都会获得相同的排名。玩家Andre, Vino, John 和Tom都有相同的age，所以他们排名并列第二。下一个最高age的玩家(Brian)排名第3。这个查询相当于MSSQL和ORACLE 中的DENSE_RANK()函数。

4、在MySQL中实现Rank高级并列排名函数
当使用RANK()函数时，如果两个或以上的行排名并列，则相同的行都会有相同的排名，但是实际排名中存在有关系的差距。

SELECT pid, name, age, rank FROM
(SELECT pid, name, age,
@curRank := IF(@prevRank = age, @curRank, @incRank) AS rank, 
@incRank := @incRank + 1, 
@prevRank := age
FROM players p, (
SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
) r 
ORDER BY age) s

这是一个查询中的子查询。我们使用三个变量(@incRank，@prevRank，@curRank)来计算关系的情况下，在查询结果中我们已经补全了因为并列而导致的排名空位。我们已经封闭子查询到查询。这个查询相当于MSSQL和ORACLE中的RANK()函数。

/----------------------------------------------------------------------
|MYSQL 查詢結果              |
|----------------------------|
| PID |    NAME | AGE | RANK |
|-----|---------|-----|------|
|  10 |   Peter |  19 |    1 |
|  12 |   Andre |  20 |    2 |
|   2 |    Vino |  20 |    2 |
|   3 |    John |  20 |    2 |
|  11 |     Tom |  20 |    2 |
|   5 |   Brian |  21 |    6 |
|   4 |    Andy |  22 |    7 |
|   9 |  George |  23 |    8 |
|   6 |     Dew |  24 |    9 |
|   7 |    Kris |  25 |   10 |
|   1 |  Samual |  25 |   10 |
|   8 | William |  26 |   12 |
----------------------------------------------------------------------/


在这里我们可以看到，Andre，Vino，John和Tom都有相同的age，所以他们排名并列第二。下一个最高年龄的球员(Brian)排名第6，而不是第3，因为有4个人并列排名在第2。

好的，我希望在这些例子后，能让你了解RANK()和DENSE_RANK()之间的区别，并且知道在哪里应使用哪个查询来获取MySQL中的rank函数。谢谢。


---------------------------------------------------------------------------------------------------------------------------------------------------------------

延伸: 相同的分數下排名也將持續算下去。

    SELECT pid, NAME, age, rank FROM
    (SELECT pid, NAME, age,
    @curRank := IF(@prevRank = age, @curRank+1, @incRank) AS rank, 
    @incRank := @incRank + 1, 
    @prevRank := age
    FROM players p, (
    SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
    ) r 
    ORDER BY age) s

/----------------------------------------------------------------------
|MYSQL 查詢結果              |
|----------------------------|
| PID |    NAME | AGE | RANK |
|-----|---------|-----|------|
|  10 |   Peter |  19 |    1 |
|  12 |   Andre |  20 |    2 |
|   2 |    Vino |  20 |    3 |
|   3 |    John |  20 |    4 |
|  11 |     Tom |  20 |    5 |
|   5 |   Brian |  21 |    6 |
|   4 |    Andy |  22 |    7 |
|   9 |  George |  23 |    8 |
|   6 |     Dew |  24 |    9 |
|   7 |    Kris |  25 |   10 |
|   1 |  Samual |  25 |   10 |
|   8 | William |  26 |   12 |
----------------------------------------------------------------------/

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

/*----------------------------大中華盃大螢幕看板{獨立顯示晉級的排名API}(顯示的順序男子->女子->混雙)--------------------------------------------------------------------------------------------------------*/

/*--------目前男子組最後結束的賽事---------------------------------------------------------------------------------------------------*/
SELECT * FROM championship
WHERE champ_type = 1 AND maitm = 1 AND maitm_id = 1 
AND NOW() >= end_datetime AND matchmode = 1
ORDER BY champ_id DESC LIMIT 1

/*--------目前女子組最後結束的賽事---------------------------------------------------------------------------------------------------*/

SELECT * FROM championship
WHERE champ_type = 1 AND maitm = 1 AND maitm_id = 1 
AND NOW() >= end_datetime AND matchmode = 2
ORDER BY champ_id DESC LIMIT 1

/*--------目前混雙組最後結束的賽事----------------------------------------------------------------------------------------------------*/

SELECT * FROM championship
WHERE champ_type = 1 AND maitm = 1 AND maitm_id = 1 
AND NOW() >= end_datetime AND matchmode = 3
ORDER BY champ_id DESC LIMIT 1


/*----------------------------晉級的排名-----------------------------------------------------------------------------------------------*/
/*備註 : -Advanced DESC 排名小的要在前面(此方法可以解決NULL 在前面的問題)*/

SELECT rank,grouping,jerseyNumber,`name`,average
                FROM (
                    SELECT p.grouping,p.jerseyNumber,p.name,p.average,
                    @curRank := IF(@prevRank = p.average, @curRank+1, @incRank) AS rank, 
                    @incRank := @incRank + 1, 
                    @prevRank := p.average
                    FROM(
                        SELECT b.grouping,d.jerseyNumber,e.name
                        ,ROUND((SUM(f.score)/COUNT(f.wec_id)),2) AS average
                        FROM championship AS a 
                        LEFT JOIN match_schedule AS b ON (b.champ_id = 95 AND b.status = 1)
                        LEFT JOIN match_player AS c ON (c.matlis_id = b.matlis_id)
                        LEFT JOIN match_jerseyNumber AS d ON (d.maitm_id = a.maitm_id AND d.open_id = c.open_id AND d.groupMode = a.groupMode)
                        LEFT JOIN player AS e ON (e.player_ID = c.player_ID)
                        RIGHT JOIN sbc_score AS f ON (
                        (CASE WHEN a.end_datetime IS NULL 
                        THEN f.datetime > a.begin_datetime 
                        ELSE f.datetime > a.begin_datetime AND f.datetime < a.end_datetime
                        END) AND f.open_id = c.open_id AND f.MC_SerialNO = a.MC_SerialNO AND f.champion = 1 AND f.ST_ID = a.ST_ID)
                        WHERE a.champ_id = 95 AND e.Advanced >= 1
                        GROUP BY c.open_id 
                        ORDER BY average DESC,Advanced ASC
                    ) AS p ,(SELECT @curRank :=0, @prevRank := NULL, @incRank := 1) r 
                ) AS s ;

/*----------------------------晉級的排名(輔助查看使用)-----------------------------------------------------------------------------------------------*/

SELECT * FROM player WHERE champ_id = 95

UPDATE player SET Advanced = NULL WHERE champ_id = 95

SELECT rank,grouping,jerseyNumber,`name`,average
,COU,Advanced
                FROM (
                    SELECT p.grouping,p.jerseyNumber,p.name,p.average,
                    @curRank := IF(@prevRank = p.average, @curRank+1, @incRank) AS rank, 
                    @incRank := @incRank + 1, 
                    @prevRank := p.average
                    ,p.COU,p.Advanced
                    FROM(
                        SELECT b.grouping,d.jerseyNumber,e.name
                        ,ROUND((SUM(f.score)/COUNT(f.wec_id)),2) AS average
                        ,SUM(f.score) AS COU,e.Advanced
                        FROM championship AS a 
                        LEFT JOIN match_schedule AS b ON (b.champ_id = 95 AND b.status = 1)
                        LEFT JOIN match_player AS c ON (c.matlis_id = b.matlis_id)
                        LEFT JOIN match_jerseyNumber AS d ON (d.maitm_id = a.maitm_id AND d.open_id = c.open_id AND d.groupMode = a.groupMode)
                        LEFT JOIN player AS e ON (e.player_ID = c.player_ID)
                        RIGHT JOIN sbc_score AS f ON (
                        (CASE WHEN a.end_datetime IS NULL 
                        THEN f.datetime > a.begin_datetime 
                        ELSE f.datetime > a.begin_datetime AND f.datetime < a.end_datetime
                        END) AND f.open_id = c.open_id AND f.MC_SerialNO = a.MC_SerialNO AND f.champion = 1 AND f.ST_ID = a.ST_ID)
                        WHERE a.champ_id = 95 AND e.Advanced >= 1
                        GROUP BY c.open_id 
                        ORDER BY average DESC,Advanced ASC
                    ) AS p ,(SELECT @curRank :=0, @prevRank := NULL, @incRank := 1) r 
                ) AS s ;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

#-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#現場控制

SELECT * FROM match_schedule WHERE champ_id = 94 AND `status` = 1 ORDER BY grouping ASC

SELECT * FROM match_player WHERE matlis_id = 13 ORDER BY pla_id ASC

EXPLAIN
SELECT a.*,b.*,c.*
FROM match_schedule AS a
LEFT JOIN match_player AS b ON b.matlis_id = a.matlis_id
LEFT JOIN match_player_wheel AS c ON c.pla_id = b.pla_id 
WHERE a.champ_id = 95 AND a.status = 1 AND c.wheel = 1
ORDER BY a.grouping ASC ,b.pla_id ASC


EXPLAIN
SELECT a.champ_id,a.grouping,b.*,MAX(b.wheel) AS maxwheel
FROM match_schedule AS a
LEFT JOIN match_player_wheel AS b ON b.matlis_id = a.matlis_id
WHERE a.champ_id = 97 AND a.status = 1
GROUP BY a.matlis_id
ORDER BY a.grouping ASC;


SELECT * FROM match_schedule WHERE champ_id = 97 AND `status` =1

EXPLAIN
SELECT a.champ_id,a.grouping,b.*,c.name
FROM match_schedule AS a
LEFT JOIN match_player AS b ON(b.matlis_id = a.matlis_id)
LEFT JOIN player AS c ON (c.player_ID = b.player_ID)
WHERE a.champ_id = 95 AND a.status = 1
ORDER BY a.grouping ASC ,b.pla_id ASC


SELECT a.matlis_id,a.champ_id,a.grouping,b.pla_id,c.*
FROM match_schedule AS a
LEFT JOIN match_player AS b ON b.matlis_id = a.matlis_id
LEFT JOIN match_player_wheel AS c ON c.pla_id = b.pla_id 
WHERE a.champ_id = 95 AND a.status = 1 AND c.wheel = 1
ORDER BY a.grouping ASC ,b.pla_id ASC


EXPLAIN
SELECT * FROM match_player_wheel WHERE matlis_id = 13 AND wheel = 1





SELECT * FROM championship
                WHERE champ_type = 1 AND maitm = 1 AND maitm_id = 1 
                AND NOW() >= begin_datetime
                AND end_datetime IS NULL;

#-----------------------------------------------------------------------------------------------
UPDATE USB_Card SET LastUpdate = NOW() WHERE USB_ID = 247; 
UPDATE Machines_status SET update_datetime = NOW() WHERE MC_SerialNO = '123456' #AND mach_id = '0A';

SELECT * FROM Machines AS a INNER JOIN USB_Card AS b ON b.MC_SerialNO = a.MC_SerialNO WHERE a.MC_SerialNO = '123456' AND a.is_active = 1 AND b.LastUpdate >= DATE_ADD(NOW(),INTERVAL -7 MINUTE)

SELECT * FROM Machines_status WHERE MC_SerialNO = '123456' #AND mach_id = '0A' 
AND update_datetime >= DATE_ADD(NOW(),INTERVAL -7 MINUTE)

UPDATE Machines_status SET open_id = NULL ,mach_status = "waiting",lastscanQRCode = NULL WHERE MC_SerialNO = '123456' #AND mach_id = '0A'
