SQL的问题？？？？？？
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 |
+----+------+------------------------------+------------------------------+
把ddd的值1.2改一下，则：
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 |
+----+------+------------------------------+------------------------------+
| 0 | ss | 002220 / 2003-08-04 01:02:02 | 000564 / 2003-08-04 13:12:00 |
| 1 | ddd | 0005.8 / 2003-08-04 02:00:00 | 000012 / 2003-08-04 10:40:30 |
| 2 | ww | 000023 / 2003-08-04 12:30:22 | 000023 / 2003-08-04 12:30:22 |
+----+------+------------------------------+------------------------------+
结果5.8>12????它怎么是按字符比较的啊？？？怎么解决？
问题点数：50、回复次数：19Top
1 楼shuixin13（犬犬（心帆））回复于 2003-08-04 15:29:12 得分 35
呵呵,
可能是你还没理解我给你的
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)
*/Top
2 楼dennis80（我爱亚薇）回复于 2003-08-04 15:39:06 得分 0
你说的我都明白，可是这样与实际的值不符啊？？该怎么解决呢？？Top
3 楼dennis80（我爱亚薇）回复于 2003-08-04 15:40:45 得分 0
不知你说: "把ddd的值1.2改一下 "
我是在做测试SQL语句时，改动的数据，没什么关系！Top
4 楼shuixin13（犬犬（心帆））回复于 2003-08-04 16:05:13 得分 0
呵呵,
我是说你改动成什么之后应变成上面的结果了??Top
5 楼dennis80（我爱亚薇）回复于 2003-08-04 17:21:53 得分 0
没改什么啊！我现在只是想让它能按照数字取最大、最小值，字符有问题啊！Top
6 楼shuixin13（犬犬（心帆））回复于 2003-08-04 17:56:39 得分 0
LPAD(CESHIZHI+0.00,6,'0'),
中的 6 需要按你的实际字段大小更改Top
7 楼lxf_1976（小木）回复于 2003-08-05 17:10:45 得分 0
对
LPAD(111+0.00, 6, '0') -> 111.00
LPAD(1111+0.00, 6, '0') -> 1111.0
LPAD(1111+0.00, 7, '0') -> 1111.00Top
8 楼lxf_1976（小木）回复于 2003-08-05 17:20:27 得分 15
另：
楼主的结果是怎么出来的？我不太明白
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'这步操作根本不起作用......
疑问中????Top
9 楼shuixin13（犬犬（心帆））回复于 2003-08-05 17:39:59 得分 0
唉,
我的疑惑就是这个,
可惜楼主一直没说清Top
10 楼dennis80（我爱亚薇）回复于 2003-08-05 19:59:34 得分 0
CESHIZHI是DOUBLE型的啊。这个问题我再看看，先谢谢各位！今天单位上不了网，所以没在线！明天去单位再试试！我是用MYSQL CC测试的，不知道可以不？？
Top
11 楼shuixin13（犬犬（心帆））回复于 2003-08-05 21:09:09 得分 0
呵呵,应该不会有什么问题,
如果不放心可以用 mysql.exe 测试Top
12 楼dennis80（我爱亚薇）回复于 2003-08-06 09:20:35 得分 0
SELECT ad,name,MAX(CONCAT(LPAD(CESHIZHI+0.00,10,'0'),'/',CESHISHIJIAN)) AS MAX
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 |
+-------+----+------+----------+---------------------+
这个问题我也很纳闷啊？？Top
13 楼dennis80（我爱亚薇）回复于 2003-08-06 09:22:40 得分 0
把CESHIZHI改成INT 就可以显示正确的结果！！！！Top
14 楼lxf_1976（小木）回复于 2003-08-06 09:44:37 得分 0
INT可以反映你的CESHIZHI数据吗？
建议你考虑一下float(10, 2)类型Top
15 楼dennis80（我爱亚薇）回复于 2003-08-06 10:01:32 得分 0
FLOAT 和DOUBLE 我都试过了啊！Top
16 楼dennis80（我爱亚薇）回复于 2003-08-06 10:06:30 得分 0
在MYSQL CC中怎么设FLOAT（10，1）啊？？Top
17 楼lxf_1976（小木）回复于 2003-08-06 10:16:44 得分 0
What's the 'MYSQL CC'?Top
18 楼dennis80（我爱亚薇）回复于 2003-08-06 10:40:05 得分 0
MYSQL Control Center 0.9.2-beta
不过现在问题解决了！
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 AD
CESHIZHI类型为DOUBLE（11，1）
谢谢小木，心帆！！Top
19 楼lxf_1976（小木）回复于 2003-08-06 11:10:59 得分 0
:)
恭喜Top
