需要在一个字段Mysql中搜索两个值

I need search for two values in the same field.

╔════╦════════════╦══════════════╗
║ ID ║ Meta_key   ║ Meta_value   ║
╠════╬════════════╬══════════════╣
║  1 ║ first_name ║    pritesh   ║
║  2 ║ last_name  ║    mahajan   ║
║  3 ║ first_name ║    ritesh    ║
║  4 ║ last_name  ║    jain      ║
║  5 ║ first_name ║    john      ║
║  6 ║ last_name  ║    a         ║
║  7 ║ first_name ║    Mambo     ║
║  8 ║ last_name  ║    Nombo     ║
╚════╩════════════╩══════════════╝  

This is my table and I want to search all the first names and last names. Below is my query but this does not return what I want.

SELECT * 
FROM `wp_usermeta` 
WHERE `meta_key` = 'last_name' 
  AND `meta_value` LIKE  '%mahajan%' 
  AND `meta_key` = 'first_name' 
  AND `meta_value` LIKE  '%a%'; 

Here you go

SQL Fiddle

Query:

SELECT first_name, last_name
FROM 
  (
    (
      SELECT meta_value AS first_name, id AS a_id 
      FROM wp_usermeta 
      WHERE meta_name = 'first_name'
    ) as a
  )
JOIN
  (
    (
      SELECT meta_value AS last_name, id AS b_id 
      FROM wp_usermeta 
      WHERE meta_name = 'last_name'
    ) as b
  )
WHERE a_id = b_id-1

Results:

| FIRST_NAME | LAST_NAME |
|------------|-----------|
|    pritesh |   mahajan |
|     ritesh |      jain |
|       john |         a |
|      Mambo |     Nombo |

With this, you can easily search using the two virtual columns first_name and last_name. You just need to add additional conditions to the WHERE clause. something like this:

  WHERE a_id = b_id-1
  AND first_name LIKE "%tesh%"
  AND last_name LIKE "%jai%"

will produce

| FIRST_NAME | LAST_NAME |
|------------|-----------|
|     ritesh |      jain |

NOTE

The big assumption I have made here (based on your sample data) is that the related first_name and last_name are always going to have successive id's. (two successive records in the table).

Can you try this,

 SELECT * FROM `wp_usermeta` WHERE (`meta_key` = 'last_name' OR `meta_key` = 'first_name' ) and (`meta_value` LIKE  '%mahajan%' OR `meta_value` LIKE  '%a%')

I think that both rows have to share the same key. Maybe it's ID, or user_id (not shown in your question?). If this is the case, you could use this query to return the IDs you are looking for:

SELECT ID
FROM `wp_usermeta`
WHERE
  (`meta_key` = 'last_name' AND `meta_value` LIKE  '%mahajan%')
  OR (`meta_key` = 'first_name' AND `meta_value` LIKE  '%a%)
GROUP BY ID
HAVING COUNT(*)=2

please substitute ID with the actual ID.

Schema

CREATE TABLE wp_usermeta 
    (
     id int auto_increment primary key, 
     meta_name varchar(20), 
     meta_value varchar(30)
    );

INSERT INTO wp_usermeta
(meta_name, meta_value)
VALUES
('first_name', 'pritesh'),
('last_name', 'mahajan'),
('first_name', 'ritesh'),
('last_name', 'jain'),
('first_name', 'john'),
('last_name', 'a'),
('first_name', 'Mambo'),
('last_name', 'Nombo'); 

And Query should be like this....

SELECT meta_name, meta_value
FROM wp_usermeta
WHERE 
(meta_name = 'last_name' AND meta_value LIKE  '%mahajan%' )
  OR
(meta_name = 'first_name' AND meta_value LIKE  '%a%');