时间戳

首页 > 解决方案 > 结果为左连接条件的情况

sql - 结果为左连接条件的情况

问题描述

表格1

Staff   category hours cost
1     Cleaning   5    20
1     Scrubbing  6    30
1     Cleaning   8    40
2     Scrubbing  4    30

表 2

`

Staff  type vendor category 
 1      part  A Cleaning
 1      full  b Cleaning 
 1      full  c Scrubbing 
...’

为了加入这两个表，我添加了一个新的 col“类型”：

Case when table1.hours=8
Then ”full”
Else ”part”
End as “type”

From table1 
Left join table2 on type=table2.type
And Table 1.staff =table2.staff

而Table1.category=table 2.category

我想要的结果是

Staff  category   Cost vendor type
  1      Cleaning  20  A.     Part
  1      Cleaning  40  B.     Full
  1      Scrubbing 30  C.    Part

但是 type=table2.type 条件不起作用所以它变成了

Staff  category   Cost vendor type
  1      Cleaning  20  A.     Part
  1      Cleaning  20  B.     part
  1      cleaning  40. A      Full
  1.      Cleaning 40  B.     Full

标签： sqlgoogle-bigquery

解决方案

如果我不明白，您需要一个在 table1 中创建列然后在条件 table1.category = table2.category 和 table1.type = table2.type 上与 table2 连接的实现。如果是这种情况，您可以尝试以下方法：

SELECT t1.Staff, t1.category, t1.cost, table2.vendor, t1.type
FROM (
  SELECT *, CASE WHEN table1.hours = 8 THEN "full" ELSE "part" END as type
  FROM table1
) t1
INNER JOIN table2
ON t1.category = table2.category
AND t1.type = table2.type
AND t1.staff = table2.staff

推荐阅读