select count(*) from systemMessage where (userid,avid) in (
select top 10 userid,avid from ( select distinct userid,avid from Interactive where interResult is not null and avid is not null and convert(varchar(10),interdate,120)=convert(varchar(10),getdate(),120) ) i order by newid() )
------解决方案--------------------
select count(*) from systemMessage where (userid,avid) in (
select top 10 userid,avid from ( select distinct userid,avid from Interactive where interResult is not null and avid is not null and convert(varchar(10),interdate,120)=convert(varchar(10),getdate(),120) ) i order by newid() )
sqlserver没这种语法，换exists吧
------解决方案--------------------
- SQL code
SELECT COUNT(*) FROM systemMessage a WHERE EXISTS ( SELECT 1 FROM ( SELECT TOP 10 userid , avid FROM ( SELECT DISTINCT userid , avid FROM Interactive WHERE interResult IS NOT NULL AND avid IS NOT NULL AND CONVERT(VARCHAR(10), interdate, 120) = CONVERT(VARCHAR(10), GETDATE(), 120) ) i ORDER BY NEWID() ) b WHERE a.userid = b.userid AND a.avid = b.avid )