如何通过选择特定列中相关数据的组合来从表中选择行？ MySQL的

问题描述：

I have a table like;

tablea

4c4fedf7    OMoy3Hoa
4c4fedf7    yiWDGB4D
broe4AMb    A9rLRawV
broe4AMb    mi9rLmZW
nhrtK9ce    yEsBoYLj
rEEtK9gt    A9rLRawV
rEEtK9gt    mi9rLmZW
rEEtK9Hh    A9rLRawV
rEEtK9Hh    msBWz8CQ

If I give the input like A9rLRawV,mi9rLmZW. I want an output like;

broe4AMb
rEEtK9gt

The output is generated as a result of A9rLRawV,mi9rLmZW combination. Here, broe4AMb and rEEtK9gt both have A9rLRawV and mi9rLmZW associated in tablea. I made a query, but I get output like;

broe4AMb
rEEtK9gt
rEEtK9Hh

My query is like;

SELECT DISTINCT prodid
FROM tablea
WHERE tagid IN ('A9rLRawV','mi9rLmZW');

The output is like this because I think, reetK9Hh has A9rLRawV associated with it in tablea. But i don't want that entry to appear because it doesn't have mi9rLmZW associated with it.

Here is the SQL fiddle http://sqlfiddle.com/#!9/12223/2/0

Does it require a SELF JOIN. What will be the most 'efficient' method? Is it possible to achieve this with MySQL alone or with support of PHP? How can I do this / fix this?

我有一个类似的表; p>

tablea strong > p>

4c4fedf7 OMoy3Hoa 4c4fedf7 yiWDGB4D broe4AMb A9rLRawV broe4AMb mi9rLmZW nhrtK9ce yEsBoYLj \ nrEEtK9gt A9rLRawV rEEtK9gt mi9rLmZW rEEtK9Hh A9rLRawV rEEtK9Hh msBWz8CQ code> pre> 如果我输入 A9rLRawV code>， mi9rLmZW code >。我想输出像; p> broe4AMb rEEtK9gt code> pre> 输出是由于 A9rLRawV code>， mi9rLmZW code>组合。这里， broe4AMb code>和 rEEtK9gt code>都有tablea中关联的 A9rLRawV code>和 mi9rLmZW code>。我做了一个查询，但得到输出像; p> broe4AMb rEEtK9gt rEEtK9Hh code> pre> 我的查询是喜欢; p> SELECT DISTINCT prodid FROM tablea WHERE tagid IN（'A9rLRawV'，'mi9rLmZW'）; code> pre> 输出是这样的，因为我认为， reetK9Hh code>在tablea中与 A9rLRawV code>相关联。但我不希望该条目出现，因为它没有与之关联的 mi9rLmZW code>。 p> 这是SQL小提琴 http://sqlfiddle.com/#!!/12223/2/0 p> \ n 是否需要SELF JOIN。什么是最“有效”的方法？是否可以单独使用MySQL或支持PHP来实现这一目标？我该怎么做/解决这个问题？ p> div>

答

I firmly believe this can be resolved by SQL alone. Get the data you need from the database. Have PHP do the presentation. Why? Typically the Database can parse data much faster than you can through code on a webserver/middleware server.

As to how... Read up on having clauses and group by:

What I've done here is group by prodID to ensure all prodIDs are returned. mySQL extends the group by clause and while it may allow a having clause to exist without a group by, you will not get the desired results without grouping by prodid. The having a gets a distinct count of both tags requested in the where. Note: I added distinct to the count on tagID as I was unsure if your overall data could have duplicate tagIds for each prodid. we could eliminate it if we know values are distinct.

The # can be dynamically set based on the number of TagIDs provided if needed.

SELECT prodid
FROM tablea
WHERE tagID in ('A9rLRawV','mi9rLmZW')
GROUP BY  prodid
HAVING count(distinct tagID)=2

SQL FIddle

I prefer this approach as it scales better than a self join as you indicated might be needed. Pass in two parameters: one for the tags one for the number of tags. (I do so hope you're using paramaterized queries with your PHP) With self joins you have to write dynamic SQL to handle each additional tag so if you have 3, 4,5 more joins. This way you just pass in the 5 values and the number 5; and get a list back of all those that match.

答

Like you assumed, a self join helps. Use this query

SELECT a.prodid
FROM tablea AS a
INNER JOIN tablea AS b
           ON a.prodid = b.prodid
WHERE a.tagid = 'A9rLRawV' and b.tagid = 'mi9rLmZW'

This here you join the table with itself and the matching pairs have both A9rLRawV (in the main table a) and mi9rLmZW (in the joined table b).

Here's your updated SQL fiddle: http://sqlfiddle.com/#!9/12223/13

The results are as wished:

broe4AMb
rEEtK9gt

如何通过选择特定列中相关数据的组合来从表中选择行？ MySQL的

相关推荐