mysql - 如何将多个列与 MySql 中的其他表进行比较
问题描述
我在下面提到了两个表:
表格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;
解决方案
您可以尝试使用CAST
asUNSIGNED
来忽略作为连接条件的十进制值。
架构(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 |
推荐阅读
- amazon-web-services - Appsync:订阅数组中的元素
- node.js - 使用 --mount 标志时 Docker 容器不运行
- android - 为什么我的 onCreateView 中的 WebView 代码无法访问?
- python - 如何在绿龙渲染中获取剪辑距离和纹理
- python - 删除链表中所有重复的元素
- android - 错误:无法解决:com.github.imperiumlabs:GeoFirestore-Android:v1.5.0
- tensorflow - RGB图像上的Tensorflow conv2d
- r - 在R中覆盖2张具有透明度的图像
- python - 当有很多字段时管理 django 的创建方法
- c# - 在满足条件之前查找值