获取X个特定行。 Mysql查询

问题描述：

Say I have a database with two tables: "food", and "whatToEat".

I query the "whatToEat" and find 3 rows:

id    Food     username
 1    Apple      John
 2    Banana     John
 3    Milk       Linda

If I want to get those from the "food" table, I can just do something like this i guess:

SELECT * 
  FROM food 
 WHERE username='John' AND typeOfFood = 'apple' 
    OR typeOfFood = 'Banana' OR typeOfFood = 'Milk'

... but is it possible to dynamically write this, since the "whatToEat" table will change all the time, or do I need a loop and query the "food" table one by one for each of the objects in "whatToEat"?

EDIT

The above is just an example, the real scenario is an online game. When it's a players turn in a game, he's put on the "matches_updated" table. This table just holds his name, and the id of the match (or matches since he can be in several at the same time). When a player recive an update, I would like to check if he have any matches that needs to be updated (query "matches_updated" table), and then pull the data and return to him from the "matches" table, where all the information is stored about the matches.

Example:

The player Tim query the "mathces_updated" table and find he have 2 new matches that needs to be updated:

   match_id   username
    1          Tim
    2          Tim
    2          Lisa
    1           John
    3           John

... He now want to get the information about these matches, which is stored in the "matches" table:

match_id   match_status player1Name Player1Score Player2Name Player2Score
   1           1           John         123         Tim          12
   2           1           Lisa          4          Tim          15
   3           1           John          0          Lisa         0

假设我有一个包含两个表的数据库：“food”和“whatToEat”。 p>

我查询“whatToEat”并找到3行： p>

  id食物用户名
 1 Apple John 
 2 Banana John \  n 3 Milk Linda 
  code>  pre> 
 
 如果我想从“食物”表中获取这些，我猜我可以做这样的事情： p> 
  
 
  SELECT * 
 FROM food 
 WHERE username ='John'AND typeOfFood ='apple'
 OR typeOfFood ='Banana'OR typeOfFood ='Milk'
  code>   pre> 
 
  ...但是可以动态写这个，因为“whatToEat”表会一直改变，或者我需要一个循环并逐个查询“food”表 “whatToEat”中的每个对象？   p> 
 
 
编辑 p> 
 
 
以上只是一个例子，真实场景是在线游戏。 当玩家进入游戏时，他会选择“matches_updated”表。 这张表只保存他的名字和比赛的id（或者因为他可以同时在几个比赛中匹配）。 当玩家回复更新时，我想检查他是否有任何需要更新的匹配（查询“matches_updated”表），然后拉出数据并从“匹配”表返回给他，其中包含所有信息 关于比赛存储。   p> 
 
 
示例： p> 
 
 
播放器Tim查询“mathces_updated”表，发现他有2个需要更新的新匹配项： p>  
 
 
  match_id username 
 1 Tim 
 2 Tim 
 2 Lisa 
 1 John 
 3 John 
  code>  pre> 
 
 。  ..他现在想获得有关这些匹配的信息，这些信息存储在“匹配”表中： p> 
 
 
  match_id match_status player1Name Player1Score Player2Name Player2Score 
 1 1 John 123 Tim  12 
 2 1 Lisa 4 Tim 15 
 3 1 John 0 Lisa 0 
  code>  pre> 
  div>

答

SELECT * FROM matches_updated JOIN matches
         ON matches_updated.match_id == matches.match_id
         WHERE matches_updated.user == "Tim"

Perhaps you want a JOIN statement?

SELECT * FROM food JOIN whattoeat
         ON food.username == whattoeat.username
         WHERE food.username == "John"

I'm having a really hard time trying to understand what your desired result is - posting example of both tables in question, and the desired result of your query, might help.

答

SELECT * FROM food WHERE username='John'

答

I am not sure whether I understand the question correctly.

Actually It depends on the queried tables have what in common.

so if suppose food is a common column, then query something like this...

select * from food where food in (select food from whattoeat where username = ?)

Try it out, if it solves your problem...

获取X个特定行。 Mysql查询

相关推荐