我在下面有两个SQL表,我想加入两个表格。然后,我想计算以下设备之类的设备的警报号码。但是我想在某个日期范围内执行此操作(对于date2- date6之间的审查)。
我尝试写一个SQL句子,但它不起作用。我该怎么做?
第一表:
Message Date ID
Alert date1 1
Alert date2 2
Alert date3 3
Alert date4 1
Alert date5 1
Alert date6 2
Alert date7 3
第二个表:
Device ID
PC1 1
PC2 2
PC3 3
PC1 1
PC1 1
PC2 2
PC3 3
我的最后桌子:
Message Device Count
Alert PC1 2
Alert PC2 2
Alert PC3 1
我的sql句子:
select table1.message,table1.date, table2.device
Count (table2.device)
From table1 INNER JOIN table1
ON table1.id = table2.id
Where DATEDIFF(DAY,TimeStamp,GETDATE()) between date2 and date6
Group By table2.device
BEGIN TRAN
CREATE TABLE #Table(_Message VARCHAR(100), _Date VARCHAR(100),ID INT)
CREATE TABLE #Table1(_Device VARCHAR(100),ID INT)
INSERT INTO #Table(_Message , _Date ,ID )
SELECT 'Alert','date1',1 UNION ALL
SELECT 'Alert','date2',2 UNION ALL
SELECT 'Alert','date3',3 UNION ALL
SELECT 'Alert','date4',1 UNION ALL
SELECT 'Alert','date5',1 UNION ALL
SELECT 'Alert','date6',2 UNION ALL
SELECT 'Alert','date7',3
INSERT INTO #Table1(_Device ,ID )
SELECT 'PC1',1 UNION ALL
SELECT 'PC2',2 UNION ALL
SELECT 'PC3',3 UNION ALL
SELECT 'PC1',1 UNION ALL
SELECT 'PC1',1 UNION ALL
SELECT 'PC2',2 UNION ALL
SELECT 'PC3',3
SELECT _Message , _Device,COUNT(*) [COUNT]
FROM #Table
JOIN
(
SELECT ID,_Device
FROM #Table1
GROUP BY ID,_Device
)A ON A.ID = #Table.ID
WHERE _Date BETWEEN 'date2' AND 'date6'
GROUP BY _Message , _Device
ROLLBACK TRAN