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