sqlite - 复合主键上的 SQLITE JOIN 表
问题描述
在 TCL 脚本中使用 SQLlite 我有三个表:
DB eval { CREATE TABLE CompFIs ( EID int NOT NULL , PlyID int NOT NULL , LC int NOT NULL , FI float NOT NULL , SR float , ImagePath text , PRIMARY KEY ( LC, EID, PlyID )) }
DB eval { CREATE TABLE CompStresses ( EID int NOT NULL , PlyID int NOT NULL , LC int NOT NULL , sigx float , sigy float , sigxy float , PRIMARY KEY ( LC, EID, PlyID )) }
DB eval { CREATE TABLE ElemMetadata ( EID int NOT NULL , AssyID int NOT NULL , PID int , PRIMARY KEY ( EID )) }
我现在想知道给定 AssID 的第一个表“CompFIs”中的最大 FI。对于那个 FI(故障指数),我需要其他表中的相应值。为了实现这一点,我想我使用主键加入表。
但是,我没有找到如何使用他们的 PK 加入表。相反,我尝试了以下方法:
SELECT
ElemMetadata.EID AS EID ,
ElemMetadata.AssyID AS AssyID ,
ElemMetadata.PID AS PID ,
CompFIs.PlyID AS Ply ,
CompFIs.LC AS LC ,
CompFIs.FI AS FI ,
CompFIs.SR AS SR ,
CompStresses.sigx AS sigx ,
CompStresses.sigy AS sigy ,
CompStresses.sigxy AS sigxy
FROM
CompFIs
LEFT JOIN CompStresses ON CompFIs.EID = CompStresses.EID
LEFT JOIN ElemMetadata ON CompFIs.EID = ElemMetadata.EID
WHERE AssyID = 1 ORDER BY FI DESC LIMIT 5
这不起作用,因为仅使用 EID 列匹配数据。
如何根据复合 PK 加入表?
示例值:
Table: CompFIs
--------------------------------------------------
EID PlyID LC FI SR ImagePath
--------------------------------------------------
282812 7 1 1.2824 null null
282813 2 3 0.3333 null null
Table: ElemMetadata
--------------------------------------------------
EID AssyID PID
--------------------------------------------------
282812 1 51
282813 1 51
Table: CompStresses
--------------------------------------------------
EID PlyID LC sigx sigy sigxy
--------------------------------------------------
282812 1 1 30.7 169.9 -31.13
282813 1 1 12.7 180.7 55.2
Expected output:
-------------------------------------------------------------------------------
EID AssyID PID Ply LC FI SR sigx sigy sigxy
-------------------------------------------------------------------------------
282812 1 51 1 1 1.2824 null 30.7 169.9 -31.13
任何帮助表示赞赏。
解决方案
使用一个CTE
和NOT EXISTS
:
with cte as (
SELECT
ElemMetadata.EID AS EID ,
ElemMetadata.AssyID AS AssyID ,
ElemMetadata.PID AS PID ,
CompFIs.PlyID AS Ply ,
CompFIs.LC AS LC ,
CompFIs.FI AS FI ,
CompFIs.SR AS SR ,
CompStresses.sigx AS sigx ,
CompStresses.sigy AS sigy ,
CompStresses.sigxy AS sigxy
FROM
CompFIs
LEFT JOIN CompStresses ON CompFIs.EID = CompStresses.EID
LEFT JOIN ElemMetadata ON CompFIs.EID = ElemMetadata.EID
WHERE AssyID = 1
ORDER BY FI DESC LIMIT 5
)
select c.* from cte c
where not exists (
select 1 from cte
where FI > c.FI
)
请参阅演示。
结果:
| EID | AssyID | PID | Ply | LC | FI | SR | sigx | sigy | sigxy |
| ------ | ------ | --- | --- | --- | ------ | --- | ---- | ----- | ------ |
| 282812 | 1 | 51 | 7 | 1 | 1.2824 | | 30.7 | 169.9 | -31.13 |
推荐阅读
- java - 使用 Apache Commons CSV 读取 Java 中的 CSV 文件以获取循环起始索引 1
- sql-server - LIKE '%[9-15]%' (SQL Server)
- vue.js - 在 iView UI 表中显示/隐藏折叠行
- angular - 从多个数据源检索数据
- sql - 在 SQL Server 中创建枚举
- jquery - jquery tablesorter - 使用图像和 div 提取文本
- c# - 从 Gmail API 检索超过 100 个邮件 ID
- c++ - Is it possible to declare a template function with 'auto' return type as a friend of a class?
- html - How can I prevent screenreaders from reading the entire contents of a list item without pausing?
- ruby-on-rails - RabbitMQ with sidekiq