遇ORA-01427错误：求特定时段内各attribute值的计数方法

First, let's break down why you're hitting the ORA-01427 error:

• Your subquery select distinct attribute from table2 returns multiple rows (since there's more than one unique attribute in table2).
• Using the = operator expects a single value from the subquery, which causes the mismatch.

To get the count for each attribute in your target time range, here are two straightforward solutions:

Solution 1: Use IN with GROUP BY #

This approach filters table1 to only include attributes present in table2, then groups by attribute to get individual counts:

SELECT 
  attribute, 
  COUNT(*) AS attribute_count
FROM table1
WHERE 
  TO_CHAR(timestamp,'yyyymmddhh24') = TO_CHAR(sysdate - 1/24,'yyyymmddhh24')
  AND attribute IN (SELECT DISTINCT attribute FROM table2)
GROUP BY attribute;

Solution 2: Join with Table2 (Using DISTINCT to Avoid Duplicates) #

If you prefer using a join instead of a subquery, this works too. We join table1 with the distinct attributes from table2, then group:

SELECT 
  t1.attribute, 
  COUNT(*) AS attribute_count
FROM table1 t1
JOIN (SELECT DISTINCT attribute FROM table2) t2
  ON t1.attribute = t2.attribute
WHERE 
  TO_CHAR(timestamp,'yyyymmddhh24') = TO_CHAR(sysdate - 1/24,'yyyymmddhh24')
GROUP BY t1.attribute;

Bonus Tip: Optimize Timestamp Filtering #

Using TO_CHAR on the timestamp column prevents the database from using any indexes on that column. A better, faster approach is to filter directly on the timestamp range:

WHERE 
  timestamp >= TRUNC(sysdate - 1/24, 'HH24')
  AND timestamp < TRUNC(sysdate, 'HH24')

This way, if there's an index on timestamp, the query will leverage it for better performance.

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