时间戳

首页 > 解决方案 > 如何使用标准 SQL BIG Query 语法在不重复的情况下重写它？

sql - 如何使用标准 SQL BIG Query 语法在不重复的情况下重写它？

问题描述

将时间条目表中的两个表合并到用户表中。目前使用大查询标准 SQL。列 id 应该为每个条目有 1 个唯一 id，但它会提取多个相同的 id 如何将 LEFT Join 与 UNION ALL 或 UNION DISTINCT 一起使用？

--*** Gives Duplications for some reason *** 
SELECT outer_e.hours, outer_e.id, outer_e.updated_at, outer_e.spent_date, u.first_name, u.is_active, u.id AS user_id, u.weekly_capacity FROM
  (SELECT e.id, MAX(e.updated_at) AS updated_at FROM `harvest-experiment.harvest.time_entries` AS e
  GROUP BY e.id LIMIT 1000) AS inner_e
LEFT JOIN `harvest-experiment.harvest.time_entries` AS outer_e
ON inner_e.id = outer_e.id AND inner_e.updated_at = outer_e.updated_at
LEFT JOIN `harvest-experiment.harvest.users` AS u
ON outer_e.user_id = u.id

标签： sqlgoogle-bigquery

解决方案

我的 SELECT 关键字旁边缺少DISTANT关键字，这样做似乎可以解决视图中的重复问题。

__***** Current Solution *****———
--*** Returns a Left Joined Table of `time entries` and `users` *** 
SELECT DISTINCT outer_e.hours, outer_e.id, outer_e.updated_at, outer_e.spent_date, outer_e.created_at, outer_e.client_id, u.is_admin, u.first_name, u.is_active, u.id AS user_id, u.weekly_capacity, client.name FROM
  (SELECT  e.id, MAX(e.updated_at) AS updated_at FROM `harvest-experiment.harvest.time_entries` AS e 
  GROUP BY e.id LIMIT 1000) AS inner_e
LEFT JOIN `harvest-experiment.harvest.time_entries` AS outer_e
ON inner_e.id = outer_e.id AND inner_e.updated_at = outer_e.updated_at
LEFT JOIN `harvest-experiment.harvest.users` AS u
ON outer_e.user_id = u.id

推荐阅读