如何传一个参数找出在标示表中存在3条或0记录的视频记录? 有查询示例SQL 谢谢SELECTGuidFROMPub_VideoWHER
如何传一个参数找出在标示表中存在3条或0记录的视频记录? 有查询示例SQL 谢谢
SELECT Guid
FROM Pub_Video
WHERE EXISTS
(SELECT Guid
FROM Pub_Marking
WHERE (MKGuid = Pub_Video.Guid)
GROUP BY Guid
HAVING (COUNT(Guid) = 1))
以上SQL是查询出
找出在标示表中存在1条记录的视频记录?
但是现在我想找出在标示表中存在0条的记录的话
我把(COUNT(Guid) = 1)改为(COUNT(Guid) = 0)
是不行的,但我又不想用Not Exists主查询
要怎么办呢?
也就是相当于,整条SQL只有查询的记录数的值是可变的,其他不可变
然后要实现查询:
找出在标示表中存在"0|1|2|3”条记录的视频记录?
谢谢
[解决办法]SELECT?????Guid
FROM?????????Pub_Video
WHERE?????EXISTS
??????????????????????????(SELECT?????Guid
????????????????????????????FROM??????????Pub_Marking
????????????????????????????WHERE??????(MKGuid?=?Pub_Video.Guid)
????????????????????????????GROUP?BY?Guid
????????????????????????????HAVING??????(COUNT(Guid)?<?1))
[解决办法]说实在的,我感觉用Not Exists就行了后面改下用HAVING (COUNT(Guid)>0))
[解决办法]子项可以继续加啊,有什么问题呢?
[解决办法]DECLARE @cnt INT
SET @cnt = 0--亦可传1,2,3,4......
SELECT a.[guid]
from Pub_Video a
LEFT JOIN Pub_Marking b
ON a.[guid] = b.[guid]
GROUP BY a.[guid]
HAVING (b.[guid]) = @cnt
[解决办法]表关系如此地简单:
select a.guid
from Pub_Video a
left join Pub_Marking b on a.guid=b.MKGuid
group by a.guid
having COUNT(b.guid) in (0,3)