建表
CREATE TABLE if not EXISTS `bb` (
`id` int not null primary key auto_increment,
`aab001` varchar(20) DEFAULT NULL COMMENT '参保组织编号',
`aab003` varchar(20) DEFAULT NULL COMMENT '组织机构代码',
`aae044` varchar(100) DEFAULT NULL COMMENT '单位名称',
`pre_rs` decimal(8,0) DEFAULT NULL COMMENT '应缴人数',
`cur_rs` decimal(8,0) DEFAULT NULL COMMENT '应缴人数',
`rs` decimal(9,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8; create table if not EXISTS aa as SELECT * from bb;
create table cc like aa;
表操作
# 改表名
rename table `aa` to `aaa`;
ALTER TABLE cc RENAME TO orbit
# 删表
drop table if exists tmp;
# 更新字段
UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = 'M'
# 删除行
DELETE FROM EMPLOYEE WHERE AGE > 20
# 插入数据
INSERT INTO cc SELECT * FROM orbit WHERE xttime > '2018-04-16 00:00:00' AND xttime <= '2018-04-17 00:00:00'; #分批插入
A、B两表，找出ID字段存在A表，但是不存在B表的数据。A表总共13w数据，去重后约3W条，B表有2W条，且B表的ID字段有索引
方法一：1.395秒
select distinct A.ID from A where A.ID not in (select ID from B)
方法二：0.739秒
select A.ID from A left join B on A.ID=B.ID where B.ID is null
方法三：0.570秒
select * from B where (select count(1) from A where A.ID = B.ID) = 0
长表转宽表
SELECT
userkey,
MAX(CASE tagid
WHEN 1 THEN valueID
ELSE NULL
END) AS tag1,
MAX(CASE tagid
WHEN 2 THEN valueID
ELSE NULL
END) AS tag2
FROM
userdata
WHERE
tagid IN (1 , 2)
GROUP BY userkey
分组排序
--方法一: 该方法目前会报错，暂不知如何解决
select
x,y,z
from
(
select
x,y,z
from
a
order by
y desc
limit
1000(大于总行数)
) t
group by
x --方法二：子查询
SELECT
machine, deny_flow, total_flow, time
FROM
total_freq_ctrl A
WHERE
1 > (SELECT COUNT(machine) FROM total_freq_ctrl WHERE machine = A.machine AND time > A.time)
AND A.module = 'all'
ORDER BY
A.time desc; --方法三
set @row=0;set @mid='';
select
a.*,b.rownum
from
total_freq_ctrl a
inner join
(
SELECT
module, machine, time,
case when @mid = machine then @row:=@row+1 else @row:=1 end rownum,
@mid:=machine mid
FROM
total_freq_ctrl
order by
module,machine,time desc
) b on b.module=a.module and b.machine=a.machine and b.time=a.time
where
b.rownum<5; --改为下面的也可以
SET @R = 0;
SET @mid = '';
SELECT
*
FROM
(
SELECT
test1.*,
( CASE WHEN @mid = a THEN @R := @R + 1 ELSE @R := 1 END ) AS rownum,
@mid := a midnum
FROM
test1
ORDER BY
a,b DESC
) t
WHERE
rownum < 2