数据库MYSQL表如下:
表名:stockdata
ID TIME STOCK_NAME DATA1 DATA2 DATA3
1 9:30 银行 aaa bbb ccc
2 9:31 银行 ddd eee fff
.. ...... ... .... .... ....
11 9:30 证券 2aaa 2bbb 2ccc
12 9:31 证券 2ddd 2eee 2fff
.. .... .... .... .... ....
我想要的结果是:按STOCK_NAME这个字段个都取出最新的10条信息。每条记录包含在这个时间上的所有STOCK_NAME的三个数据都展示在一条记录上。请问怎么写。
银行 证券
TIME DATA1 DATA2 DATA3 DATA1 DATA2 DATA3
9:31 ddd eee fff 2ddd 2eee 2fff
9: 30 aaa bbb ccc 2aaa 2bbb 2ccc
... ... ... ... ... ... ...
查询出来的表就是按这样的以时间排序,一条记录上显示每个分类的相同的字段名(DATA1 ,DATA2,DATA3)的数据。
表名:stockdata
ID TIME STOCK_NAME DATA1 DATA2 DATA3
1 9:30 银行 aaa bbb ccc
2 9:31 银行 ddd eee fff
.. ...... ... .... .... ....
11 9:30 证券 2aaa 2bbb 2ccc
12 9:31 证券 2ddd 2eee 2fff
.. .... .... .... .... ....
我想要的结果是:按STOCK_NAME这个字段个都取出最新的10条信息。每条记录包含在这个时间上的所有STOCK_NAME的三个数据都展示在一条记录上。请问怎么写。
银行 证券
TIME DATA1 DATA2 DATA3 DATA1 DATA2 DATA3
9:31 ddd eee fff 2ddd 2eee 2fff
9: 30 aaa bbb ccc 2aaa 2bbb 2ccc
... ... ... ... ... ... ...
查询出来的表就是按这样的以时间排序,一条记录上显示每个分类的相同的字段名(DATA1 ,DATA2,DATA3)的数据。
解决方案 »
- 为什么我的PostgreSQL的SQL shell(psql)执行不了命令?
- 关于数据库的问题我想,取中间的数据该怎么取?
- MySql gb2312 load data infiles 时出现的ERROR 1366 (HY000)问题
- 插入数据
- 批量列出多记录首条记录
- MYSQL临时表能在两个存储过程中通用吗
- sql的STUFF函数和mysql的哪个函数一样,如果没有,可以写个不?
- 谁有恢复delete 数据的方案,TH’X
- ...where time1 >='2006-12-20 00:00:00' and time1 <='2006-12-20 24:00:00'查不到数据的问题?
- 小妹只有20分了,请教各位:我每次重新安装完MySql后,在管理工具--》服务下记录了MySql这个名称,不知怎样才能删除
- 问一个两个表联合更新的问题
- 如何修改mysql主键(id)的值为自增
最新的10条信息:什么标准
from tb
group by TIME
`id` int(11) unsigned NOT NULL auto_increment,
`stock_code` varchar(6) NOT NULL,
`real_time` time NOT NULL,
`buycount` float(11,2) NOT NULL,
`bigbuycount` float(11,2) NOT NULL,
`waitbuycount` float(11,2) NOT NULL,
PRIMARY KEY (`id`)
)
表数据如下:
+------+------------+-----------+-----------+-------------+--------------+
| id | stock_code | real_time | buycount | bigbuycount | waitbuycount |
+------+------------+-----------+-----------+-------------+--------------+
| 3906 | 881155 | 09:30:30 | 631571.00 | 386114.00 | 1193500.00 |
| 3907 | 881155 | 09:31:30 | 615313.00 | 740784.00 | 1368660.00 |
| 3908 | 881155 | 09:32:30 | 637119.00 | 825097.00 | 1368660.00 |
| 3909 | 881155 | 09:33:30 | 651109.00 | 1079200.00 | 1393390.00 |
| 3910 | 881155 | 09:34:30 | 646276.00 | 1249060.00 | 1846370.00 |
| 3911 | 881155 | 09:35:30 | 686333.00 | 1249060.00 | 1942370.00 |
| 3912 | 881155 | 09:36:30 | 714819.00 | 1803690.00 | 1942370.00 |
| 3913 | 881155 | 09:37:30 | 718675.00 | 2413490.00 | 2073110.00 |
| 3914 | 881155 | 09:38:30 | 713274.00 | 2508890.00 | 3004600.00 |
| 3915 | 881155 | 09:39:30 | 711690.00 | 2549290.00 | 3706170.00 |
| 3916 | 991156 | 09:30:30 | 718620.00 | 2990190.00 | 3987790.00 |
| 3917 | 991156 | 09:31:30 | 717383.00 | 3282790.00 | 4516690.00 |
| 3918 | 991156 | 09:32:30 | 763006.00 | 3282790.00 | 4864920.00 |
| 3919 | 991156 | 09:33:30 | 772281.00 | 3382790.00 | 5013120.00 |
| 3920 | 991156 | 09:34:30 | 775898.00 | 3382790.00 | 5158260.00 |
| 3921 | 991156 | 09:35:30 | 778824.00 | 3382790.00 | 5494020.00 |
| 3922 | 991156 | 09:36:30 | 778067.00 | 3432290.00 | 5761090.00 |
| 3923 | 991156 | 09:37:30 | 773697.00 | 3577390.00 | 5978690.00 |
| 3924 | 991156 | 09:38:30 | 768519.00 | 3577390.00 | 6329150.00 |
| 3925 | 991156 | 09:39:30 | 763734.00 | 3635690.00 | 6615000.00 |
+------+------------+-----------+-----------+-------------+--------------+要求的结果就是:
+---------+---------+-----------+------------+----------+------------+------------+
|real_time|buycount |bigbuycount|waitbuycount|buycount |bigbuycount |waitbuycount|
+---------+---------+-----------+------------+----------+------------+------------+
| 09:30:30|718620.00|2990190.00 |3987790.00 |631571.00 | 386114.00 | 1193500.00 |
| 09:31:30|717383.00|3282790.00 |4516690.00 |15313.00 | 740784.00 | 1368660.00 |
| 09:32:30|763006.00|3282790.00 |4864920.00 |637119.00 | 825097.00 | 1368660.00 |
| 09:33:30|772281.00|3282790.00 |5013120.00 |651109.00 | 079200.00 | 1393390.00 |
| 09:34:30|775898.00|3282790.00 |5158260.00 |646276.00 | 1249060.00 | 1846370.00 |
| 09:35:30|778824.00|3282790.00 |5158260.00 |686333.00 | 1249060.00 | 1942370.00 |
| 09:36:30|778067.00|3282790.00 |5494020.00 |714819.00 | 1803690.00 | 1942370.00 |
| 09:37:30|773697.00|3282790.00 |5494020.00 |718675.00 | 2413490.00 | 2073110.00 |
| 09:38:30|768519.00|3282790.00 |5978690.00 |713274.00 | 2508890.00 | 3004600.00 |
| 09:39:30|763734.00|3282790.00 |6615000.00 |711690.00 | 2549290.00 | 3706170.00 |
说明,也就说前3个(buycount |bigbuycount|waitbuycount)字段显示的是stock_code=991156的。
后面三个是stock_code=881155的。
select id, stock_code, real_time,
sum(if(stock_code=991156, buycount, 0)) as buycount,
sum(if(stock_code=991156, bigbuycount, 0)) as bigbuycount,
sum(if(stock_code=991156, waitbuycount, 0)) as waitbuycount,
sum(if(stock_code=881155, buycount, 0)) as buycount,
sum(if(stock_code=881155, bigbuycount, 0)) as bigbuycount,
sum(if(stock_code=881155, waitbuycount, 0)) as waitbuycount
from stock_data
group by real_time
order by real_time desc
from `stock_data`
group by real_time
MAX(if(stock_code=991156, buycount, 0)) as buycount,
MAX(if(stock_code=991156, bigbuycount, 0)) as bigbuycount,
MAX(if(stock_code=991156, waitbuycount, 0)) as waitbuycount,
MAX(if(stock_code=881155, buycount, 0)) as buycount,
MAX(if(stock_code=881155, bigbuycount, 0)) as bigbuycount,
MAX(if(stock_code=881155, waitbuycount, 0)) as waitbuycount
from stock_data group by real_time order by stock_code desc
。
select id, real_time,
sum(if(stock_code=881156, buycount, 0)) as buycount,
sum(if(stock_code=881156, bigbuycount, 0)) as bigbuycount,
sum(if(stock_code=881156, waitbuycount, 0)) as waitbuycount,
sum(if(stock_code=881155, buycount, 0)) as buycount,
sum(if(stock_code=881155, bigbuycount, 0)) as bigbuycount,
sum(if(stock_code=881155, waitbuycount, 0)) as waitbuycount
from stock_data
group by real_time
order by real_time limit 20;+------+-----------+----------+-------------+--------------+------------+-------------+--------------+
| id | real_time | buycount | bigbuycount | waitbuycount | buycount | bigbuycount | waitbuycount |
+------+-----------+----------+-------------+--------------+------------+-------------+--------------+
| 3906 | 09:30:30 | 47627.90 | 285765.00 | 190879.00 | 631571.00 | 386114.00 | 1193500.00 |
| 3907 | 09:31:30 | 51287.00 | 461365.00 | 201079.00 | 615313.00 | 740784.00 | 1368660.00 |
| 3908 | 09:32:30 | 54209.00 | 535065.00 | 240457.00 | 1274238.00 | 1650194.00 | 2737320.00 |
| 3909 | 09:33:30 | 57182.80 | 641965.00 | 298277.00 | 651109.00 | 1079200.00 | 1393390.00 |
| 3910 | 09:34:30 | 58559.70 | 719064.00 | 328477.00 | 646276.00 | 1249060.00 | 1846370.00 |
| 3911 | 09:35:30 | 63272.30 | 777463.00 | 427629.00 | 686333.00 | 1249060.00 | 1942370.00 |
| 3912 | 09:36:30 | 64842.20 | 828763.00 | 427629.00 | 714819.00 | 1803690.00 | 1942370.00 |
| 3913 | 09:37:30 | 63586.20 | 901363.00 | 480429.00 | 718675.00 | 2413490.00 | 2073110.00 |
| 3914 | 09:38:30 | 61886.60 | 1051770.00 | 615602.00 | 713274.00 | 2508890.00 | 3004600.00 |
| 3915 | 09:39:30 | 63369.30 | 1095570.00 | 696844.00 | 711690.00 | 2549290.00 | 3706170.00 |
| 3916 | 09:40:30 | 63950.20 | 1121580.00 | 782352.00 | 718620.00 | 2990190.00 | 3987790.00 |
| 3917 | 09:41:30 | 63695.60 | 1155180.00 | 791860.00 | 717383.00 | 3282790.00 | 4516690.00 |
| 3918 | 09:42:30 | 63076.40 | 1238680.00 | 889460.00 | 763006.00 | 3282790.00 | 4864920.00 |
| 3919 | 09:43:30 | 63089.50 | 1238680.00 | 937451.00 | 772281.00 | 3382790.00 | 5013120.00 |
| 3920 | 09:44:30 | 65631.40 | 1405860.00 | 987718.00 | 775898.00 | 3382790.00 | 5158260.00 |
| 3921 | 09:45:30 | 66191.90 | 1405860.00 | 1060480.00 | 778824.00 | 3382790.00 | 5494020.00 |
| 3922 | 09:46:30 | 66366.00 | 1507920.00 | 1060480.00 | 778067.00 | 3432290.00 | 5761090.00 |
| 3923 | 09:47:30 | 65982.50 | 1507920.00 | 1179400.00 | 773697.00 | 3577390.00 | 5978690.00 |
| 3924 | 09:48:30 | 67274.40 | 1522920.00 | 1179400.00 | 768519.00 | 3577390.00 | 6329150.00 |
| 3925 | 09:49:30 | 68636.30 | 1545520.00 | 1179400.00 | 763734.00 | 3635690.00 | 6615000.00 |
+------+-----------+----------+-------------+--------------+------------+-------------+--------------+