视图名是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 有谁知道原因么,谢谢
'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