<mysql> 特殊技巧

#添加新列序列號
#http://www.jb51.net/article/87351.htm

第一种方法：
select   (@i:=@i+1)   as   i,table_name.*   from   table_name,(select   @i:=0)   as   it 

第二种方法：
set @rownum=0;
select @rownum:=@rownum+1 as rownum, t.username from auth_user t limit 1,5;

＃應用在mysql添加一列，然後取各個區段的和

SELECT
    SUM(CASE WHEN rownum<=i1 THEN 1 ELSE 0 END)pay1,
    SUM(CASE WHEN rownum<=i1 THEN pay ELSE 0 END)sum1,
    SUM(CASE WHEN rownum>i1 AND rownum<=i2 THEN 1 ELSE 0 END)pay2,
    SUM(CASE WHEN rownum>i1 AND rownum<=i2 THEN pay ELSE 0 END)sum2,
    SUM(CASE WHEN rownum>i2 AND rownum<=i3 THEN 1 ELSE 0 END)pay3,
    SUM(CASE WHEN rownum>i2 AND rownum<=i3 THEN pay ELSE 0 END)sum3
FROM 


(SELECT 
    COUNT(DISTINCT uid)i3
    ,ROUND(COUNT(DISTINCT uid)*0.02)i1
    ,ROUND(COUNT(DISTINCT uid)*0.18) i2 
FROM `XXX`.`XXXX` 
WHERE gamecode = "XXXX") AS t1,


(SELECT @rownum:=@rownum+1 AS rownum ,uid,pay
    FROM 
        (SELECT  uid,SUM(usd)pay
         FROM `XXXX`.`XXXXX`
         WHERE gamecode = "XXXX"  
GROUP BY uid ORDER BY SUM(usd) DESC ) AS t2
,(SELECT @rownum:=0)AS i ) AS t3

#MySQL varchar转换为int  
http://www.xuebuyuan.com/2054666.html
  
1. 手动转化类型(直接+0)  
示例：select server_id from cardserver where game_id = 1 order by server_id+0 desclimit 10  
  
2. 使用MySQL函数CAST：  
示例：select server_id from cardserver where game_id = 1 order by CAST(server_id as SIGNED) desc limit 10;  
  
3. 使用MySQL函数CONVERT：  
示例：select server_id from cardserver where game_id = 1 order by CONVERT(server_id,SIGNED)desc limit 10;  
  
  
附注：  
CAST函数,CONVERT用法  
CAST(expr   AS   type),   CONVERT(expr,type)   ,   CONVERT(expr   USING   transcoding_name)    
CAST()   和CONVERT()   函数可用来获取一个类型的值，并产生另一个类型的值。    
这个类型   可以是以下值其中的   一个：      
BINARY[(N)]    
CHAR[(N)]    
DATE    
DATETIME    
DECIMAL    
SIGNED   [INTEGER]    
TIME    
UNSIGNED   [INTEGER]