Sqlplus中如何排除特定字符串结果及存在该字符串的ID所有行？

Let's tackle your two SQL questions one by one—they're related but cover different filtering scenarios, and SQL*Plus is just Oracle's command-line tool for running queries; the core logic lives in your SQL statement, not the client itself.

To filter out rows where a column includes a particular string, you'll use the NOT LIKE operator (for partial matches) or direct inequality (!=/<>) for exact matches.

For example, if you want to exclude any row where the activity column contains "Logged off", your query would look like this:

SELECT * 
FROM your_table_name 
WHERE activity NOT LIKE '%Logged off%';

• The % is a wildcard that matches any sequence of characters (including none), so this catches any row where "Logged off" appears anywhere in the activity value.
• If you only want to exclude rows where activity is exactly "Logged off" (no extra text), use activity != 'Logged off' instead.

Absolutely—this is a common requirement, and there are a few clean, efficient ways to do it. Using your example (removing all rows for IDs that ever had a "Logged off" activity), here are three reliable approaches:

• Using NOT IN with a subquery
  First, identify all IDs that have at least one "Logged off" entry, then exclude any rows associated with those IDs:

  SELECT * 
  FROM your_table_name 
  WHERE ID NOT IN (
      SELECT DISTINCT ID 
      FROM your_table_name 
      WHERE activity = 'Logged off'
  );
  

  Note: If the subquery returns any NULL values, NOT IN will fail to return results. Since we're selecting distinct IDs from valid activity records, this isn't a problem here, but it's a good edge case to remember.

• Using NOT EXISTS (best for large datasets)
  This checks for the absence of a "Logged off" entry for the same ID. Oracle optimizes NOT EXISTS well, especially if you have indexes on ID and activity:

  SELECT t.* 
  FROM your_table_name t
  WHERE NOT EXISTS (
      SELECT 1 
      FROM your_table_name t2
      WHERE t2.ID = t.ID 
        AND t2.activity = 'Logged off'
  );
  

• Using LEFT JOIN with IS NULL
  This joins your main table to a list of "bad" IDs (those with "Logged off") and keeps only rows where there's no match:

  SELECT t.* 
  FROM your_table_name t
  LEFT JOIN (
      SELECT DISTINCT ID 
      FROM your_table_name 
      WHERE activity = 'Logged off'
  ) bad_ids ON t.ID = bad_ids.ID
  WHERE bad_ids.ID IS NULL;
  

  This approach is intuitive and works across most SQL dialects, including Oracle.

All three queries will return exactly the result you want in your example: only the rows for cdl, since jjl has a "Logged off" entry and is excluded entirely.

内容的提问来源于stack exchange，提问作者John Lockhart