mysql查询相当于一个条件。有什么建议么？

Question

我不是 SQL 专家。

我想实现与此条件等效的查询

If  ( (first-access-of-error1 = first-access-of-error2 and 
       second-access-of-error1 = second-access-of-error2) OR 
      (first-access-of-error1 = second-access-of-error2 and
       second-accessr-of-error1 = first-access-of-error2) )

我尝试过这样的事情:

select d.id,
       concat(a.variable_name,"|",a.file_url,"|",a.line_number,"|",a.stacktrace)
             as FirstAccess_Params, 
       concat(b.variable_name,"|",b.file_url,"|",b.line_number,"|",b.stacktrace) 
             as SecondA_Params 
from defect d 
right join (accessor a, accessor b) 
   on (d.id=a.defect_id and d.id=b.defect_id and a.id<b.id) 
where d.category_id=0 and d.relationship_id!=-1 
group by FirstAccess_Params,SecondA_Params

通过上面的查询，我可以解决这个问题:

(first-access-of-error1 = first-access-of-error2 and 
 second-access-of-error1 = second-access-of-error2)

但是我遇到了如何实现这个条件的问题:

(first-access-of-error1 = second-access-of-error2 and
 second-accessr-of-error1 = first-access-of-error2)

有什么帮助值得赞赏吗？欢迎任何想法..

这是访问器表的架构

<createTable tableName="accessor">
        <column autoIncrement="true" name="id" type="BIGINT UNSIGNED">
            <constraints nullable="false" primaryKey="true"/>
        </column>
        <column name="defect_id" type="BIGINT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="operation" type="TINYINT UNSIGNED"/>
        <column name="variable_name" type="VARCHAR(128)"/>
        <column name="object_address" type="VARCHAR(64)"/>
        <column name="type" type="TINYINT UNSIGNED"/>
        <column name="module_id" type="INT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="file_url" type="VARCHAR(256)"/>
        <column name="function_name" type="VARCHAR(64)"/>
        <column name="line_number" type="SMALLINT UNSIGNED"/>
        <column name="accessing_order" type="TINYINT UNSIGNED"/>
        <column name="stacktrace_type" type="TINYINT UNSIGNED"/>
        <column name="stacktrace" type="VARCHAR(2048)"/>
        <column name="parameter" type="VARCHAR(5120)"/>
    </createTable>

这是缺陷表的架构

<createTable tableName="defect">
        <column autoIncrement="true" name="id" type="BIGINT UNSIGNED">
            <constraints nullable="false" primaryKey="true"/>
        </column>
        <column defaultValueNumeric="0" name="rule_id" type="SMALLINT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column defaultValueBoolean="false" name="hide" type="BIT">
            <constraints nullable="false"/>
        </column>
        <column defaultValueNumeric="0" name="relationship_id" type="BIGINT">
            <constraints nullable="false"/>
        </column>
        <column name="category_id" type="SMALLINT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="sub_category_id" type="SMALLINT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="module1_id" type="INT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="module2_id" type="INT UNSIGNED"/>
        <column name="execution_instance_id" type="INT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="application_id" type="INT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="project_id" type="INT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="target_id" type="INT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="testsuite_id" type="INT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="timestamp" type="INT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="priority" type="BIT"/>
        <column name="status" type="BIT"/>
        <column name="assignee" type="VARCHAR(64)"/>
        <column name="label_ids" type="VARCHAR(1024)"/>
        <column name="remark" type="VARCHAR(512)"/>
        <column name="accessor_ids" type="VARCHAR(1024)"/>
        <column name="parameter" type="VARCHAR(2048)"/>
        <column defaultValueNumeric="0" name="parent_id" type="BIGINT">
            <constraints nullable="false"/>
        </column>
    </createTable>

Answer 1

考虑这个 jsFiddle:http://sqlfiddle.com/#!2/c25e1/1 。它不显示重复项。还要考虑使用 DISTINCT sql 命令。