开发者

Return a boolean value if string matches one of list MYSQL

I have 2 tables

SEQUENCES
-----------------
sequence (blob)


KNOWN_SEQUENCES
-----------------
sequence (blob)

I need to return a list of all entries in the sequences table and Id like to return a boolean if it is in the known table list

开发者_StackOverflow社区
sequence            known
----------------------------------
111423fa686ca          0
066787caf5671          1


See use of 'CASE' in mysql. http://dev.mysql.com/doc/refman/5.0/en/case-statement.html.

I am posting a sample here. Sorry I dont have access to a sql server right now to test this. Try to see if something like this helps.

Select s.sequence,
    CASE 
        WHEN (select count(*) from KNOWN_SEQUENCES k where k.sequence = s.sequence) > 0 THEN '1'
        ESLE '0'
    END 
    `known`,
from SEQUENCES s;

Also, indexing the table 'KNOWN_SEQUENCES' on column 'sequence' might be better keeping performance in mind.


I did get it working, but it takes 3 seconds for only 9,000 records. That may be the best I can do. Luckily I just need to run this once.

SELECT
  sequences.*,
  1 as `known`
FROM
  sequences, known_sequences
WHERE sequences.sequence = known_sequences.sequence
UNION
 SELECT
  sequences.*,
  0 as `known`
FROM
  sequences, known_sequences
WHERE sequences.sequence NOT IN(
SELECT known_sequences.sequence
FROM known_sequences) GROUP BY sequences.id


This might be a little long, but it should work.

  SELECT
      s.*,
      1 as `known`
    FROM
      sequence AS s
    INNER JOIN
      known_sequences AS ks
    ON
      s.sequence = ks.sequence
UNION
   SELECT
      s.sequence,
      0 AS `known`
   FROM
      sequence AS s
   WHERE
     s.sequence NOT IN
   (
   SELECT
      s2.sequence
    FROM
      sequence AS s2
    LEFT JOIN
      known_sequences AS ks
    ON
      s.sequence == ks.sequence
   )
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜