Константин Цветков

SELECT (SELECT COUNT(*)
          FROM sp
          WHERE ([some] = 1)) AS SomeCount,
       (SELECT COUNT(*)
          FROM sp
          WHERE ([some] = 1) AND (noone = 1)) AS NooneCount

SELECT `p`, * FROM catalog_ads  
WHERE status = 1 AND category_id = 1 AND moderation = 0 AND ad_diamond = 0 AND ad_vip = 0 
UNION  
SELECT `v_p`, *  FROM catalog_ads
WHERE status = 1 AND category_id = 1 AND moderation = 0 AND ad_vip = 1
UNION  
SELECT `d_p`, *  FROM catalog_ads
WHERE status = 1 AND category_id = 1 AND moderation = 0 AND ad_diamond = 1 
ORDER BY 1 DESC

SELECT items.item_id AS Item, FirstDoc.op_type AS FirstDocOper, LastDoc.op_type AS LastDocOper
  FROM items 
    INNER JOIN documents AS FirstDoc ON items.firstDocument = FirstDoc.docid 
    INNER JOIN documents AS LastDoc ON items.lastDocument = LastDoc.docid

SELECT Task.keyvalue, Task.exception, TaskComplete.completed
  FROM Task 
    INNER JOIN Task AS TaskComplete ON Task.keyvalue = TaskComplete.keyvalue
  WHERE (NOT (Task.exception IS NULL)) AND (NOT (TaskComplete.completed IS NULL))

SELECT t.id, t.country, t.salary 
  FROM t
    INNER JOIN 
      ( SELECT country, max(salary) AS max_salary FROM t GROUP BY country ) tm
        ON t.country = tm.country AND t.salary = tm.max_salary

Проверка.

SELECT t.id, dt, curr,  rate 
  FROM t 
   INNER JOIN
      ( SELECT id, COUNT(*)
        FROM t
        GROUP BY id
        HAVING COUNT(*) > 1 ) t_count ON t.id = t_count.id

SELECT marque, COUNT(*) AS count_car
  FROM CAR
    INNER JOIN DEL ON id_care = CAR.id
  GROUP BY marque

Существует представление

Поскольку вы не указали СУБД, то повезёт если оная умеет делать индексированные представления. К представлению добавляется нужный вам индекс. Также такое представление вычисляется и результат сохраняется.

SELECT * FROM games JOIN lvl ON … WHERE lvl=1
UNION
SELECT * FROM games JOIN lvl_hard ON … WHERE lvl=2

С учётом уточнения интерпретации данных, а также допуская что метки только последовательно добавляются, предлагаю использовать триггер на добавление. Создаёте таблицу периодов.

(Tag NVARCHAR(60), [StartTime] datetime, [EndTime] datetime)

В триггере: если метка более 2-х минут от предыдущей (определяется легко: последняя - 1), то добавляем строку и заносим в StartTime, если нет — то в EndTime.

Или процедура:

CREATE PROCEDURE DowntimeCalculatePeriod
AS
  DECLARE @P TABLE ( ID INT IDENTITY, 
                     [Tag] NVARCHAR(60), [StartTime] DATETIME, [EndTime] DATETIME)
  DECLARE @DG1 NVARCHAR(60), @DT1 DATETIME
  DECLARE @DG0 NVARCHAR(60) = CHAR(0x19), @DT0 DATETIME = '2020'
  DECLARE @ID INT = -1
  DECLARE DowntimeCalc CURSOR FOR
    SELECT Tag, [DateTime] FROM Downtime ORDER BY 1, 2
  OPEN DowntimeCalc
  FETCH NEXT FROM DowntimeCalc INTO @DG1, @DT1
  WHILE @@FETCH_STATUS = 0 BEGIN
    IF ( @DG1 <> @DG0 ) OR (( @DG1 = @DG0 ) AND ( DATEDIFF( ss, @DT0, @DT1) > 90 )) BEGIN
      INSERT INTO @P ([Tag], [StartTime]) VALUES ( @DG1, @DT1 )
      SET @ID = SCOPE_IDENTITY()
    END ELSE BEGIN
      UPDATE @P SET [EndTime] = @DT1 WHERE ID = @ID
    END
    SET @DG0 = @DG1; SET @DT0 = @DT1
    FETCH NEXT FROM DowntimeCalc INTO @DG1, @DT1
  END
  CLOSE DowntimeCalc
  DEALLOCATE DowntimeCalc
  SELECT [Tag], [StartTime], [EndTime] FROM @P 
    WHERE NOT [EndTime] IS NULL 
    ORDER BY 1, 2
GO
--
EXECUTE DowntimeCalculatePeriod

MICROSOFT+ACCESS+PIVOT