mysql利用中間值來暫時性存儲值
mysql在查詢數據的時候，有時候後面的語句會用到查出來的某個值，
這時候你會考慮怎麼把這個值存起來，有兩個方法來達到目的：
一。利用自定義值。二。利用中間表。
下面會分別說一下這兩個方法如何調用：
www.2cto.com
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)
兩張方法各有差異：自定義變量是利用了內存，臨時表利用了數據庫空間。。哈哈，建議數據量大還是用臨時表好一點。。。
