如何比较多列与MySql中的其他表

Question

下面我提到了两张表格：

Table1：

ID        Value1      Value2       Value3     Remarks
RTE-10    2400.00     1.5          2300       Processed
RTE-11    1300.00     1.8          1750       Failed
RTE-12    1900.00     1.25         2000       Accepted

Table2：

ID        Read1      Read2         Read3         Status
RTE-10    2400.0     1.5           2300.25       Processed
RTE-11    1300.0     1.4           1900.22       Accepted
RTE-12    1600.0     1.20          2000.45       Processed

通过比较这两个表，我希望从Table1到Table2 (忽略十进制值)获取值不匹配的行。

所需产出：

ID       Value1      Value2       Value3     Remarks    Read1      Read2         Read3         Status
RTE-11   1300.00     1.8          1750       Failed     1300.0     1.4           1900.22       Accepted
RTE-12   1900.00     1.25         2000       Accepted   1600.0     1.20          2000.45       Processed

我正在尝试下面提到的查询：

select * from Table1 t1
left join Table2 t2 on t1.ID=t2.ID
where t1.Value1!=t2.Read1
and t1.Value2!=t2.Read2
and t1.Value3!=t2.Read3
and t1.Remarks!=t2.Status
group by t1.ID;

Answer 1

您可以尝试使用CAST作为UNSIGNED来忽略十进制值被加入的条件。

模式(MySQL v5.7)

CREATE TABLE Table1(
    ID VARCHAR(50),
    Value1 FLOAT,
    Value2 FLOAT,
    Value3 FLOAT,
    Remarks VARCHAR(50)
);


INSERT INTO Table1 values ('RTE-10',2400.00,1.5,2300,'Processed');
INSERT INTO Table1 values ('RTE-11',1300.00,1.8,1750,'Failed');
INSERT INTO Table1 values ('RTE-12',1900.00,1.25,2000,'Accepted');

CREATE TABLE Table2(
    ID VARCHAR(50),
    Read1 FLOAT,
    Read2 FLOAT,
    Read3 FLOAT,
    Status VARCHAR(50)
);


INSERT INTO Table2 values ('RTE-10',2400.0,1.5,2300.25,'Processed');
INSERT INTO Table2 values ('RTE-11',1300.0,1.4,1900.22,'Accepted');
INSERT INTO Table2 values ('RTE-12',1600.0,1.20,2000.45,'Processed');

查询#1

SELECT t1.*,t2.*
FROM Table1 t1 JOIN Table2 t2 
ON t1.ID = t2.ID and 
(
    CAST(t1.Value1 AS UNSIGNED )<> CAST(t2.Read1 AS UNSIGNED ) OR
    CAST(t1.Value2 AS UNSIGNED )<> CAST(t2.Read2 AS UNSIGNED ) OR
    CAST(t1.Value3 AS UNSIGNED )<> CAST(t2.Read3 AS UNSIGNED )
 );

| ID     | Value1 | Value2 | Value3 | Remarks  | ID     | Read1 | Read2 | Read3   | Status    |
| ------ | ------ | ------ | ------ | -------- | ------ | ----- | ----- | ------- | --------- |
| RTE-11 | 1300   | 1.8    | 1750   | Failed   | RTE-11 | 1300  | 1.4   | 1900.22 | Accepted  |
| RTE-12 | 1900   | 1.25   | 2000   | Accepted | RTE-12 | 1600  | 1.2   | 2000.45 | Processed |

关于DB Fiddle的看法