如何筛选同一设备与日期下存在不同Action的行？

Got it, let's work through this problem step by step. You're trying to filter your table so only rows where a specific Machine_N + Date pair has more than one distinct Action are kept. Let's break down why your initial HAVING COUNT attempt might have failed, then share two reliable solutions.

原数据表 #

┌─────────┬────────────────┬────────────────┐
│Machine_N│ Date           │ Action         │
├─────────┼────────────────┼────────────────┤
│ RS1     │ 2018-02-08     │ Reading        │
│ RS1     │ 2018-02-08     │ Referred       │
│ RS1     │ 2018-02-16     │ Reading        │
│ RS2     │ 2018-01-31     │ Reading        │
│ RS2     │ 2018-01-31     │ Referred       │
└─────────┴────────────────┴────────────────┘

为什么你的HAVING COUNT可能没生效 #

Chances are you tried grouping directly and selecting the rows, but that only returns the grouped pairs—not the original rows from the table. Or maybe you used COUNT(*) instead of COUNT(DISTINCT Action): if a group had duplicate Action values (which you don't have here, but it's a common pitfall), COUNT(*) would incorrectly flag it as having multiple actions.

解决方案1：使用窗口函数（推荐） #

Window functions let you calculate metrics for each group without collapsing the rows. Here, we'll add a column that counts distinct Actions per Machine_N + Date pair, then filter for pairs where that count is greater than 1:

SELECT Machine_N, Date, Action
FROM (
    SELECT 
        Machine_N, 
        Date, 
        Action,
        -- Count distinct actions in the same Machine_N + Date group
        COUNT(DISTINCT Action) OVER (PARTITION BY Machine_N, Date) AS distinct_action_count
    FROM your_table_name
) AS filtered_groups
WHERE distinct_action_count > 1;

解决方案2：子查询分组后关联 #

If window functions aren't available in your SQL dialect, you can first identify the valid Machine_N + Date pairs (those with multiple distinct actions), then join back to the original table to get the full rows:

SELECT t.Machine_N, t.Date, t.Action
FROM your_table_name t
INNER JOIN (
    -- Get only pairs with more than one distinct Action
    SELECT Machine_N, Date
    FROM your_table_name
    GROUP BY Machine_N, Date
    HAVING COUNT(DISTINCT Action) > 1
) AS valid_pairs
ON t.Machine_N = valid_pairs.Machine_N 
   AND t.Date = valid_pairs.Date;

期望结果 #

Both queries will return exactly the rows you want:

┌─────────┬────────────────┬────────────────┐
│Machine_N│ Date           │ Action         │
├─────────┼────────────────┼────────────────┤
│ RS1     │ 2018-02-08     │ Reading        │
│ RS1     │ 2018-02-08     │ Referred       │
│ RS2     │ 2018-01-31     │ Reading        │
│ RS2     │ 2018-01-31     │ Referred       │
└─────────┴────────────────┴────────────────┘

内容的提问来源于stack exchange，提问作者Ayoub Salhi