php - 在 SELECT 里面

Question

是否可以在 SQL 中执行类似的操作？

SELECT SUM(jobRequirements.number) as total, 
    SUM(jobRequirements.number WHERE jobRequirements.type=1) as jobIT, 
    SUM(jobRequirements.number WHERE jobRequirements.type=2) as jobTourism FROM jobRequirements

我在这里也使用 where 子句进行内部联接，但是我是否必须对每个 jobRequirements.type 使用 where 子句进行单独查询，还是可以在一个查询中完成？

我的完整查询来自 Laravel，它看起来像这样:

$jobStats = DB::table('jobRequirements')
            ->join('jobs', 'jobRequirements.job', '=', 'jobs.id')
            ->join('types', 'jobRequirements.type', '=', 'types.id')
            ->join('users', 'jobs.user', '=', 'users.id')
            ->select(DB::raw('SUM(jobRequirements.number) as total, users.name'))
            ->where('jobRequirements.active', 1)
            ->orderBy('users.name', 'asc')
            ->groupBy('users.id')
            ->get();

有了这个，我得到了总数，但我需要按类型得到数字的总和。

更新

有了这个，稍微修改一下，来自@Terminus 的代码

SELECT SUM(jobReq.number) as total, users.name as user, types.name as type
FROM jobRequirements jobReq
INNER JOIN jobs j ON j.id = jobReq.job
INNER JOIN types t ON t.id = jobReq.type
INNER JOIN users u ON u.id = j.user
WHERE jobReq.active = 1
GROUP BY u.id, t.id
ORDER BY u.name ASC

我明白了:

total  |  user  |  type
------------------------
2      |  user1 |  type2
5      |  user2 |  type6
1      |  user1 |  type3
3      |  user2 |  type2

最后我需要的是:

       | type2 | type3  | type6 | total
---------------------------------------
user1  | 2     | 1      | 0     | 3
user2  | 3     | 0      | 5     | 8
total  | 5     | 1      | 5     | 11

Answer 1

SELECT SUM(jobRequirements.number) as total, 
    SUM(CASE WHEN jobRequirements.type=1 THEN jobRequirements.number ELSE 0 END) as jobIT, 
    SUM(CASE WHEN jobRequirements.type=2 THEN jobRequirements.number ELSE 0 END) as jobTourism 
FROM jobRequirements