我们可以使用OR关键字进行sql连接吗? [英] Can we do a sql join with OR key word?

问题描述

我的表如下所示； (我是从excel填充的)

My table looks like as follows; (I populated it from excel)

我想从另一个表中提取一些数据，所以我使用sql joins.由于我的栏包含逗号分隔的值，因此我尝试将Join与Or一起使用，但未成功.这是加入会员的正确方法吗? 我正在寻找此逗号分隔列的快速解决方案.

I want to extract some data from another table so I use sql joins. Since my column contains comma separated values, i try to use join with Or but no success. Is this right way to do joins? I look for quick solution for this comma separated columns.

例如:

SELECT * FROM test.types as a
inner join test.`matric as ma on (a.category= SUBSTRING_INDEX(ma.`Function Code AA`,',',1) 
or a.category= SUBSTRING_INDEX(ma.`Function Code AA`,',',2) 
or a.category= SUBSTRING_INDEX(ma.`Function Code AA`,',',3) 
or a.category= SUBSTRING_INDEX(ma.`Function Code AA`,',',4)
or a.category= SUBSTRING_INDEX(ma.`Function Code AA`,',',5) 
or a.category= SUBSTRING_INDEX(ma.`Function Code AA`,',',6))
and a.type = ma.`function Code NN` and ma.`Priority` = "T1"

推荐答案

您有一个可怕的数据模型.您可能无法大大加快查询速度.但是您至少可以简化代码.

You have a horrible data model. You probably cannot speed up the query very much. But you can at least simplify the code.

SELECT *
FROM test.types t JOIN
     test.matric ma
     ON FIND_IN_SET(t.category, REPLACE(ma.`Function Code AA`, ', ', ',')) > 0 OR
        t.type = ma.`function Code NN` 
WHERE ma.`Priority` = 'T1';

但是，您应该修复数据模型！.这里有一些问题:

However, you should fix your data model!!!. Here are some issues:

• 数据库的字符串处理能力很差.
• 应使用正确的类型存储值.
• 外键应正确声明.
• 这种结构阻止数据库使用索引，分区和最佳的优化方法.

SQL有一种 great 方式来存储列表.它不称为 string .它称为表.

SQL has a great way to store lists. It is not called a string. It is called a table.

这篇关于我们可以使用OR关键字进行sql连接吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！