如何获取SQL Server中的联接表的计数？

debugcn 发表于 Dev

大山

我正在使用现有的sql server语句：

select 
    rs.record_StepID, rs.recordID, rs.stepTypeID, 
    rs.label, rs.assignedToUserID, rs.stepActivatedDate,
    si.scheduledInspectionID, 
    l.streetNo, l.streetName, l.unit, l.city, l.state, 
    l.postalCode, l.country, rt.categoryName 
from 
    Record_Steps as rs 
left join 
    Users as u on rs.assignedToUserID = u.userID 
left join 
    ScheduledInspections as si on rs.record_StepID = si.record_StepID 
left join 
    Records as r on r.recordID = rs.recordID 
left join 
    Locations as l on l.locationID = r.locationID 
left join 
    recordTypes as rt on r.recordTypeID = rt.recordTypeID 
where  
    (rs.stepTypeID = 1 and rs.status = 1 and rs.assignedToUserID = '3200') 
    or 
    (rs.stepTypeID = 6 and rs.status = 1 and rs.assignedToUserID = '3200') 
    or 
    (rs.stepTypeID = 4 and rs.status = 1  and si.inspectorUserID = '3200' 
     and si.inspectionDate IS NOT NULL 
     AND CAST(si.inspectionDate AS DATE) <= CONVERT(DATE,'2014-9-8')) 
order by 
    stepActivatedDate asc

现在，我要做的就是获取此语句检索的记录数。我将需要设置一个条件来决定何时获取计数以及何时获取实际数据，但是现在我需要首先获取计数，然后在客户端确认后获取数据。

我对mysql更加熟悉，因此我希望获得类似的东西select count(*) from (select * from users)，但是任何允许我仅获取计数的更改，因为该语句的返回值将对我有很大帮助。

我试着做

select count(*) from ( <copy paste statement>)

无济于事。

SQLChao

这应该工作。我order by从您的派生表中删除了它，因为这会导致错误，并且还为派生表提供了别名。

select count(1) as Cnt
from
(
    select rs.record_StepID, rs.recordID, rs.stepTypeID, rs.label, rs.assignedToUserID, rs.stepActivatedDate,si.scheduledInspectionID, l.streetNo,l.streetName, l.unit, l.city, l.state, l.postalCode, l.country, rt.categoryName 
    from Record_Steps as rs 
    left join Users as u on rs.assignedToUserID = u.userID 
    left join ScheduledInspections as si on rs.record_StepID=si.record_StepID 
    left join Records as r on r.recordID = rs.recordID 
    left join Locations as l on l.locationID = r.locationID 
    left join recordTypes as rt on r.recordTypeID = rt.recordTypeID 
    where (rs.stepTypeID = 1 and rs.status=1 and rs.assignedToUserID = '3200') 
    or (rs.stepTypeID = 6 and rs.status=1 and rs.assignedToUserID = '3200') 
    or (rs.stepTypeID = 4 and rs.status = 1  and si.inspectorUserID = '3200' and si.inspectionDate is not NULL and CAST(si.inspectionDate AS DATE) <= CONVERT(DATE,'2014-9-8')) 
)  as aTable

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。