dongtan7418
2016-07-19 13:35
浏览 35
已采纳

比较表字段值以在数据库中插入新值

I inherited a website with an existing database structure. I have one table (TABLE1) that has something like the following structure:

POSITION_1_TAGS_PERMITTED  | POSITION_2_TAGS_PERMITTED | POSITION_3_TAGS_PERMITTED
----------------------------------------------------------------------------------
CD                         | EN,CS                     | TECH,CS

TABLE1 has this structure up to 20 columns.

I then have another table (TABLE2) that stores the selected value (also has 20 columns):

POS1_SEL_SYMBOL | POS2_SEL_SYMBOL | POS3_SEL_SYMBOL | POS4_SEL_SYMBOL
---------------------------------------------------------------------
A               | AAPL            |                 | AA

I have the tags for the selected variable in this case the tag for my variable is CS. I'd like to find the position in TABLE1 that has the allowed tags and then look in TABLE2 to see if that position already has an entry in it. In this case it would identify position 3 as the column to add my entry to and then I would write a new update statement to add that entry to the database. Is there an easy way to do this with the column format of my database?

EDIT: If there are multiple tags for the variable I'd like it to search the permitted tags column for any value in it. I tried to update my SQL statement to be something like the following:

SELECT eventid, 
CASE 
WHEN (FIND_IN_SET("S&P", POSITION_1_TAGS_PERMITTED) OR FIND_IN_SET("CS", POSITION_1_TAGS_PERMITTED)) AND POS1_SEL_SYMBOL = ""
THEN 1 
END AS found_col 
FROM TABLE1 
JOIN TABLE2 
USING (eventid) 
WHERE EVENTID='159'

This query doesn't give me the same results as just searching for CS did.

图片转代码服务由CSDN问答提供 功能建议

我继承了一个具有现有数据库结构的网站。 我有一个表( TABLE1 ),它具有类似以下结构:

  POSITION_1_TAGS_PERMITTED |  POSITION_2_TAGS_PERMITTED |  POSITION_3_TAGS_PERMITTED 
 -----------------------------------------------  ----------------------------------- 
CD |  EN,CS |  TECH,CS 
   
 
 

TABLE1 具有最多20列的此结构。

然后我有另一个表( TABLE2 )来存储所选的值(也有20列):

 < 代码> POS1_SEL_SYMBOL |  POS2_SEL_SYMBOL |  POS3_SEL_SYMBOL |  POS4_SEL_SYMBOL 
 -----------------------------------------------  ---------------------- 
A |  AAPL |  |  AA 
   
 
 

我有所选变量的标签,在这种情况下,我的变量的标签是 CS 。 我想在 TABLE1 中找到具有允许标签的位置,然后查看 TABLE2 以查看该位置是否已有条目。 在这种情况下,它会将位置3标识为要添加我的条目的列,然后我会编写一个新的更新语句以将该条目添加到数据库中。 有没有一种简单的方法可以使用我的数据库的列格式来执行此操作?

编辑:如果变量有多个标记,我希望它可以搜索允许的标记列中的任何一个 它的价值。 我尝试将我的SQL语句更新为如下所示:

  SELECT eventid,
CASE 
WHEN(FIND_IN_SET(“S&amp; P”,POSITION_1_TAGS_PERMITTED)或FIND_IN_SET(“  CS“,POSITION_1_TAGS_PERMITTED))和POS1_SEL_SYMBOL =”“
THEN 1 
END AS found_col 
FROM TABLE1 
JOIN TABLE2 
USING(eventid)
WHERE EVENTID ='159'
   
 
  

这个查询不会给我带来与搜索CS相同的结果。

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • drus40229 2016-07-19 13:50
    已采纳
    SELECT eventid, 
        CASE
            WHEN FIND_IN_SET('CS', POSITION_1_TAGS_PERMITTED) AND POS1_SEL_SYMBOL = ''
            THEN 1
            WHEN FIND_IN_SET('CS', POSITION_2_TAGS_PERMITTED) AND POS2_SEL_SYMBOL = ''
            THEN 2
            WHEN FIND_IN_SET('CS', POSITION_3_TAGS_PERMITTED) AND POS3_SEL_SYMBOL = ''
            THEN 3
            ...
            WHEN FIND_IN_SET('CS', POSITION_20_TAGS_PERMITTED) AND POS20_SEL_SYMBOL = ''
            THEN 20
        END AS found_col
    FROM TABLE1
    JOIN TABLE2 USING (eventid)
    

    See SQL Insert in first empty column in a row MySQL error for an UPDATE query that you could adapt using the above structure so that you update the field that's found rather than just returning the column number. But with 20 columns, that query will get really long.

    已采纳该答案
    打赏 评论

相关推荐 更多相似问题