时间戳

首页 > 解决方案 > 如何使用子查询或旋转来反转查询选择

sql - 如何使用子查询或旋转来反转查询选择

问题描述

我有如下 SQL 查询

SELECT Count(L.id) As Count, U.name As Name, L.action As Action
FROM tabel1 L LEFT JOIN tabel2 U ON L.uId = U.id
WHERE L.date >= '1/1/2018 12:00:00 AM' AND L.date <= '11/2/2019 12:00:00 AM' AND L.nId IN (18161, 18156, 4924, 18129, 18073, 18070, 18140, 2588, 18141, 18139, 18144, 18143, 18142, 6741, 16796, 3333, 6364, 6367, 6370, 6373, 17991, 7812, 11148, 7799, 11074) 
group by U.name, L.action
ORDER BY U.name, L.action

其结果如下

Count   Name        Action
--------------------------     
24      User 1      Create
31      User 1      Edit
252     User 2      Create
48      User 2      Edit
257     User 3      Create
29      User 3      Edit
3       User 4      Create
216     User 5      Create
287     User 5      Edit
256     User 6      Edit
9       User 6      Create
1       User 6      Delete
101     User 7      Edit

我需要得到如下结果

Name      Create      Edit      Delete
--------------------------------------
User 1    24          31        0
User 2    252         48        0
User 3    257         29        0
User 4    3           0         0
User 5    216         287       0
User 6    256         9         1
User 7    0           101       0

我怎样才能做到这一点？

标签： sqlsql-serverdatabase

解决方案

只需使用条件聚合：

SELECT U.name As Name, 
       SUM(CASE WHEN l.Action = 'Create' THEN 1 ELSE 0 END) as num_created,
       SUM(CASE WHEN l.Action = 'Edit' THEN 1 ELSE 0 END) as num_edited,
       SUM(CASE WHEN l.Action = 'Delete' THEN 1 ELSE 0 END) as num_deleted
FROM tabel1 L LEFT JOIN
     tabel2 U
     ON L.uId = U.id
WHERE L.date >= '2018-01-01' AND
      L.date <= '2019-02-11' AND
      L.nId IN (18161, 18156, 4924, 18129, 18073, 18070, 18140, 2588, 18141, 18139, 18144, 18143, 18142, 6741, 16796, 3333, 6364, 6367, 6370, 6373, 17991, 7812, 11148, 7799, 11074) 
GROUP BY U.name
ORDER BY U.name;

请注意，我还简化了日期比较并引入了 ISO 标准格式。但是，我不确定较晚的日期是 2019-02-11 还是 2019-11-02。

推荐阅读