10.表结构如下:
oid owner num status
1 0002 100 1
1 0003 200 2
2 0002 400 3
2 0003 400 1
1 0002 500 1
1 0003 600 1
2 0002 700 3
希望显示的结果:
oid owner Sum_num status
1 0002 600 完成
1 0003 800 完成中
2 0002 1100 初始
2 0003 400 完成
sum_num为相同oid与owner列的num之和;
S_status:若全为 1 则是完成;
若全为 3 则是初始
其他情况 则是完成中
sql
oid owner num status
1 0002 100 1
1 0003 200 2
2 0002 400 3
2 0003 400 1
1 0002 500 1
1 0003 600 1
2 0002 700 3
希望显示的结果:
oid owner Sum_num status
1 0002 600 完成
1 0003 800 完成中
2 0002 1100 初始
2 0003 400 完成
sum_num为相同oid与owner列的num之和;
S_status:若全为 1 则是完成;
若全为 3 则是初始
其他情况 则是完成中
sql
,sum(num) from tt group by oid,owner,3;
,sum(num) from tb
group by oid.owner,3;
表和数据:CREATE TABLE `temp` (
`oid` INT(11) NOT NULL,
`owner` VARCHAR(50) DEFAULT NULL,
`num` FLOAT DEFAULT NULL,
`status` INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO `temp` (`oid`, `owner`, `num`, `status`) VALUES('1','0002','100','1');
INSERT INTO `temp` (`oid`, `owner`, `num`, `status`) VALUES('1','0003','200','2');
INSERT INTO `temp` (`oid`, `owner`, `num`, `status`) VALUES('2','0002','400','3');
INSERT INTO `temp` (`oid`, `owner`, `num`, `status`) VALUES('2','0003','400','1');
INSERT INTO `temp` (`oid`, `owner`, `num`, `status`) VALUES('1','0002','500','1');
INSERT INTO `temp` (`oid`, `owner`, `num`, `status`) VALUES('1','0003','600','1');
INSERT INTO `temp` (`oid`, `owner`, `num`, `status`) VALUES('2','0002','700','3');
查询语句:SELECT oid ,OWNER, SUM(num) AS Sum_num,
CASE
WHEN STATUS = 1 THEN '完成'
WHEN STATUS = 3 THEN '初始'
ELSE '完成中'
END STATUS
FROM temp GROUP BY oid,OWNER;
建议:不要使用mysql的关键字,要有主键,尽量列不要为null。排序字段加上索引。
`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`,`rows`, `Extra`)
'1','SIMPLE','temp','ALL',NULL,NULL,NULL,NULL,'7',
'Using temporary; Using filesort');