SELECT *
FROM
(
SELECT Employee,
Department,
Salary,
ROW_NUMBER() OVER(PARTITION BY Department ORDER BY salary DESC) AS rn
FROM table
) AS t1
WHERE rn <= 3
SELECT 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
Или нужно создавать две таблицы - поручения и статусы. И делать запрос на основе этих таблиц?
UPDATE Users
SET language=:language
WHERE username = :username
SELECT t1.*,
round(sum(count_id1) / sum(count_id2) * 100, 1) as percent
FROM
(
select
...
count(table.id1) as count_id1,
count(table.id2) as count_id2
from table
...
) AS t1
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; }
|Veokite rentimine ja kasutusrent (77121)
`sektor` LIKE '%entimine ja kasutusren%'
DECLARE @minValue INT, @maxValue INT;
IF (@v1 < @v2)
BEGIN
SET @minValue = @v1;
SET @maxValue = @v2;
END
ELSE
BEGIN
SET @minValue = @v2;
SET @maxValue = @v1;
END
SELECT *
FROM table
WHERE v BETWEEN @minValue AND @maxValue