应用场景,彩票的开奖表
CREATE TABLE `fz_lottery20_winning` (
`winning_id` varchar(255) NOT NULL COMMENT '期次id',
`type` varchar(255) DEFAULT NULL COMMENT '彩票类型',
`winning_num` varchar(255) DEFAULT NULL COMMENT '开奖号码',
`term` varchar(255) DEFAULT NULL COMMENT '期数',
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`winning_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='期次记录表';
一、普通的group by查询(查询每种彩票的一期开奖信息)
SELECT
*
FROM
fz_lottery20_winning
GROUP BY
type
结果是得到每种彩票的第一条数据(数据库ID最小的一条)
二、通过group by查询每组中期数最大的一期
SELECT
a.*FROM
(select * FROM fz_lottery20_winning ORDER BY term DESC) tt
GROUP BY
tt.type
其中select * FROM fz_lottery20_winning ORDER BY term DESC是将整个表先排序,再分组
三、group by和join一起用,原则是先通过group by生成一个临时表
SELECT
b.ftb_id,b.date,b.time,a.total_number
FROM
(SELECT ftb_id,COUNT(1) total_number from fz_lottery23_ftb_detail WHERE `status`="1" GROUP BY ftb_id)a
JOIN
fz_lottery22_ftb b
WHERE
a.ftb_id=b.ftb_id