select top 10000 ff.address,ff.firm_name,a.base_create_time ,a.base_last_updated, a.base_gtin,e.class_code,
e.brand,placeoforigin,orderingUnitOfMeasure,height,heightunit, width,widthunit,depth,depthunit,retailPriceOnTradeItem,
pkgcode,gross, grossWeightunit,netWeight, netWeightunit,packagingMaterialCompositionQuantity,
descriptionShort,tradeItemGroupIdentificationDescription,
specification,shelfLife,description
from baseinfo a
left join( select base_id, [31] as pkgcode,[84] as grossWeightunit,[189] as netWeightunit,[332] as specification,
[356] as shelfLife,[52] as tradeItemGroupIdentificationDescription,[132] as descriptionShort,
[51] as packagingMaterialCompositionQuantity,[405] as placeoforigin,[122] as orderingUnitOfMeasure,
[331] as depthunit,[326] as heightunit,[104] as widthunit,
CASE WHEN dbo.isReallyNumeric(''+[106]+'')=1 and ISNUMERIC(''+[106]+'')=1 THEN convert(numeric(18,4),[106]) ELSE 0 END as height,
CASE WHEN dbo.isReallyNumeric(''+[101]+'')=1 and ISNUMERIC(''+[101]+'')=1 THEN convert(numeric(18,4),[101]) ELSE 0 END as width,
CASE WHEN dbo.isReallyNumeric(''+[118]+'')=1 and ISNUMERIC(''+[118]+'')=1 THEN convert(numeric(18,4),[118]) ELSE 0 END as depth,
CASE WHEN dbo.isReallyNumeric(''+[196]+'')=1 and ISNUMERIC(''+[196]+'')=1 THEN convert(numeric(18,4),[196]) ELSE 0 END as retailPriceOnTradeItem,
CASE WHEN dbo.isReallyNumeric(''+[54]+'')=1 and ISNUMERIC(''+[54]+'')=1 THEN convert(numeric(18,4),[54]) ELSE 0 END as gross,
CASE WHEN dbo.isReallyNumeric(''+[10]+'')=1 and ISNUMERIC(''+[10]+'')=1 THEN convert(numeric(18,4),[10]) ELSE 0 END as netWeight,
CASE WHEN isdate([325])=1 THEN convert(datetime,[325]) ELSE convert(datetime,'9999-12-31') END as DiscontinuedDate
FROM
(SELECT valu,att_id,base_id from tradeitembv ) P pivot ( max(valu) FOR att_id
IN ([106], [101],[118],[31],[54],[84],[10],[189],[196],[332],
[356],[132],[52],[88],[51],[325],[405],[122],[331],[326],[104]) ) AS pvt ) d
on d.base_id=a.base_id join tradeitemb e on d.base_id=e.base_id
left join V_Card_FID c on a.base_card=c.card_no join firm ff on c.f_id=ff.f_id
where a.base_status>0 and a.base_last_updated >='2010-07-20'
谁能帮我在以上sql语句上,加上分页,谢谢!!!
e.brand,placeoforigin,orderingUnitOfMeasure,height,heightunit, width,widthunit,depth,depthunit,retailPriceOnTradeItem,
pkgcode,gross, grossWeightunit,netWeight, netWeightunit,packagingMaterialCompositionQuantity,
descriptionShort,tradeItemGroupIdentificationDescription,
specification,shelfLife,description
from baseinfo a
left join( select base_id, [31] as pkgcode,[84] as grossWeightunit,[189] as netWeightunit,[332] as specification,
[356] as shelfLife,[52] as tradeItemGroupIdentificationDescription,[132] as descriptionShort,
[51] as packagingMaterialCompositionQuantity,[405] as placeoforigin,[122] as orderingUnitOfMeasure,
[331] as depthunit,[326] as heightunit,[104] as widthunit,
CASE WHEN dbo.isReallyNumeric(''+[106]+'')=1 and ISNUMERIC(''+[106]+'')=1 THEN convert(numeric(18,4),[106]) ELSE 0 END as height,
CASE WHEN dbo.isReallyNumeric(''+[101]+'')=1 and ISNUMERIC(''+[101]+'')=1 THEN convert(numeric(18,4),[101]) ELSE 0 END as width,
CASE WHEN dbo.isReallyNumeric(''+[118]+'')=1 and ISNUMERIC(''+[118]+'')=1 THEN convert(numeric(18,4),[118]) ELSE 0 END as depth,
CASE WHEN dbo.isReallyNumeric(''+[196]+'')=1 and ISNUMERIC(''+[196]+'')=1 THEN convert(numeric(18,4),[196]) ELSE 0 END as retailPriceOnTradeItem,
CASE WHEN dbo.isReallyNumeric(''+[54]+'')=1 and ISNUMERIC(''+[54]+'')=1 THEN convert(numeric(18,4),[54]) ELSE 0 END as gross,
CASE WHEN dbo.isReallyNumeric(''+[10]+'')=1 and ISNUMERIC(''+[10]+'')=1 THEN convert(numeric(18,4),[10]) ELSE 0 END as netWeight,
CASE WHEN isdate([325])=1 THEN convert(datetime,[325]) ELSE convert(datetime,'9999-12-31') END as DiscontinuedDate
FROM
(SELECT valu,att_id,base_id from tradeitembv ) P pivot ( max(valu) FOR att_id
IN ([106], [101],[118],[31],[54],[84],[10],[189],[196],[332],
[356],[132],[52],[88],[51],[325],[405],[122],[331],[326],[104]) ) AS pvt ) d
on d.base_id=a.base_id join tradeitemb e on d.base_id=e.base_id
left join V_Card_FID c on a.base_card=c.card_no join firm ff on c.f_id=ff.f_id
where a.base_status>0 and a.base_last_updated >='2010-07-20'
谁能帮我在以上sql语句上,加上分页,谢谢!!!
SELECT TOP 页记录数量 *
FROM 表名
WHERE (ID NOT IN
(SELECT TOP (每页行数*(页数-1)) ID
FROM 表名
ORDER BY ID))
ORDER BY ID
//自己还可以加上一些查询条件
例:
select top 2 *
from Sys_Material_Type
where (MT_ID not in
(select top (2*(3-1)) MT_ID from Sys_Material_Type order by MT_ID))
order by MT_ID 2.分页方案二:(利用ID大于多少和SELECT TOP分页) 语句形式:
SELECT TOP 每页记录数量 *
FROM 表名
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 每页行数*页数 id FROM 表
ORDER BY id) AS T)
)
ORDER BY ID 例:
SELECT TOP 2 *
FROM Sys_Material_Type
WHERE (MT_ID >
(SELECT MAX(MT_ID)
FROM (SELECT TOP (2*(3-1)) MT_ID
FROM Sys_Material_Type
ORDER BY MT_ID) AS T))
ORDER BY MT_ID 3.分页方案三:(利用SQL的游标存储过程分页)
create procedure SqlPager
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off 4.总结:
其它的方案:如果没有主键,可以用临时表,也可以用方案三做,但是效率会低。
建议优化的时候,加上主键和索引,查询效率会提高。 通过SQL 查询分析器,显示比较:我的结论是:
分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句
分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句
分页方案三:(利用SQL的游标存储过程分页) 效率最差,但是最为通用