duanniesui6391
duanniesui6391
2015-05-25 14:53

即使在变量列表中的列中至少有一个值匹配时,Sql查询也可以选择行计数

I have a table name called Kits in which has 5 columns for Book ISBN.

  • So i want to select Row even when at least 1 value of ISBN gets matched with any of the 5 columns(ISBN_BOOK1,ISBN_BOOK2,ISBN_BOOK3,ISBN_BOOK4,ISBN_BOOK5) of table kits meant for Storing ISBN.
  • if any one of the ISBN exists then return count.

I have written an sql query for this but I am getting row output only if last value is matching with any row. In my case the value is 7589765432 Here is my sql query:

Select * 
  from kits k
  left 
  join kits_cstm kc
    on k.id = kc.id_c 
 where '8192933563' 
   and '8192933567' 
   and '8192933568' 
   and '8192933564' 
   and '7589765432' IN (kc.isbn_book_1_c,kc.isbn_book_2_c,kc.isbn_book_3_c,kc.isbn_book_4_c,kc.isbn_book_5_c) 
   and kc.city_c = 'BAN' 
   and kc.kit_for_c = 'SCHL' 
   and k.deleted = 0 
 ORDER 
    BY k.kit_id DESC 
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答

  • dsieyx2015 dsieyx2015 6年前

    You can't use "AND" to conjoin values to one operator

    WRONG WAY: WHERE '8192933536' AND '8192933567' IN ({array]) RIGHT WAY: WHERE '8192933536' IN ({array]) AND '8192933567' IN ({array])

    But I suspect that what you're actually trying to do is more like:

    SELECT 
          * 
    FROM 
         kits k LEFT JOIN kits_cstm kc
              ON k.id = kc.id_c 
    WHERE 
         ('8192933563' IN (kc.isbn_book_1_c,kc.isbn_book_2_c,kc.isbn_book_3_c,kc.isbn_book_4_c,kc.isbn_book_5_c)
          OR 
         '8192933567' IN (kc.isbn_book_1_c,kc.isbn_book_2_c,kc.isbn_book_3_c,kc.isbn_book_4_c,kc.isbn_book_5_c)
          OR 
         '8192933568' IN (kc.isbn_book_1_c,kc.isbn_book_2_c,kc.isbn_book_3_c,kc.isbn_book_4_c,kc.isbn_book_5_c)
          OR 
         '8192933564' IN (kc.isbn_book_1_c,kc.isbn_book_2_c,kc.isbn_book_3_c,kc.isbn_book_4_c,kc.isbn_book_5_c)
          OR 
         '7589765432' IN (kc.isbn_book_1_c,kc.isbn_book_2_c,kc.isbn_book_3_c,kc.isbn_book_4_c,kc.isbn_book_5_c) 
          )
    AND
         kc.city_c = 'BAN' 
    AND
         kc.kit_for_c = 'SCHL' 
    AND
         k.deleted = 0 
    ORDER BY 
         k.kit_id DESC 
    
    点赞 评论 复制链接分享
  • dongtuo2373 dongtuo2373 6年前

    You should normalize your tables. But, if you are stuck with this data structure, then just use multiple in statements. Also, you don't want a left join, you want a regular join:

    Select count(*) 
    from kits k join
         kits_cstm kc
         on k.id = kc.id_c 
     where '8192933563' IN (kc.isbn_book_1_c, kc.isbn_book_2_c, kc.isbn_book_3_c, kc.isbn_book_4_c, kc.isbn_book_5_c) 
       and '8192933567' IN (kc.isbn_book_1_c, kc.isbn_book_2_c, kc.isbn_book_3_c, kc.isbn_book_4_c, kc.isbn_book_5_c) 
       and '8192933568' IN (kc.isbn_book_1_c, kc.isbn_book_2_c, kc.isbn_book_3_c, kc.isbn_book_4_c, kc.isbn_book_5_c) 
       and '8192933564' IN (kc.isbn_book_1_c, kc.isbn_book_2_c, kc.isbn_book_3_c, kc.isbn_book_4_c, kc.isbn_book_5_c) 
       and '7589765432' IN (kc.isbn_book_1_c, kc.isbn_book_2_c, kc.isbn_book_3_c, kc.isbn_book_4_c, kc.isbn_book_5_c) 
       and kc.city_c = 'BAN' 
       and kc.kit_for_c = 'SCHL' 
       and k.deleted = 0 
    ORDER  BY k.kit_id DESC 
    
    点赞 评论 复制链接分享
  • douoyou3348 douoyou3348 6年前

    That syntax is NEVER going to work: https://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html

    'a' AND 'b' AND 'c' IN (....)
    

    is going to be executed as

    ('a' AND 'b') AND ('c' IN (...))
        TRUE      AND (....)
    

    You cannot just make up your own parsing rules for SQL.

    You also should normalize your tables. What if you need 6 isbn's at some point. Now you have alter your table. The ISBNs should be in their own child table, reducing your problem to a simple where subtable.isbn IN ('a', 'b', ...)

    点赞 评论 复制链接分享