问题:
一个表有三列,A,B,C 请用SQL语句实现如下功能:
如果A列大于B列,选择A列,否则选择B列;如果B列大于C列,选择B列,否则选择C列,用
SQL语句如何实现?
建表:
DROP TABLE IF EXISTS `comparetable`;
CREATE TABLE `comparetable` (
`Id` int(11) NOT NULL auto_increment,
`name` varchar(12) default NULL,
`moneys1` int(11) default NULL,
`moneys2` int(11) default NULL,
`moneys3` int(11) default NULL,
PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `comparetable` VALUES (1,'zhang',3000,5200,6300);
INSERT INTO `comparetable` VALUES (2,'wang',5000,5200,4000);
INSERT INTO `comparetable` VALUES (3,'li',4000,1600,2300);
Sql:
SELECT Id, name, (CASE WHEN moneys1 > moneys2 THEN moneys1 ELSE moneys2 END) AS moneysstart,
(CASE WHEN moneys2 > moneys3 THEN moneys2 ELSE moneys3 END) AS moneysend FROM comparetable;