select top 10
a.h_id,
a.h_name,
a.h_writer,
pub_name = dbo.uf_getPubName(a.pub_id),
a.h_output_price,
a.h_smallimage,
a.h_publish_date,
discount_price =CONVERT(decimal(10, 2),(db_product_discount.h_output_discount_ls*a.h_output_price))
from (select *
from db_product b
where b.h_id not in(
select top 100
db_product.h_id
from db_product,
db_publishs,
db_product_discount
where db_product.pub_id=db_publishs.pub_id
AND db_product.h_id=db_product_discount.h_id
AND db_product.h_type='0707')) a我在执行上面的语句时出现这样的错误:
服务器: 消息 107,级别 16,状态 3,行 1
列前缀 'db_product_discount' 与查询中所用的表名或别名不匹配。
a.h_id,
a.h_name,
a.h_writer,
pub_name = dbo.uf_getPubName(a.pub_id),
a.h_output_price,
a.h_smallimage,
a.h_publish_date,
discount_price =CONVERT(decimal(10, 2),(db_product_discount.h_output_discount_ls*a.h_output_price))
from (select *
from db_product b
where b.h_id not in(
select top 100
db_product.h_id
from db_product,
db_publishs,
db_product_discount
where db_product.pub_id=db_publishs.pub_id
AND db_product.h_id=db_product_discount.h_id
AND db_product.h_type='0707')) a我在执行上面的语句时出现这样的错误:
服务器: 消息 107,级别 16,状态 3,行 1
列前缀 'db_product_discount' 与查询中所用的表名或别名不匹配。
——〉 (a.h_output_discount_ls*a.h_output_price))
select top 10
a.h_id,
a.h_name,
a.h_writer,
pub_name = dbo.uf_getPubName(a.pub_id),
a.h_output_price,
a.h_smallimage,
a.h_publish_date,
discount_price =CONVERT(decimal(10, 2),(a.h_output_discount_ls*a.h_output_price))
from (select *
from db_product b
where b.h_id not in(
select top 100
db_product.h_id
from db_product,
db_publishs,
db_product_discount
where db_product.pub_id=db_publishs.pub_id
AND db_product.h_id=db_product_discount.h_id
AND db_product.h_type='0707')
) a
服务器: 消息 207,级别 16,状态 3,行 1
列名 'h_output_discount_ls' 无效。
此处出错,原因是你取得数据的表是a表,db_product_discount是另外一个表,所以才会出现
列前缀 'db_product_discount' 与查询中所用的表名或别名不匹配的错误。修改方法在最后a表后面加一个表名'db_product_discount'
即:select top 10
a.h_id,
a.h_name,
a.h_writer,
pub_name = dbo.uf_getPubName(a.pub_id),
a.h_output_price,
a.h_smallimage,
a.h_publish_date,
discount_price =CONVERT(decimal(10, 2),(db_product_discount.h_output_discount_ls*a.h_output_price))
from (select *
from db_product b
where b.h_id not in(
select top 100
db_product.h_id
from db_product,
db_publishs,
db_product_discount
where db_product.pub_id=db_publishs.pub_id
AND db_product.h_id=db_product_discount.h_id
AND db_product.h_type='0707')) a,db_product_discount得分,谢谢!
a.h_id,
a.h_name,
a.h_writer,
pub_fullname = dbo.uf_getPubName(a.pub_id),
a.h_output_price,
a.h_smallimage,
a.h_publish_date,
discount_price =CONVERT(decimal(10, 2),(db_product_discount.h_output_discount_ls*a.h_output_price))
FROM (select *
FROM db_product b
WHERE b.h_id not in(
SELECT top $StartIndex$
db_product.h_id
FROM db_product,
db_publishs,
db_product_discount
WHERE db_product.pub_id=db_publishs.pub_id
AND db_product.h_id=db_product_discount.h_id
AND db_product.h_type='$h_type$')) a,db_product_discount
WHERE a.h_id=db_product_discount.h_id其中在两个$$ 之间是参数;
PageSize是每页显示多少个,
//计算总共有多少记录
RecordCount=books.RowsCount;
//设定导入的起终地址
StartIndex = CurrentPage*PageSize; 这是事件:
public void Page_OnClick(Object sender,CommandEventArgs e)
{
CurrentPage = (int)ViewState["PageIndex"];
PageCount = (int)ViewState["PageCount"]; string cmd = e.CommandName;
//判断cmd,以判定翻页方向
switch(cmd)
{
case "next":
if(CurrentPage<(PageCount-1)) CurrentPage++;
break;
case "prev":
if(CurrentPage>0) CurrentPage--;
break;
} ViewState["PageIndex"] = CurrentPage; ListBind(); } //ListBind()
public void ListBind()
{
myDataList.DataSource = CreateSource();
myDataList.DataBind(); lbnNextPage.Enabled = true;
lbnPrevPage.Enabled = true;
if(CurrentPage==(PageCount-1)) lbnNextPage.Enabled = false;
if(CurrentPage==0) lbnPrevPage.Enabled = false;
lblCurrentPage.Text = (CurrentPage+1).ToString();
} private IList CreateSource(){
int StartIndex;
//设定导入的起终地址
StartIndex = CurrentPage*PageSize;
GetResult(PageSize,StartIndex,h_type);
Yhdt.Presentation.UserActions.BooksAction action = new Yhdt.Presentation.UserActions.BooksAction(Context);
return action.GetList_books(htb);
}不好意思,可能有点乱
CREATE PROCEDURE mypage
@tb varchar(50), --表名
@col varchar(50), --按该列来进行分页
@coltype int, --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800)--查询条件
ASDECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
ENDIF @page=1--第一页
begin
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
@where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
EXEC(@sql)
return
end
IF @orderby=0
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@col+' DESC'
EXEC(@sql)
return