mysql利用中间值来暂时性存储值_MySQL

bitsCN.com

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