SQL查询：计算给定两个或多个字符串的行数

| id | pid | idx | type | key       | val        |
|----+-----+-----+------+-----------+------------|
|  1 |   0 |     | o    |           |            |
|  2 |   1 |     | s    | time      | 2010-...   |
|  3 |   1 |     | s    | ip        | 127.0.0.1  |
|  4 |   1 |     | o    | p         |            |
|  5 |   4 |     | s    | tape/id   | 8abade...  |
|  6 |   4 |     | s    | tape/page | http://... |
|  7 |   0 |     | o    |           |            |
|  8 |   7 |     | a    | foo       |            |
|  9 |   8 |   0 | s    |           | bar        |
| 10 |   8 |   1 | o    |           |            |
| 11 |  10 |     | s    | baz       | quux       |
| 12 |   4 |     | s    | tape/dref | x          |

If you cannot see it properly, it's available on pastebin (http://pastebin.com/2RDn7ad0).

Ok, I have a database like that. It contains many rows (a lot more than in the above example). Only fields I care about right now are tape/id and tape/dref. Now, I have at least two strings. For one string, I have a query like this:

select count(*) as count from (select distinct e.val from entries e where e.key='tape/id' and (select s.val from entries s where s.pid=e.pid and s.key='tape/dref')='x') q;

This query works as it should. Now, the problem I cannot solve is to have the same query but that it counts how many rows exists with two or more strings given (in the above example there is only one string "x").

May I suggest to replace ='x' with IN (list of values), at the end of the query?

For example: ... IN ('x', 'y', 'z')

SELECT  COUNT(DISTINCT eid.val)
FROM    entries eid
JOIN    entries edref
ON      edref.pid = eid.pid
        AND edref.key = 'tape/dref'
        AND edref.val IN ('x', 'y')
WHERE   eid.key = 'tape/id'

Create a UNIQUE INDEX on entries(key, pid, val) for this to work fast.

(where e.key = 'tape/id' or where e.key = 'tape/dref') AND (where e.val = 'x' or where e.val = ???)