mysql 备忘sql

CREATE TABLE table1(SELECT ecuserid,id,PartnerType FROM table2
WHERE PartnerType=4 AND createdate BETWEEN '2011-09-23' AND '2011-10-10')

 

UPDATE ecuser_cooperationuser AS a
LEFT JOIN ecuser_customer b ON a.KubaUserId = b.EcUserId
SET b.PartnerType = 2
WHERE a.Platform='彩贝'

 

SELECT COUNT(0) AS '会员数',CASE UserLevelId WHEN 1 THEN '蜗居会员' WHEN 2 THEN '一居会员' WHEN 3 THEN '二居会员'  WHEN 4 THEN '三居会员'
WHEN 5 THEN '四居会员' WHEN 6 THEN '复式楼会员' WHEN 7 THEN '别墅会员' END AS '会员级别'
  FROM ecuser_customer GROUP BY UserLevelId

CREATE TABLE temp_badip (SELECT ip FROM  (SELECT   用户名,MAX(登录时间) AS logintime,MAX(登录ip) AS ip,MAX(会员级别),COUNT(用户名)    FROM ecuser_loginuser_201110211430
GROUP BY 用户名) AS t  GROUP BY ip HAVING COUNT(*)>20)