如何在SQL中显示这样的输出？

Question

我有一张桌子，如下所示：

+----------------+-------+----------+---------+
| Name           | Model | system   | ItemTag |
+----------------+-------+----------+---------+
| Alarm Id       | T58   | ASC      |         |
+----------------+-------+----------+---------+
| Door Lock      | F48   | ASC      |         |
+----------------+-------+----------+---------+
| AlarmSounder   | T58   | ASC      |         |
+----------------+-------+----------+---------+
| Card Reader    | K12   | ASC      |         |
+----------------+-------+----------+---------+
| Magnetic Lock  | F48   | ASC      |         |
+----------------+-------+----------+---------+
| T2 Card Reader | K12   | ASC      |         |
+----------------+-------+----------+---------+
| Power   Supply | Null  | ASC      |         |
+----------------+-------+----------+---------+
| Battery         |  Null| ASC      |         |
+----------------+-------+----------+---------+

现在，我想像这样显示数据：

+-------------+-------+--------+--------+
| Name        | Model | system | count  |
+-------------+-------+--------+--------+
| Alarm       | T58   | ASC    | 2      |
+-------------+-------+--------+--------+
| Door Lock   | F58   | ASC    | 2      |
+-------------+-------+--------+--------+
| Card Reader | K12   | ASC    | 2      |
+-------------+-------+--------+--------+
|Power supply | Null   | ASC    | 1     |
+-------------+-------+--------+--------+
| Battery    | Null   | ASC    | 1     |
+-------------+-------+--------+--------+

如何在SQL中实现？

更新后的我还包括空列作为我的第二次更新。

Answer 1

您可以使用窗口函数：

SELECT Name, Model, system, cnt AS count 
FROM (SELECT *, COUNT(*) OVER(PARTITION BY Model) AS cnt,
            ROW_NUMBER() OVER(PARTITION BY Model ORDER BY ...) AS rn
      FROM your_tab) AS sub
WHERE rn = 1;

Rextester演示

请记住，您需要一个列来排序，因此(id/时间戳)应该用于获取组中的第一个值。

编辑：

因为我有与空列相关的不同名称。我怎么才能把它分开

SELECT Name, Model, system, cnt AS count 
FROM (SELECT *, COUNT(*) OVER(PARTITION BY Model) AS cnt,
            ROW_NUMBER() OVER(PARTITION BY Model ORDER BY id) AS rn
      FROM my_tab
      WHERE Model IS NOT NULL) AS sub
WHERE rn = 1
UNION ALL
SELECT Name, Model, system, 1
FROM my_tab
WHERE Model IS NULL;

RextesterDemo 2

Answer 2

您可以进行如下简单的查询

SELECT MIN(Name) Name, 
       Model, 
       system, 
       COUNT(*) [count]
  FROM yourtable
 GROUP BY Model, system

结果

Name        Model   system  count
Door Lock   F58     ASC     2
Card Reader K12     ASC     2
Alarm Id    T58     ASC     2

Answer 3

add 2025年的解决方案简化了，在一个步骤中计算了NULL和NULL，并为空行添加了一些逻辑：

SELECT Name, Model, system, 
   CASE WHEN Model IS NULL THEN 1 ELSE cnt END AS count 
FROM
 (
   SELECT *, 
      COUNT(*) OVER(PARTITION BY Model) AS cnt,
      ROW_NUMBER() OVER(PARTITION BY Model ORDER BY Name) AS rn
   FROM my_tab
 ) AS sub
WHERE rn = 1         -- one row per model 
   OR Model IS NULL; -- all rows for the NULL model