<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)sum3FROM (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]