时间戳

首页 > 解决方案 > 为一种信息源组合 SQL 表

sql - 为一种信息源组合 SQL 表

问题描述

我试图在查询中将 3 个不同的表组合成一行，但遇到了一些问题。目前我有四张桌子；PaintSched、Painted_Log、Paint_Defect 和 Paint_Inspection。

PaintSched - 单个条目，当调度程序只安排一些要绘制的部分时

LOT QTY
1   150
2   100

Painted_Log - 油漆部门然后拿了很多，并说明他们能画多少

LOT(FK) QTYPainted
1       145
2       100

Paint_Defect - 零件涂漆后用于油漆检查的缺陷主列表。我们手工检查我们涂漆的所有零件的质量。

 DID   Defect
 1     Scratch
 2     Paint Run

Paint_Inspection - 每次发现缺陷时，检查员都会点击相关按钮并记录以下内容。Lot 是 FK，DID 代表 Paint_Defect 中的缺陷 ID。数量始终为 1

Lot(FK)  DID(FK) QTY
1        1       1
1        1       1
1        2       1
1        1       1
2        2       1
1        2       1
2        1       1

我想要得到的是以下输出：

Lot Sched Painted Scratch Paint Run
1   150   145     3       2
2   100   100     1       1

我尝试了以下方法无济于事：

 SELECT PaintSched.Scheduled,   PaintSched.Lot,    PaintSched.qty,   PaintSched.Is_Painted,   Painted_Log.falloff 
 FROM PaintSched   
   INNER JOIN Painted_Log ON PaintSched.Lot = Painted_Log.lot   
   INNER JOIN MPA_Desc ON MPA_Desc.MPAID = PaintSched.MPAID   
   inner JOIN (
     SELECT lot,  sum(Paint_Inspection.qty) as seds   
     FROM Paint_Inspection   
     WHERE Paint_Inspection.Status = '1'
   ) AS seeds ON PaintSched.Lot = Paint_Inspection.Lot

标签： sql

解决方案

两件事情：

  1. 您应该在子查询中有一个 group by
  2. 当您为子查询设置别名时，您没有正确加入它

请参阅下面对您的查询的修改：

SELECT 
  PaintSched.Scheduled,   
  PaintSched.Lot,    
  PaintSched.qty,   
  PaintSched.Is_Painted,   
  Painted_Log.falloff 
FROM PaintSched   
INNER JOIN Painted_Log ON PaintSched.Lot = Painted_Log.lot   
INNER JOIN MPA_Desc ON MPA_Desc.MPAID = PaintSched.MPAID   
INNER JOIN (
    SELECT lot,  sum(Paint_Inspection.qty) as seds   
    FROM Paint_Inspection   
    WHERE Paint_Inspection.Status = '1'
    GROUP BY Paint_Inspection.lot -- Missing GROUP BY
  ) AS seeds 
ON PaintSched.Lot = seeds.Lot

推荐阅读