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
ВЫБРАТЬ БЕЗ ПОВТОРОВ карта
ИЗ карты
ГДЕ card_status В (Decline, Stolen, Card OK)
ВЫБРАТЬ КОЛИЧЕСТВО( БЕЗ ПОВТОРОВ карта)
ИЗ журнал изменения
ГДЕ (AMND_STATE = 'A') и (card_status <> 'Card OK')
-- Обновление минимальным кодом:
UPDATE Human
SET car_id = UniqCar.min_id
FROM Human
INNER JOIN Car ON Human.car_id = Car.car_id
INNER JOIN (SELECT MIN(car_id) AS min_id, model
FROM Car AS CarMini
GROUP BY model) AS UniqCar ON UniqCar.model = Car.model
-- Удаление лишних машин:
DELETE FROM Car
WHERE Car.car_id <> (SELECT MIN(CarOff.car_id) FROM Car AS CarOff WHERE CarOff.model = Car.model)
team_id = "+ textBox2.Text +",
Если textBox2.Text
пустой, будет ошибка.строка формируется нормально, но всё равно ошибка
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
PIVOT
. Делал недавно.DECLARE @UserInfo TABLE ( user_id INT, meta_key VARCHAR(100), meta_value VARCHAR(100) )
INSERT @UserInfo VALUES ( 1, 'gender', 'male' ),
( 1, 'age', '21' ),
( 2, 'gender', 'female' ),
( 2, 'age', '23' ),
( 3, 'gender', 'genderqueer' ),
( 4, 'age', '25' ),
( 4, 'location', 'toster' ),
( 5, 'gender', 'male' ),
( 5, 'age', '27' ),
( 6, 'gender', 'female' ),
( 6, 'age', '29' )
SELECT user_id, gender, age, location
FROM ( SELECT * FROM @UserInfo ) AS UI
PIVOT ( MAX( meta_value ) FOR meta_key IN (gender, age, location )) AS PT
WHERE user_id BETWEEN 2 AND 5
ORDER BY user_id
user_id gender age location
2 female 23 NULL
3 genderqueer NULL NULL
4 NULL 25 toster
5 male 27 NULL
(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