27,580
社区成员
发帖
与我相关
我的任务
分享
select top 10 a.pid,a.name --top 10 其实不需要了
from A表 a
left join B表 b ON a.pid=b.pid
where b.parentclass='基础班'
and a.pid BETWEEN @页内最小pid AND @页内最大pid
group by a.pid,a.name
order by a.pid desc
select top 10 a.pid,a.name
from A表 a
left join B表 b ON a.pid=b.pid
where b.parentclass='基础班'
and a.pid > @上页最大pid
group by a.pid,a.name
order by a.pid desc
DECLARE @pageSize int
DECLARE @page int
SET @pageSize = 100
SET @page = 2
SELECT pid
FROM (
SELECT DISTINCT pid,
ROW_NUMBER() OVER (ORDER BY GetDate()) rn
FROM B
WHERE parentclass = '基础班'
) t
WHERE rn BETWEEN (@page-1)*@pageSize+1 AND @page*@pageSize
ORDER BY rn
select A表.* from A表 join(SELECT pid,parentClass B表 where B表.parentclass='基础班' GROUP BY pid,parentClass)T on A表.pid=T.pid
--去重复计数
SELECT COUNT(DISTINCT pid) FROM B WHERE parentclass = '基础班'
--去重复查询
SELECT DISTINCT pid FROM B WHERE parentclass = '基础班'
select top 10 a.pid,a.name from A表 a left join B表 b ON a.pid=b.pid where b.parentclass='基础班' group by a.pid,a.name order by a.pid desc
第二页:
select top 10 a.pid,a.name from A表 a left join B表 b ON a.pid=b.pid where b.parentclass='基础班' and b.pid >(select min(pid) from (select top 10 pid from B表 where parentclass='基础班' group by pid order by pid desc) as tmp) group by a.pid,a.name order by a.pid desc
cout总数是这样吗?
select distinct count(*) from B表 where parentclass=‘基础班’
sql2000数据库,求指点!!如能优化更加感激不尽!
select top 10 a.pid,a.name from A表 a LEFT JOIN B表 b ON a.pid=b.pid where b.parentclass='基础班' group by a.pid,a.name order by a.pid desc
select top 10 a.pid,a.name from A表 a LEFT JOIN B表 b ON a.pid=b.pid where b.parentclass='基础班' group by a.pid order by a.pid desc
但后面的页和count就不知道怎么写了