视图名是view1 ,视图由"select * from table1 where field3 = 1"构建执行如下sql
select field1 ,SUM(field2) as field2
from view1
group by field1
order by field2 desc
limit 5得到的结果集合:
field1 field2
'2008-04-01', 0.871766875000
'2008-06-01', 0.842127828571
'2008-06-01', 0.842127800000
'2008-06-01', 0.819379785714
'2008-04-01', 0.811397999166好像"group by" 没有起作用直接执行子查询的时,得到正确结果
select view1.field1 ,SUM(view1.field2) as field2
from (select * from table1 where field3=1) view1
group by view1.field1
order by view1.field2 desc
limit 5mysql 5.0.22 有谁知道原因么,谢谢
select field1 ,SUM(field2) as field2
from view1
group by field1
order by field2 desc
limit 5得到的结果集合:
field1 field2
'2008-04-01', 0.871766875000
'2008-06-01', 0.842127828571
'2008-06-01', 0.842127800000
'2008-06-01', 0.819379785714
'2008-04-01', 0.811397999166好像"group by" 没有起作用直接执行子查询的时,得到正确结果
select view1.field1 ,SUM(view1.field2) as field2
from (select * from table1 where field3=1) view1
group by view1.field1
order by view1.field2 desc
limit 5mysql 5.0.22 有谁知道原因么,谢谢
解决方案 »
- 能用mysqldump备份远程数据库,且将备份库保存在远端机上?
- mysql char(1) 读取问题
- 关于时间类型转换的问题。。。
- 一道sql面试题,各位请帮忙看看
- 如何设置这个表结构?
- 在mysql存储过程中为什么不能这样写:update chain_order_number con set iNextId = con.next_id = co
- 在存储过程中动态创建游标
- 100分求一数据库设计
- 再次请教MYSQL数据库关联查询的问题,三级省市菜单显示
- 在MYSQL中,"SELECT * FROM student WHERE score=80 order by date d" 什么日期排序不生效呢???
- 一个比较复杂的sql表达式查询!请求帮助。!
- Linux 下使用MySQL问题
'2008-04-01', 8000000.00000000
'2008-05-01', 16500000.00000000
'2008-06-01', 7000000.00000000
'2008-04-01', -10000.00000000
'2008-04-01', 12000000.00000000
'2008-04-01', 12000000.00000000
'2008-06-01', 35000000.00000000
'2008-05-01', 20000000.00000000
'2008-06-01', 12000000.00000000
'2008-06-01', 16000000.00000000
'2008-04-01', 14000000.00000000
'2008-04-01', -140000.00000000
'2008-04-01', 16000000.00000000
'2008-06-01', 25000000.00000000
'2008-05-01', 4200000.00000000
'2008-06-01', 7500000.00000000
from view1
group by field1
order by field2 desc
limit 5得到的结果集合:
'2008-05-01', 4200000.00000000
'2008-06-01', 7000000.00000000
'2008-06-01', 7500000.00000000
'2008-04-01', 8000000.00000000
'2008-04-01', 23990000.00000000
field2 Decimal(21,8)视图的全部记录都贴出来了,没有全角字符
from view1
group by DATE_FORMAT(field1,'%Y%m%d')
order by field2 desc
limit 5 ;
使用group by DATE_FORMAT(field1,'%Y%m%d')
返回正确结果一换成group by field1就返回不正确结果
select DATE_FORMAT(field1,'%Y%m%d %H%i%s'),field1,field2
from view1
where DATE_FORMAT(field1,'%Y%m%d')='20080601'用上面SQL语句确认一下。
如果把field1上的索引删除掉的话,即使用group by field1也返回正确结果也就是说,删除field1上的索引,下面sql语句返回正确结果
select field1 ,SUM(field2) as field2
from view1
group by field1
order by field2 desc
limit 5
CREATE TABLE `mcolap_c001_data`.`table1` (
`field1` date default NULL,
`field2` decimal(21,8) default NULL,
`field3` varchar(4) default NULL,
KEY `Index_1` (`field1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;;INSERT INTO table1 VALUES ('2008-04-01',8000000,'1');
INSERT INTO table1 VALUES ('2008-05-01',16500000,'1');
INSERT INTO table1 VALUES ('2008-06-01',7000000,'1');
INSERT INTO table1 VALUES ('2008-04-01',-10000,'1');
INSERT INTO table1 VALUES ('2008-04-01',12000000,'1');
INSERT INTO table1 VALUES ('2008-04-01',12000000,'1');
INSERT INTO table1 VALUES ('2008-06-01',35000000,'1');
INSERT INTO table1 VALUES ('2008-05-01',20000000,'1');
INSERT INTO table1 VALUES ('2008-06-01',12000000,'1');
INSERT INTO table1 VALUES ('2008-06-01',16000000,'1');
INSERT INTO table1 VALUES ('2008-04-01',14000000,'1');
INSERT INTO table1 VALUES ('2008-04-01',-140000,'1');
INSERT INTO table1 VALUES ('2008-04-01',16000000,'1');
INSERT INTO table1 VALUES ('2008-06-01',25000000,'1');
INSERT INTO table1 VALUES ('2008-05-01',4200000,'1');
INSERT INTO table1 VALUES ('2008-06-01',7500000,'1');
CREATE OR REPLACE VIEW view1 AS
SELECT *
FROM table1
WHERE field3 = 1;
===============================================================
执行:SELECT field1,SUM(field2)
FROM view1
GROUP BY field1
ORDER BY field2
limit 5;
用你数据测试,结果
field1 SUM(field2)
2008-06-01 102500000.00000000
2008-04-01 61850000.00000000
2008-05-01 40700000.00000000
我看看5.1.X