呵呵,可能是你还没理解我给你的
Max(CONCAT(LPAD(CESHIZHI+0.00,6,'0'),' / ',CESHISHIJIAN))
真正的含义
我是思路是就是让数字以字符串方式比较,
这样才能达到你所要的目的
CESHIZHI+0.00
作用是将 CESHIZHI 字段中的整数从无小数点的 100 形式变为统一的有两位小数的形式LPAD(CESHIZHI+0.00,6,'0')
作用是将所有的数变换为一个等长的字符串,
这样就可以字符串形式比较两个数的大小了在这里,
'0005.8 /'
'000012 /'
两个字符那是一定第一个大了,所以会出现你上面的问题,不知你说: "把ddd的值1.2改一下 "
这一句是什么意思????不明白#
# Table structure for table 't1'
#CREATE TABLE t1 (
fenji int(11) NOT NULL default '0',
ad tinyint(4) default NULL,
name char(10) default NULL,
ceshizhi decimal(5,2) default NULL,
ceshishijian datetime default NULL
) TYPE=MyISAM;#
# Dumping data for table 't1'
#INSERT INTO t1 VALUES("1", "1", "ddd", "22.30", "2003-08-04 10:40:30");
INSERT INTO t1 VALUES("1", "0", "ss", "12.30", "2003-08-04 01:02:00");
INSERT INTO t1 VALUES("1", "2", "ww", "23.00", "2003-08-04 12:30:22");
INSERT INTO t1 VALUES("1", "3", "ddd", "110.00", "2003-08-04 02:00:00");#
# 测试语句
#SELECT ad, name,
MAX(CONCAT(LPAD(CESHIZHI+0.00,6,'0'),' / ',CESHISHIJIAN)) AS MAX,
MIN(CONCAT(LPAD(CESHIZHI+0.00,6,'0'),' / ',CESHISHIJIAN)) AS MIN
FROM t1
WHERE TO_DAYS(CESHISHIJIAN) = TO_DAYS(CURDATE())
GROUP BY name;
/*
测试结果mysql> SELECT ad, name,
-> MAX(CONCAT(LPAD(CESHIZHI+0.00,6,'0'),' / ',CESHISHIJIAN)) AS MAX, -> MIN(CONCAT(LPAD(CESHIZHI+0.00,6,'0'),' / ',CESHISHIJIAN)) AS MIN
-> FROM t1
-> WHERE TO_DAYS(CESHISHIJIAN) = TO_DAYS(CURDATE())
-> GROUP BY name;
+------+------+------------------------------+------------------------------+
| ad | name | MAX | MIN |
+------+------+------------------------------+------------------------------+
| 1 | ddd | 110.00 / 2003-08-04 02:00:00 | 022.30 / 2003-08-04 10:40:30 |
| 0 | ss | 012.30 / 2003-08-04 01:02:00 | 012.30 / 2003-08-04 01:02:00 |
| 2 | ww | 023.00 / 2003-08-04 12:30:22 | 023.00 / 2003-08-04 12:30:22 |
+------+------+------------------------------+------------------------------+
3 rows in set (0.01 sec)*/
Max(CONCAT(LPAD(CESHIZHI+0.00,6,'0'),' / ',CESHISHIJIAN))
真正的含义
我是思路是就是让数字以字符串方式比较,
这样才能达到你所要的目的
CESHIZHI+0.00
作用是将 CESHIZHI 字段中的整数从无小数点的 100 形式变为统一的有两位小数的形式LPAD(CESHIZHI+0.00,6,'0')
作用是将所有的数变换为一个等长的字符串,
这样就可以字符串形式比较两个数的大小了在这里,
'0005.8 /'
'000012 /'
两个字符那是一定第一个大了,所以会出现你上面的问题,不知你说: "把ddd的值1.2改一下 "
这一句是什么意思????不明白#
# Table structure for table 't1'
#CREATE TABLE t1 (
fenji int(11) NOT NULL default '0',
ad tinyint(4) default NULL,
name char(10) default NULL,
ceshizhi decimal(5,2) default NULL,
ceshishijian datetime default NULL
) TYPE=MyISAM;#
# Dumping data for table 't1'
#INSERT INTO t1 VALUES("1", "1", "ddd", "22.30", "2003-08-04 10:40:30");
INSERT INTO t1 VALUES("1", "0", "ss", "12.30", "2003-08-04 01:02:00");
INSERT INTO t1 VALUES("1", "2", "ww", "23.00", "2003-08-04 12:30:22");
INSERT INTO t1 VALUES("1", "3", "ddd", "110.00", "2003-08-04 02:00:00");#
# 测试语句
#SELECT ad, name,
MAX(CONCAT(LPAD(CESHIZHI+0.00,6,'0'),' / ',CESHISHIJIAN)) AS MAX,
MIN(CONCAT(LPAD(CESHIZHI+0.00,6,'0'),' / ',CESHISHIJIAN)) AS MIN
FROM t1
WHERE TO_DAYS(CESHISHIJIAN) = TO_DAYS(CURDATE())
GROUP BY name;
/*
测试结果mysql> SELECT ad, name,
-> MAX(CONCAT(LPAD(CESHIZHI+0.00,6,'0'),' / ',CESHISHIJIAN)) AS MAX, -> MIN(CONCAT(LPAD(CESHIZHI+0.00,6,'0'),' / ',CESHISHIJIAN)) AS MIN
-> FROM t1
-> WHERE TO_DAYS(CESHISHIJIAN) = TO_DAYS(CURDATE())
-> GROUP BY name;
+------+------+------------------------------+------------------------------+
| ad | name | MAX | MIN |
+------+------+------------------------------+------------------------------+
| 1 | ddd | 110.00 / 2003-08-04 02:00:00 | 022.30 / 2003-08-04 10:40:30 |
| 0 | ss | 012.30 / 2003-08-04 01:02:00 | 012.30 / 2003-08-04 01:02:00 |
| 2 | ww | 023.00 / 2003-08-04 12:30:22 | 023.00 / 2003-08-04 12:30:22 |
+------+------+------------------------------+------------------------------+
3 rows in set (0.01 sec)*/
LPAD(1111+0.00, 6, '0') -> 1111.0
LPAD(1111+0.00, 7, '0') -> 1111.00
楼主的结果是怎么出来的?我不太明白SELECT ad, name,
Max(CONCAT(LPAD(CESHIZHI+0.00,6,'0'),' / ',CESHISHIJIAN)) AS MAX,
Min(CONCAT(LPAD(CESHIZHI+0.00,6,'0'),' / ',CESHISHIJIAN)) AS MIN
FROM dianyuanping
WHERE TO_DAYS(CESHISHIJIAN) = TO_DAYS(CURDATE())
GROUP BY name ORDER BY AD+----+------+------------------------------+------------------------------+
| ad | name | MAX | MIN |
+----+------+------------------------------+------------------------------+
| 2 | ww | 000023 / 2003-08-04 12:30:22 | 000023 / 2003-08-04 12:30:22 |
| 0 | ss | 002220 / 2003-08-04 01:02:02 | 0001.2 / 2003-08-04 01:02:00 |
| 1 | ddd | 000110 / 2003-08-04 02:00:00 | 0001.8 / 2003-08-04 10:40:30 |
+----+------+------------------------------+------------------------------+如果CESHIZHI字段为数值类型,则MAX、MIN结果中,'000023'等数据应该显示为'023.00',如果CESHIZHI字段为字符串类型,则LPAD(CESHIZHI+0.00,6,'0')中'+0.00'这步操作根本不起作用......疑问中????
FROM dianyuanping WHERE TO_DAYS(CESHISHIJIAN)=TO_DAYS(CURDATE()) GROUP BY NAME+----+------+--------------------------------+
| ad | name | MAX |
+----+------+--------------------------------+
| 1 | ddd | 00000005.8/2003-08-06 20:00:00 |
| 0 | ss | 0000002220/2003-08-06 05:02:02 |
| 2 | ww | 0000000023/2003-08-06 12:30:22 |
+----+------+--------------------------------+数据库中的数据为:SELECT *
FROM `dianyuanping`+-------+----+------+----------+---------------------+
| fenji | ad | Name | CESHIZHI | CESHISHIJIAN |
+-------+----+------+----------+---------------------+
| 1 | 1 | ddd | 402 | 2003-08-06 03:40:30 |
| 1 | 0 | ss | 13.2 | 2003-08-06 01:02:00 |
| 1 | 2 | ww | 23 | 2003-08-06 12:30:22 |
| 1 | 3 | ddd | 5.8 | 2003-08-06 20:00:00 |
| 1 | 4 | ss | 564 | 2003-08-06 13:12:00 |
| 1 | 5 | ss | 2220 | 2003-08-06 05:02:02 |
+-------+----+------+----------+---------------------+
这个问题我也很纳闷啊??
SELECT ad,name,MAX(CONCAT(LPAD(CESHIZHI+0.0,8,'0'),'/',CESHISHIJIAN) ) ,MIN(CONCAT(LPAD(CESHIZHI+0.0,8,'0'),'/',CESHISHIJIAN)) ,
AVG(CESHIZHI)
FROM dianyuanping WHERE TO_DAYS(CESHISHIJIAN)=TO_DAYS(CURDATE()) GROUP BY NAME ORDER BY ADCESHIZHI类型为DOUBLE(11,1)
谢谢小木,心帆!!