mysql - 三个小表的SQL查询

Question

关闭。这个问题需要更多 focused 。目前不接受答案。

---

想改进这个问题吗？ 更新问题，使其只关注一个问题 editing this post .

已关闭 3 年前。

Improve this question

我的 SQL 查询是否有任何人都能看到的错误？我正在使用名为 SHPMT、ORIGIN 和 SHPMT_TRUCK 的三个表，预期输出应为 Location、Ship_Day、Count_With_Truck 和 Count_Without_Truck。如果需要更多信息，请告诉我。谢谢!

SELECT 
  SHPMT.Origin_Abbr AS Location, 
  DATE(SHPMT.Sched_Tsp) AS Ship_Day,
  COUNT(SHPMT_TRUCK.Truck_ID) AS Count_With_Truck,
  (COUNT(SHPMT.ID) - COUNT(TRUCK.TRUCK_ID)) AS Count_Without_Truck,
FROM SHPMT
WHERE ORIGIN.Typ_Cd = 'BRWY'
  AND SHPMT.Shp_Tsp = NULL
  AND Count_With_Truck > Count_Without_Truck
JOIN SHPMT_TRUCK ON SHPMT.ID = SHPMT_TRUCK.Shpmt_ID 
JOIN ORIGIN ON SHPMT.Origin_Abbr = ORIGIN.Origin_Abbr
GROUP BY SHPMT.Origin_Abbr, DATE(SHPMT.Sched_Tsp)
ORDER BY SHPMT.Origin_Abbr, COUNT(SHPMT.Shpmt_ID);

Answer 1

我不知道从哪里开始:)

• 作为@B。 Seberle 在他的评论中说 JOIN 应该在 WHERE 之前条款
• 在一个地方使用 TRUCK.TRUCK_ID，其中 TRUCK 应该是 SHPMT_TRUCK 表的别名，但您尚未将该别名指定给该表如下所示:JOIN SHPMT_TRUCK TRUCK ON SHPMT.ID = TRUCK.Shpmt_ID。当然，你应该更改每个引用到 SHPMT_TRUCK 表中的列
• 您在查询中使用了在查询中创建的别名:Count_Without_Truck 和 Count_With_Truck
• 它是 IS NULL 而不是 = NULL
• GROUP BY 和 ORDER BY 不行......

当我删除所有这些错误和更多错误时，我得到了这个:

select * from (SELECT SHPMT.Origin_Abbr AS Location
       , DATE(SHPMT.Sched_Tsp) AS Ship_Day
       , COUNT(TRUCK.Truck_ID) AS Count_With_Truck
       , (COUNT(SHPMT.ID) - COUNT(TRUCK.TRUCK_ID)) AS Count_Without_Truck
FROM SHPMT
JOIN SHPMT_TRUCK TRUCK ON SHPMT.ID = TRUCK.Shpmt_ID
JOIN ORIGIN ON SHPMT.Origin_Abbr = ORIGIN.Origin_Abbr
WHERE ORIGIN.Typ_Cd = 'BRWY'
AND SHPMT.Shp_Tsp IS NULL
GROUP BY SHPMT.Origin_Abbr, SHPMT.Sched_Tsp
ORDER BY SHPMT.Origin_Abbr, SHPMT.Sched_Tsp) A
where Count_With_Truck > Count_Without_Truck;

或者您可以按照 @scaisEdge 的建议使用 HAVING 来完成此操作(感谢 @scaisEdge!):

SELECT SHPMT.Origin_Abbr AS Location
       , DATE(SHPMT.Sched_Tsp) AS Ship_Day
       , COUNT(TRUCK.Truck_ID) AS Count_With_Truck
       , (COUNT(SHPMT.ID) - COUNT(TRUCK.TRUCK_ID)) AS Count_Without_Truck
FROM SHPMT
JOIN SHPMT_TRUCK TRUCK ON SHPMT.ID = TRUCK.Shpmt_ID
JOIN ORIGIN ON SHPMT.Origin_Abbr = ORIGIN.Origin_Abbr
WHERE ORIGIN.Typ_Cd = 'BRWY'
AND SHPMT.Shp_Tsp IS NULL
GROUP BY SHPMT.Origin_Abbr, SHPMT.Sched_Tsp
having COUNT(TRUCK.Truck_ID) > (COUNT(SHPMT.ID) - COUNT(TRUCK.TRUCK_ID))
ORDER BY SHPMT.Origin_Abbr, SHPMT.Sched_Tsp;

但我无法确定这是否能为您提供所需的数据。这是简单的DEMO