DELETE dbo.Корреспонденция
FROM dbo.Корреспонденция
INNER JOIN dbo.Отправители ON dbo.Корреспонденция.код_отправителя = Отправители.код_отправителя
WHERE (LEN( контактный_телефон ) = 11 )
AND (SUBSTRING( контактный_телефон 1, 2 ) IN ( '80', '81', '82', '83', '84',
'85', '86', '87', '88', '89' ))
AND (SUBSTRING( контактный_телефон 2, 1 ) = SUBSTRING( контактный_телефон 4, 1 ))
INSERT INTO tableName (ImageColumn)
SELECT BulkColumn
FROM OpenRowset( Bulk 'image..Path..here', Single_Blob ) AS Image
DECLARE @Temp TABLE ( [Name] VARCHAR(100), [Event] INT, Gold INT, Silver INT, Bronze INT )
INSERT @Temp VALUES
( 'Илья', 1, 5, 3, 4 ),
( 'Алексей', 1, 6, 3, 7 ),
( 'Илья', 2, 3, 5, 6 ),
( 'Илья', 3, 8, 3, 1 ),
( 'Алексей', 2, 5, 1, 1 ),
( 'Алексей', 3, 3, 4, 2 )
SELECT E1.[Name],
E1.Gold event_1_gold, E1.Silver event_1_silver, E1.Bronze event_1_bronze,
E2.Gold event_2_gold, E2.Silver event_2_silver, E2.Bronze event_2_bronze,
E3.Gold event_3_gold, E3.Silver event_3_silver, E3.Bronze event_3_bronze
FROM @Temp AS E1
JOIN @Temp AS E2 ON E1.[Name] = E2.[Name]
JOIN @Temp AS E3 ON E1.[Name] = E3.[Name]
WHERE E1.[Event] = 1
AND E2.[Event] = 2
AND E3.[Event] = 3
DECLARE @Temp TABLE( [Name] VARCHAR(1000), db_size VARCHAR(1000), [owner] VARCHAR(1000),
[dbid] VARCHAR(1000), created VARCHAR(1000), [status] VARCHAR(1000),
[compatibility_level] VARCHAR(1000))
INSERT INTO @Temp EXECUTE sp_helpdb
SELECT TOP (5) [Name], db_size AS [Size]
FROM @Temp
ORDER BY CAST( REPLACE( db_size, 'MB', '' ) AS REAL ) DESC
SELECT SUBSTRING( nazv_spec, 0, CHARINDEX( '-', Group_number, 1)) AS Spec, COUNT(*) AS CountGroup
FROM studenti_grupp
GROUP BY SUBSTRING( nazv_spec, 0, CHARINDEX( '-', Group_number, 1))
ORDER BY 2 DESC
INSTEAD OF
, то есть до (вместо) операции.FOR EACH ROW
.USE Testus
GO
DROP TABLE IF EXISTS Study
CREATE TABLE Study ( Grup_ID INT, Subj_ID INT )
INSERT Study VALUES
( 1, 1 ), ( 1, 2 ), ( 1, 3 ), ( 1, 5 ),
( 2, 2 ), ( 2, 3 ), ( 2, 4 ), ( 3, 5 ),
( 3, 7 )
DROP TABLE IF EXISTS Progress
CREATE TABLE Progress ( Stud_ID INT, Grup_ID INT, Subj_ID INT, [Оценка] INT)
INSERT Progress VALUES
( 1, 1, 1, 3 ), ( 1, 1, 2, 5 ), ( 1, 1, 3, 5 ),
( 2, 1, 1, 4 ), ( 2, 1, 2, 5 ), ( 2, 1, 3, 2 ),
( 1, 2, 1, 3 ), ( 1, 2, 2, 5 ), ( 1, 2, 3, 5 ),
( 2, 2, 1, 4 ), ( 2, 2, 2, 5 ), ( 2, 2, 3, 2 ),
( 1, 3, 1, 3 ), ( 1, 3, 2, 5 ), ( 1, 3, 7, 5 ),
( 2, 3, 1, 4 ), ( 2, 3, 2, 5 ), ( 2, 3, 7, 2 )
-- Проверка все ли оценки возможны
SELECT Progress.Stud_ID, Study.Grup_ID, Study.Subj_ID, [Оценка]
FROM Progress
LEFT OUTER JOIN Study
ON Progress.Grup_ID = Study.Grup_ID AND Progress.Subj_ID = Study.Subj_ID
DROP TABLE IF EXISTS ProgressClear
CREATE TABLE ProgressClear ( Stud_ID INT, Grup_ID INT, Subj_ID INT, [Оценка] INT)
INSERT ProgressClear
SELECT Progress.Stud_ID, Study.Grup_ID, Study.Subj_ID, [Оценка]
FROM Progress
INNER JOIN Study
ON Progress.Grup_ID = Study.Grup_ID AND Progress.Subj_ID = Study.Subj_ID
SELECT * FROM ProgressClear
Stud_ID Grup_ID Subj_ID Оценка
1 1 1 3
1 1 2 5
1 1 3 5
2 1 1 4
2 1 2 5
2 1 3 2
1 2 2 5
1 2 3 5
2 2 2 5
2 2 3 2
1 3 7 5
2 3 7 2
DROP TABLE IF EXISTS ProgressClear
CREATE TABLE ProgressClear ( Stud_ID INT, Grup_ID INT, Subj_ID INT, [Оценка] INT)
GO
CREATE TRIGGER OnlyPure
ON ProgressClear INSTEAD OF INSERT
AS
DECLARE @RC INT
SELECT @RC = COUNT(*) FROM inserted
INSERT ProgressClear
SELECT inserted.*
FROM inserted
INNER JOIN Study
ON inserted.Grup_ID = Study.Grup_ID AND inserted.Subj_ID = Study.Subj_ID
IF @@ROWCOUNT < @RC RAISERROR( 'Оценка не соответствует учебному плану.', 16, 10 )
GO
INSERT ProgressClear
SELECT * FROM Progress
SELECT * FROM ProgressClear
DECLARE @VAL TABLE ( Ident INT, A INT, B INT, val INT );
INSERT @VAL VALUES
( 1, 1, 1, 200 ),
( 2, 1, 1, 250 ),
( 3, 1, 1, 180 ),
( 4, 1, 2, 15 ),
( 5, 1, 2, 10 );
SELECT *
FROM @VAL
INNER JOIN ( SELECT MAX(Ident) AS MIdent
FROM @VAL
GROUP BY A, B ) AS MaxIndent
ON MaxIndent.MIdent = Ident
Source, Campaign, Ad
— это составной ключ (для source any — внешний).UNION ALL
по всем таблицам, а полученные результат группируете по Source, Campaign, Ad
.DECLARE @SA TABLE ( [Date] DATE, Campaign VARCHAR(100), Ad VARCHAR(100),
Impression INT, Click INT, Cost MONEY );
INSERT @SA VALUES
('2022-11-08', 'Vasya', 'C', 12, 1, 1),
('2022-11-08', 'Vasya', 'C', 12, 1, 1),
('2022-11-08', 'A', 'C', 12, 1, 1),
('2022-11-08', 'A', 'C', 12, 1, 1),
('2022-11-08', 'B', 'A', 12, 1, 1),
('2022-11-08', 'B', 'A', 12, 1, 1),
('2022-10-08', 'Vasya', 'C', 12, 1, 1);
DECLARE @SB TABLE ( [DateTime] DATETIME, Campaign VARCHAR(100), Ad VARCHAR(100),
Impression INT, Click INT, Cost MONEY );
INSERT @SB VALUES
('2022-11-06 12:10:00.000', 'Random', 'V', 12, 1, 1),
('2022-11-06 12:10:00.000', 'Random', 'V', 12, 1, 1),
('2022-11-07 12:10:00.000', 'H', 'A', 12, 1, 1),
('2022-11-07 12:10:00.000', 'H', 'A', 12, 1, 1),
('2022-11-08 12:10:00.000', 'K', 'C', 12, 1, 1),
('2022-11-08 12:10:00.000', 'K', 'C', 12, 1, 1),
('2022-11-06 12:10:00.000', 'Random', 'V', 12, 2, 2);
DECLARE @Sany TABLE ( [Date] DATE, [Source] VARCHAR(100), Campaign VARCHAR(100), Ad VARCHAR(100),
Install INT, Purchase MONEY );
INSERT @Sany VALUES
('2022-11-08', 'Source A', 'Vasya', 'C', 1, 1),
('2022-11-08', 'Source A', 'Vasya', 'C', 1, 1),
('2022-11-08', 'Source A', 'A', 'C', 1, 1),
('2022-11-08', 'Source A', 'A', 'C', 1, 1),
('2022-11-08', 'Source A', 'B', 'A', 1, 1),
('2022-11-08', 'Source A', 'B', 'A', 1, 1),
('2022-11-06', 'Source B', 'Random', 'V', 1, 1),
('2022-11-06', 'Source B', 'Random', 'V', 1, 1),
('2022-11-07', 'Source B', 'H', 'A', 1, 1),
('2022-11-08', 'Source A', 'Vasya', 'C', 3, 3),
('2022-11-08', 'Source A', 'Vasya', 'C', 1, 1),
('2022-11-08', 'Source A', 'Vasya', 'C', 1, 1),
('2022-11-11', 'Source A', 'Vasya', 'C', 2, 2),
('2022-11-13', 'Source C', 'AAAA', 'B', 2, 2);
SELECT [Source], Campaign, Ad,
SUM( Click ) AS SumClick, SUM( Cost ) AS SumCost,
SUM( Install ) AS SumInstall, SUM( Purchase) AS SumPurchase
FROM (
SELECT 'Source A' AS [Source], Campaign, Ad, Click, Cost,
CAST(NULL AS INT) AS [Install], CAST(NULL AS MONEY) AS Purchase
FROM @SA
UNION ALL
SELECT 'Source B' AS [Source], Campaign, Ad, Click, Cost,
CAST(NULL AS INT) AS [Install], CAST(NULL AS MONEY) AS Purchase
FROM @SB
UNION ALL
SELECT [Source], Campaign, Ad, NULL, NULL, [Install], Purchase
FROM @Sany
) AS Common
GROUP BY [Source], Campaign, Ad
DECLARE @Table TABLE ( [Column] NVARCHAR(50))
INSERT @Table VALUES
('46546546'),
('45.04545'),
('4545.5454'),
('5.0'),
('545.0'),
('0.0'),
('0.025'),
(NULL)
SELECT ISNULL(FORMAT( CAST( [Column] AS FLOAT ), '', 'en-US' ), 'sale')
FROM @Table
46546546
45.04545
4545.5454
5
545
0
0.025
sale
DECLARE @Table
TABLE ( [Column] NVARCHAR(50),
[ColumnNeed] AS ISNULL(FORMAT( CAST( [Column] AS FLOAT ), '', 'en-US' ), 'sale'));
INSERT @Table VALUES
('46546546'),
('45.04545'),
('4545.5454'),
('5.0'),
('545.0'),
('0.0'),
('0.025'),
(NULL)
SELECT ColumnNeed
FROM @Table
SELECT *
FROM @Words AS Wrd
JOIN ( SELECT LEFT( Word1, 4 ) AS Word4
FROM @Words
GROUP BY LEFT( Word1, 4 )
HAVING COUNT(*) > 1 ) AS Wrd4
ON Wrd4.Word4 = LEFT( Word1, 4 )
JOIN ( SELECT LEFT( Word2, 2 ) AS Word2
FROM @Words
GROUP BY LEFT( Word2, 2 )
HAVING COUNT(*) > 1 ) AS Wrd2
ON Wrd2.Word2 = LEFT( Wrd.Word2, 2 )
cheque_id
и ROUND(qty,2)
и соответствующим HAVING
, а потом соединяете его с основной таблицей.DECLARE @items_sales TABLE ( [Date] DATE, cheque_id INT, item INT, position INT, qty REAL )
INSERT @items_sales VALUES
('2022-09-12', 1, 1, 1, 0.237),
('2022-09-12', 1, 2, 2, 0.238),
('2022-09-12', 1, 3, 3, 0.322),
('2022-09-12', 1, 4, 4, 0.239),
('2022-09-12', 2, 1, 1, 0.210),
('2022-09-12', 2, 2, 2, 0.210),
('2022-09-12', 3, 5, 1, 0.240),
('2022-09-12', 4, 9, 1, 0.322),
('2022-09-12', 4, 8, 2, 0.322)
SELECT *
FROM @items_sales AS Iteams
JOIN ( SELECT cheque_id, ROUND( qty, 2) AS RoundQty
FROM @items_sales
GROUP BY cheque_id, ROUND( qty, 2)
HAVING COUNT(*) >= 3 ) AS IteamCount
ON IteamCount.cheque_id = Iteams.cheque_id
AND IteamCount.RoundQty = ROUND( qty, 2)
ORDER BY [Date], Iteams.cheque_id, item