SELECT ID, MO_Date, Src_terminal_Id, Msg_Content
FROM CMPP2_Deliver a
WHERE Src_terminal_Id IN (
SELECT Src_terminal_Id
FROM CMPP2_Deliver
WHERE Src_terminal_Id = a.Src_terminal_Id and MO_Date BETWEEN '2008-7-2 0:0:0' AND '2008-7-2 15:0:0'
GROUP BY Src_terminal_Id
HAVING COUNT(Src_terminal_Id) > 1
)
ORDER BY Src_terminal_Id
FROM CMPP2_Deliver a
WHERE Src_terminal_Id IN (
SELECT Src_terminal_Id
FROM CMPP2_Deliver
WHERE Src_terminal_Id = a.Src_terminal_Id and MO_Date BETWEEN '2008-7-2 0:0:0' AND '2008-7-2 15:0:0'
GROUP BY Src_terminal_Id
HAVING COUNT(Src_terminal_Id) > 1
)
ORDER BY Src_terminal_Id
FROM CMPP2_Deliver a
WHERE Src_terminal_Id IN
(SELECT Src_terminal_Id FROM CMPP2_Deliver WHERE datepart(hh,MO_Date) BETWEEN 0 AND 15 --去掉AS B WHERE a.Src_terminal_Id = b.Src_terminal_Id
GROUP BY Src_terminal_Id
HAVING COUNT(Src_terminal_Id) > 1 )
ORDER BY Src_terminal_Id
FROM CMPP2_Deliver a
WHERE (Src_terminal_Id IN
(SELECT Src_terminal_Id
FROM CMPP2_Deliver as b
WHERE MO_Date BETWEEN '2008-7-2 0:0:0' AND '2008-7-2 15:0:0'
and a.Src_terminal_Id = b.Src_terminal_Id
GROUP BY Src_terminal_Id
HAVING COUNT(Src_terminal_Id) > 1))
ORDER BY Src_terminal_Id 把 as b 放到CMPP2_Deliver 后面
为什么 是不是错在 sql 以为 只要a表的 Src_terminal_Id和 查询里 的 Src_terminal_Id 相等就算符合条件?
FROM CMPP2_Deliver a
WHERE Src_terminal_Id IN (
SELECT Src_terminal_Id
FROM CMPP2_Deliver
WHERE Src_terminal_Id = a.Src_terminal_Id and MO_Date BETWEEN '2008-7-2 0:0:0' AND '2008-7-2 15:0:0'
GROUP BY Src_terminal_Id
HAVING COUNT(Src_terminal_Id) > 1
)
WHERE Src_terminal_Id = a.Src_terminal_Id and MO_Date BETWEEN '2008-7-2 0:0:0' AND '2008-7-2 15:0:0'
ORDER BY Src_terminal_Id
FROM CMPP2_Deliver
WHERE (Src_terminal_Id IN
(SELECT Src_terminal_Id
FROM CMPP2_Deliver
WHERE MO_Date BETWEEN '2008-7-2 0:0:0' AND '2008-7-2 15:0:0'
GROUP BY Src_terminal_Id
HAVING COUNT(Src_terminal_Id) > 1))
ORDER BY Src_terminal_Id
FROM CMPP2_Deliver
WHERE Src_terminal_Id = a.Src_terminal_Id and MO_Date BETWEEN '2008-7-2 0:0:0' AND '2008-7-2 15:0:0'
GROUP BY Src_terminal_Id
HAVING COUNT(Src_terminal_Id) > 1 查出了时间段的ID
但这个ID在以前可能还有数据
FROM CMPP2_Deliver
WHERE (Src_terminal_Id IN
(SELECT Src_terminal_Id
FROM CMPP2_Deliver
WHERE MO_Date BETWEEN '2008-7-2 0:0:0' AND '2008-7-2 15:0:0'
GROUP BY Src_terminal_Id
HAVING COUNT(Src_terminal_Id) > 1))
and
MO_Date BETWEEN '2008-7-2 0:0:0' AND '2008-7-2 15:0:0' ORDER BY Src_terminal_Id
SELECT ID, MO_Date, Src_terminal_Id, Msg_Content
FROM CMPP2_Deliver a
WHERE Src_terminal_Id IN (
SELECT Src_terminal_Id
FROM CMPP2_Deliver
WHERE Src_terminal_Id = a.Src_terminal_Id and MO_Date BETWEEN '2008-7-2 0:0:0' AND '2008-7-2 15:0:0'
GROUP BY Src_terminal_Id
HAVING COUNT(Src_terminal_Id) > 1
)
WHERE MO_Date BETWEEN '2008-7-2 0:0:0' AND '2008…上一个多+了一个条件哈
FROM CMPP2_Deliver a
inner join (SELECT Src_terminal_Id
FROM CMPP2_Deliver
WHERE MO_Date BETWEEN '2008-7-2 0:0:0' AND '2008-7-2 15:0:0' ) b
on a.Src_terminal_Id = b.Src_terminal_Id
GROUP BY Src_terminal_Id
HAVING COUNT(Src_terminal_Id) > 1
ORDER BY Src_terminal_Id
FROM CMPP2_Deliver a
WHERE Src_terminal_Id IN
(SELECT Src_terminal_Id FROM CMPP2_Deliver WHERE datepart(hh,MO_Date) BETWEEN 0 AND 15 --去掉AS B WHERE a.Src_terminal_Id = b.Src_terminal_Id
GROUP BY Src_terminal_Id
HAVING COUNT(Src_terminal_Id) > 1 )
ORDER BY Src_terminal_Id
FROM CMPP2_Deliver a
inner join (SELECT Src_terminal_Id
FROM CMPP2_Deliver
WHERE MO_Date BETWEEN '2008-7-2 0:0:0' AND '2008-7-2 15:0:0' ) b
on a.Src_terminal_Id = b.Src_terminal_Id
GROUP BY ID, MO_Date, Src_terminal_Id, Msg_Content
HAVING COUNT(Src_terminal_Id) > 1
ORDER BY Src_terminal_Id