分页查询的 Tb_Trans表(主表) 要查询出的数据列ItemName,ItemId
Tb_Item 表(次表) 要查询的关联列 ItemName(别名 PPName),DistId 要求过滤 主表Tb_Trans中的相同ItemName列且不为空,主表Tb_Trans 中的DistId不为空且在 次表Tb_Item 中DistId字段对应,下面是我写的sql,功能倒是满足,但性能有点慢,我是在20W数据中抓的发现从第一页到第二页用时20秒,望前辈们指点
select * from(select a.ItemName,a.ItemId,c.ItemName as PPName,d.distId from Tb_Trans a
left join tb_item c on c.ItemId=a.ItemId
inner join tb_Item d on d.distId=a.distId
where a.ItemName is not null and d.distId is not null
group by a.ItemName,a.ItemId,c.ItemName,d.distId)
where rownum<=80
minus
select * from(select a.ItemName,a.ItemId,c.ItemName as PPName,d.distId from Tb_Trans a
left join tb_item c on c.ItemId=a.ItemId
inner join tb_Item d on d.distid=a.distId
where a.ItemName is not null and d.distId is not null
group by a.ItemName,a.ItemId,c.ItemName,d.distId)
where rownum<=60
Tb_Item 表(次表) 要查询的关联列 ItemName(别名 PPName),DistId 要求过滤 主表Tb_Trans中的相同ItemName列且不为空,主表Tb_Trans 中的DistId不为空且在 次表Tb_Item 中DistId字段对应,下面是我写的sql,功能倒是满足,但性能有点慢,我是在20W数据中抓的发现从第一页到第二页用时20秒,望前辈们指点
select * from(select a.ItemName,a.ItemId,c.ItemName as PPName,d.distId from Tb_Trans a
left join tb_item c on c.ItemId=a.ItemId
inner join tb_Item d on d.distId=a.distId
where a.ItemName is not null and d.distId is not null
group by a.ItemName,a.ItemId,c.ItemName,d.distId)
where rownum<=80
minus
select * from(select a.ItemName,a.ItemId,c.ItemName as PPName,d.distId from Tb_Trans a
left join tb_item c on c.ItemId=a.ItemId
inner join tb_Item d on d.distid=a.distId
where a.ItemName is not null and d.distId is not null
group by a.ItemName,a.ItemId,c.ItemName,d.distId)
where rownum<=60
FROM
(
select
a.ItemName,a.ItemId,c.ItemName as PPName,d.distId,ROW_NUMBER()OVER(ORDER BY a.ItemName) AS row
from Tb_Trans a
left join tb_item c on c.ItemId=a.ItemId
inner join tb_Item d on d.distId=a.distId
where a.ItemName is not null and d.distId is not null
group by a.ItemName,a.ItemId,c.ItemName,d.distId
) t
WHERE row BETWEEN 61 AND 80
谢谢前辈,但是我想如果一用到分组group by 的话基本就要慢好多,但是不分组用distinct好像不支持多列,也不知道有什么好方法吗?
from (select distinct col2,col3 from tablename)t
group by --會改變順序
FROM
(
select
a.ItemName,a.ItemId,c.ItemName as PPName,d.distId,ROW_NUMBER()OVER(ORDER BY a.ItemName) AS row
from Tb_Trans a
left join tb_item c on c.ItemId=a.ItemId
inner join tb_Item d on d.distId=a.distId
where a.ItemName is not null and d.distId is not null
group by a.ItemName,a.ItemId,c.ItemName,d.distId
) t
WHERE row BETWEEN 61 AND 80這樣用,你試試
FROM
(
select
a.ItemName,a.ItemId,c.ItemName as PPName,d.distId,ROW_NUMBER()OVER(ORDER BY a.ItemName) AS Num
from Tb_Trans a
left join tb_item c on c.ItemId=a.ItemId
inner join tb_Item d on d.distId=a.distId
where a.ItemName is not null and d.distId is not null
group by a.ItemName,a.ItemId,c.ItemName,d.distId
) t
WHERE Num BETWEEN 61 AND 80