SELECT *
FROM
(
SELECT
t1.*
,t2.*
(
(CASE WHEN t1.val1=t2.val1 THEN 1 ELSE 0 END)
+ (CASE WHEN t1.val2=t2.val2 THEN 1 ELSE 0 END)
+ (CASE WHEN t1.val3=t2.val3 THEN 1 ELSE 0 END)
) AS checkSum
FROM t1
INNER JOIN t2
ON 1=1
) AS t1
WHERE checkSum >=2
SELECT *
FROM
(
SELECT Employee,
Department,
Salary,
ROW_NUMBER() OVER(PARTITION BY Department ORDER BY salary DESC) AS rn
FROM table
) AS t1
WHERE rn <= 3SELECT ProgramId,
Subject,
StatusDate
FROM
(
SELECT programs.Id AS ProgramId,
subjects.Name AS Subject,
status.Date AS StatusDate,
status.Id AS StatusId,
ROW_NUMBER() OVER(PARTITION BY programs.Id ORDER BY status.Date DESC, status.Id DESC) AS rn
FROM programs
INNER JOIN status
ON programs.Id=status.ProgramId
INNER JOIN subjects
ON programs.SubjectId=subjects.Id
) AS t1
WHERE rn=1
AND StatusId=3 -- 3 - айдишка статуса 'заблокировано' в справочнике статусов
ORDER BY StatusDate DESC-- Пишем телефоны в пустые значения
UPDATE table AS t1
SET t1.Phone=t2.Phone
FROM table AS t1
INNER JOIN table AS t2
ON t1.FIO=t2.FIO
AND t1.phone IS NULL
AND t2.phone IS NOT NULL
-- Удаляем лишнее
DELETE
FROM table
WHERE Id NOT IN
(
SELECT Id
FROM
(
SELECT MAX(Id) AS id, FIO, Phone
FROM table
GROUP BY FIO,
Phone
) AS t1
)SELECT (CASE
WHEN stat=0
THEN 'работа не начата'
WHEN stat=1
THEN 'работа идет'
WHEN stat=2
THEN 'работа выполнена'
END) AS statStr
FROM tableИли нужно создавать две таблицы - поручения и статусы. И делать запрос на основе этих таблиц?
SELECT t1.date,
t2.income,
t3.outcome
FROM
(
SELECT date
FROM payments
UNION
SELECT date
FROM cash
) AS t1
LEFT JOIN
(
SELECT date,
SUM(income) AS income
FROM payments
GROUP BY date
) AS t2
ON t1.date=t2.date
LEFT JOIN
(
SELECT date,
SUM(summa) AS outcome
FROM payments
GROUP BY date
) AS t3
ON t1.date=t3.date[DataType(DataType.Currency, ErrorMessage = "Must be a Deciaml!")]
public float Price { get; set; }