1.问题
有一个表,其中一列是城市(city),一列是人的姓名(personname),
怎么获取每个城市中任意两个人?
DDL如下:
-- ----------------------------
-- Table structure for `myperson`
-- ----------------------------
DROP TABLE IF EXISTS `myperson`;
CREATE TABLE `myperson` (
`id` int(11) NOT NULL auto_increment,
`city` varchar(12) default NULL,
`personname` varchar(24) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of myperson
-- ----------------------------
2.答案
SELECT
city,
personname
FROM
(
SELECT
CASE
WHEN @mycity != city THEN @rownum:= 1
ELSE @rownum:= @rownum + 1
END AS No,
@mycity := city AS city,
personname
FROM
(SELECT @rownum:=0) r,
(SELECT @mycity:= '') p,
(SELECT * FROM myperson order by city, rand()) tmp
) a
WHERE
No <= 2
ORDER BY
city;
3.答案分析
说明:从如下问题来分析上面的sql
1.变量如何命名?变量如何赋值?
2.case when用在select子句和用在where子句的区别是什么?