mysql利用中间值来暂时性存储值
mysql在查询数据的时候，有时候后面的语句会用到查出来的某个值，
这时候你会考虑怎么把这个值存起来，有两个方法来达到目的：
一。利用自定义值。二。利用中间表。
下面会分别说一下这两个方法如何调用：
1.如何把某张表的某个值存到自定义变量上
这个利用内存来存值
sql代码
#定义@amax_price为常量值
mysql> set @amax_price=(select max(fscorecount) from tbl_user);
query ok, 0 rows affected (0.00 sec)
mysql> select * from tbl_user where fscorecount= @amax_price;
+---------+-----------+-------+------+-------------+------------+--------------+
--------+--------+---------+---------+---------+---------+--------------+-------
-------+---------+---------------------+------------+--------------+------------
---------+----------------+-------+
| fuserid | fqq | fnick | fpwd | fscorecount | fvotecount | finvitecount |
fbtype | fltype | fenable | fvalue1 | fvalue2 | fvalue3 | fvalue4 | fvalue
5 | fvalue6 | ftime | fdate | fip | flastlogint
ime | flastlogindate | fmemo |
+---------+-----------+-------+------+-------------+------------+--------------+
--------+--------+---------+---------+---------+---------+--------------+-------
-------+---------+---------------------+------------+--------------+------------
---------+----------------+-------+
| 1 | 455342107 | | | 22 | 0 | 2 |
0 | 0 | 0 | 1 | 0 | | 2011-08-15_3 | 2011-0
8-10_2 | | 2011-08-10 10:02:40 | 2011-08-10 | 192.168.1.34 | 2011-08-16
14:30:18 | 2011-08-16 | |
+---------+-----------+-------+------+-------------+------------+--------------+
--------+--------+---------+---------+---------+---------+--------------+-------
-------+---------+---------------------+------------+--------------+------------
---------+----------------+-------+
2.如何把某张表的某个值存到临时表
sql代码
#查出tbl_user表中所有值
mysql> select fqq,fscorecount from tbl_user;
+-----------+-------------+
| fqq | fscorecount |
+-----------+-------------+
| 455342107 | 22 |
| 24222 | 0 |
| 345333 | 5 |
| 664444 | 5 |
| 234324 | 0 |
| 137543511 | 5 |
| 519422206 | 5 |
| 234222 | 0 |
| 14234 | 5 |
| 1242354 | 5 |
| 111565 | 5 |
| 342323 | 0 |
| 234322 | 0 |
| 543244 | 0 |
+-----------+-------------+
14 rows in set (0.00 sec)
#把最大值存储到临时表tmp上
mysql> create table tmp select max(fscorecount) as max_score from tbl_user;
query ok, 1 row affected (0.05 sec)
records: 1 duplicates: 0 warnings: 0
#查出临时表结构
mysql> select * from tmp;
+-----------+
| max_score |
+-----------+
| 22 |
+-----------+
1 row in set (0.00 sec)
mysql> show tables;
+----------------------------------+
| tables_in_db_nissansunny_2011_08 |
+----------------------------------+
| tbl_code |
| tbl_comment |
| tbl_file |
| tbl_invitehistory |
| tbl_lotterycount |
| tbl_lotteryhistory |
| tbl_qqshow |
| tbl_score |
| tbl_scoredetail |
| tbl_user |
| tbl_useraward |
| tbl_userprofile |
| tbl_votehistory |
| tmp |
+----------------------------------+
14 rows in set (0.00 sec)
两张方法各有差异：自定义变量是利用了内存，临时表利用了数据库空间。。哈哈，建议数据量大还是用临时表好一点。。。
bitscn.com