Geek asks and answers

The most efficient sql for searching

I have this table

 ----------------
|   X    |   Y   |
 ----------------
|   a    |   1   |
|   c    |   6   |
|   e    |   3   |
|   d    |   6   |
|   c    |   4   |
|   b    |   1   |
|   a    |   5   |
|   g    |   1   |
 ----------------

When I am given the array [c, d], I need to find "6" in the above table. That is, for each set of elements I need to find the value Y, which is shared by all elements in the set, but only if there is no other element (i.e., an element that is not in this array) that shares this value. The number of elements in the array has no theoretical limits.

Other examples: for [a, b, c] I do not need to look for anything. For [a, b] I also do not need to look for anything (because g also has an entry for Y = 1, so for [a, b, g] I need to find "1").

I could, of course, iterate over the array, request by request, but this seems like such an inefficient way to execute it. What is the best way to do this in SQL? Thank.

+5
2

"" .

create table t ( x varchar(1), y int);

insert into t (x, y) values ('a', 1);
insert into t (x, y) values ('c', 6);
insert into t (x, y) values ('e', 3);
insert into t (x, y) values ('d', 6);
insert into t (x, y) values ('c', 4);
insert into t (x, y) values ('b', 1);
insert into t (x, y) values ('a', 5);
insert into t (x, y) values ('g', 1);

create table q ( x varchar(1) );

insert into q (x) values ('a');
insert into q (x) values ('b');

select a.y from
(
   select t.y
     from t join q on (t.x = q.x)
   group by t.y
   having count(*) = (select count(*) from q)
) a 
join t on (a.y = t.y) 
group by a.y
having count(*) = (select count(*) from q)

SQLFiddle.

, .

, select count(*) , IN, , , where.

select a.y from
(
select t.y
from t
  where t.x in ('c', 'd')
group by t.y
having count(*) = 2
) a 
join t on (a.y = t.y) 
group by a.y
having count(*) = 2
+2

/ Y X.

, ...

CREATE TEMPORARY TABLE params (
  item VARCHAR(16)
)
INSERT INTO params SELECT 'a'
INSERT INTO params SELECT 'b'
INSERT INTO params SELECT 'g'


SELECT
  yourTable.Y
FROM
  yourTable
LEFT JOIN
  params
    ON yourTable.X = params.item
GROUP BY
  yourTable.Y
HAVING
  COUNT(DISTINCT yourTable.X) = COUNT(DISTINCT params.item)


, , , ...

SELECT
  y
FROM
  yourTable
GROUP BY
  y
HAVING
  COUNT(DISTINCT x) = COUNT(DISTINCT CASE WHEN x IN ('a', 'b', 'g') THEN x ELSE NULL END)

, .

+2

More articles:


All Articles