将字段值作为字段显示

CREATE TABLE IF NOT EXISTS `user` (
  `userid` int(10) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- 转存表中的数据 `user`
--

INSERT INTO `user` (`userid`, `username`, `password`) VALUES
(1, 'zhangsan', 'd10906c3dac1172d4f60bd41f224ae75'),
(2, 'lisi', 'd10906c3dac1172d4f60bd41f224ae75'),
(3, 'wangwu', 'd10906c3dac1172d4f60bd41f224ae75'),
(4, 'zhaoliu', 'd10906c3dac1172d4f60bd41f224ae75');

　　

CREATE TABLE IF NOT EXISTS `pay` (
  `payid` int(10) NOT NULL AUTO_INCREMENT,
  `userid` int(10) NOT NULL,
  `payment` int(10) NOT NULL,
  `paytime` date NOT NULL,
  PRIMARY KEY (`payid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- 转存表中的数据 `pay`
--

INSERT INTO `pay` (`payid`, `userid`, `payment`, `paytime`) VALUES
(1, 1, 100, '2001-10-12'),
(2, 1, 105, '2001-11-10'),
(3, 2, 50, '2001-11-28'),
(4, 3, 20, '2001-11-28'),
(5, 1, 10, '2012-11-28'),
(6, 3, 50, '2012-11-28');

　将字段值作为字段显示。　

select t1.userid,
sum(case `paytime` when '2001' then payment end) as '2001',
sum(case `paytime` when '2012' then payment end) as '2012'from
( select u.userid,year(p.paytime) paytime ,sum(p.payment) payment from `user` u inner join `pay` p on u.userid = p.userid 
group by u.userid,year(p.paytime)) t1 group by t1.userid

还有一个类似的结构：

select memberid,sum(member>10) as gt,sum(number<10) as lt from `order` group by `memberid`;

只是换了sum中的条件,用于统计行数。