问题:有两列。Column1(任务)和column2(用户)。列1可能有一些类似的数据(任务),列2可能有true和false,这取决于用户是否被分配到该任务。
我想要的是,在第一列中,相同的任务需要首先进行分组,并检查用户是否被分配到所有这些相同的任务,如果其中一个任务是空的,那么它应该返回false。
example : Table
Value |PrimaryOwnerId
_______________________________________________________________________
EVP-RP&P | null
Execution Coordinator | null
Execution Coordinator | 2
Inspection Lead | null
Instrument, Electrical, and Analyzer | null
Logistics | 1
Logistics | 1
Operations | null
Planning | 4
Output table should look like :
Value |PrimaryOwnerId
_______________________________________________________________________
EVP-RP&P | False
Execution Coordinator | False
Inspection Lead | False
Instrument, Electrical, and Analyzer | False
Logistics | True
Operations | False
Planning | True
我正在使用这个查询。
select CraftName AS value,
case
when PrimaryOwnerID is Null
Then 'true'
else 'flase'
End as PrimaryOwnerID
FROM EventTasks AS et
INNER JOIN EventMileStones AS em ON em.EventMileStoneID = et.EventMileStoneID
INNER JOIN LocationTasks AS lt ON lt.LocationTaskID = et.LocationTaskID
INNER JOIN Crafts AS c ON c.ID = lt.CraftID
WHERE ProjectID = 4
group by CraftName, PrimaryOwnerID
有谁能帮上忙吗?
发布于 2019-07-17 00:44:32
尽管您没有提到数据库名称,但以下逻辑将在大多数数据库中工作-
SELECT Value,
CASE
WHEN SUM(CASE WHEN PrimaryOwnerId IS NULL THEN 1 ELSE 0 END) > 0 THEN 'False'
ELSE 'True'
END
FROM your_table
GROUP BY Value
根据您的逻辑,Planning应该得到"True“,因为没有将null分配给此值,但您在示例输出数据中显示了"False”。
https://stackoverflow.com/questions/57061806
复制相似问题