sql—如何将多个列与mysql中的其他表进行比较

zbdgwd5y 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(278)

我在下面提到了两个表格：
表1：

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

表2：

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;

sql mysql amazon-redshift

来源：https://stackoverflow.com/questions/52493592/how-to-compare-multiple-columns-with-other-table-in-mysql

1条答案

按热度按时间

rks48beu1#

你可以试着用 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视图

赞(0）回复(0）举报 2021-06-18

我来回答

sql—如何将多个列与mysql中的其他表进行比较

1条答案

相关问题

热门标签

最新问答