sqlserver with cte语句问题
语句是这样的
with cte as(select distinct top 10 QStandType.QStandTypeID,QStandType.QStandTypeName,ROW_NUMBER() over(order by QStandID asc) as num from QStand
inner join containstable(QStand,(QStandFRContent,QStandGRContent),'混凝土 and 强度',50)as k
on QStandID=k.[key]
inner join QStandType on
QStand.QStandTypeID=QStandType.QStandTypeID
order by QStandTypeID asc
)
select * from cte where num>5
查询结果如下：
9建筑地基处理技术规范 JGJ79—200220
11锚杆喷射混凝土支护技术规范 GB50086-200123
11锚杆喷射混凝土支护技术规范 GB50086-200124
11锚杆喷射混凝土支护技术规范 GB50086-200125
11锚杆喷射混凝土支护技术规范 GB50086-200126
15大体积混凝土施工规范 GB50496-200940
15大体积混凝土施工规范 GB50496-200941
16装配式大板居住建筑设计与施工规程 JGJ1-9143
17高层建筑混凝土结构技术规程 JGJ3-200244
19冷拔钢丝预应力混凝土构件设计施工规程 JGJ19-9245
这里有两个问题
1.查询出来的结果，不是5条，应该是后5条的，个人感觉containstable前面的语句没用上
2.查询出来的结果有重复，但是我在第一句就写了 select distinct
这里主要是涉及到了分页存储过程
请各位大侠指点。。 实验成功的语句
declare @ContentOnenvarchar(50)
declare @ContentTwonvarchar(50)
set @ContentOne='混凝土'
set @ContentTwo='强度'
declare @sql nvarchar(500)
set @sql='select distinct top 5 QStandType.QStandTypeID,QStandType.QStandTypeName from QStand
inner join
containstable(Qstand,([QStandFRContent],[QStandGRContent]),''("'+@ContentOne+'")and
("'+@ContentTwo+'")'',5000) as k
on QStandID=k.[key]
inner join QStandType on
QStand.QStandTypeID=QStandType.QStandTypeID
order by QStandTypeID'
exec sp_executesql @sql
结果如下：
3建筑基坑支护技术规范 JGJ120-99
4湿陷性黄土地区建筑基坑工程安全技术规程 JGJ167-2009
5膨胀土地区建筑技术规范 GBJ112-87
6湿陷性黄土地区建筑规范 GB50025-2004
7建筑边坡工程技术规范 GB50330-2002
[最优解释]
select distinct top 10
改为试试
select top 10 distinct
[其他解释]
order by QStandTypeID desc呢？
--把QStandID 放cte然后查 看看
SELECT *
FROM cte
WHERE num > 5
ORDER BY QStandID ASC
with cte as(select distinct top 10 QStandType.QStandTypeID,QStandType.QStandTypeName,ROW_NUMBER() over(order by QStandID asc) as num from QStand
inner join containstable(QStand,(QStandFRContent,QStandGRContent),'混凝土 and 强度',50)as k
on QStandID=k.[key]
inner join QStandType on
QStand.QStandTypeID=QStandType.QStandTypeID
order by QStandTypeID asc
)
select * from cte where num>5
with cte as(select distinct top 5 QStandType.QStandTypeID,QStandType.QStandTypeName,ROW_NUMBER() over(order by QStandType.QStandTypeID asc) as num from QStand
inner join containstable(QStand,(QStandFRContent,QStandGRContent),'混凝土 and 强度',50)as k
on QStandID=k.[key]
inner join QStandType on
QStand.QStandTypeID=QStandType.QStandTypeID
order by QStandTypeID asc
)
select * from cte where num>0
with cte as(select QStandType.QStandTypeID,QStandType.QStandTypeName,ROW_NUMBER() over(order by QStandType.QStandTypeID asc) as num from QStand
inner join containstable(QStand,(QStandFRContent,QStandGRContent),'混凝土 and 强度',50)as k
on QStandID=k.[key]
inner join QStandType on
QStand.QStandTypeID=QStandType.QStandTypeID
)
select top 5 * from (select distinct * from cte )t order by QStandTypeID asc
with cte as(select QStandType.QStandTypeID,QStandType.QStandTypeName
--,ROW_NUMBER() over(order by QStandType.QStandTypeID asc) as num
from QStand
inner join containstable(QStand,(QStandFRContent,QStandGRContent),'混凝土 and 强度',50)as k
on QStandID=k.[key]
inner join QStandType on
QStand.QStandTypeID=QStandType.QStandTypeID
)
select top 5 * from (select distinct * from cte )t order by QStandTypeID asc
with cte as(select distinct QStandType.QStandTypeID,QStandType.QStandTypeName
from QStand
inner join containstable(QStand,(QStandFRContent,QStandGRContent),'混凝土 and 强度',50)as k
on QStandID=k.[key]
inner join QStandType on
QStand.QStandTypeID=QStandType.QStandTypeID
)
select *,row=ROW_NUMBER() over(order by QStandType.QStandTypeID asc) as num from cte --在这句做分页查询
ALTER Procedure [dbo].[Popular_PageList]
@ContentOne nvarchar(20), --查询关键字
@ContentTwo nvarchar(20),
@ContentThree nvarchar(20),
@CurrentCount int, ----当前的索引，假设每页5条数据，第二页的话，currencount为10
@numint, --每页的条数
@TempTypeID int, --所属分类
@TempTypeIDName nvarchar(50), --所属分类名 与上对应 两个是一起的
@fieldOnenvarchar(20), --要查询的字段 强规
@fieldTwonvarchar(20), --一般规
@TempIDNamenvarchar(20),--要排序的ID
@Tablenvarchar(20) --要查询的表
AS
declare @Sql nvarchar(300)
declare @opreat nvarchar(20)
if @ContentTwo is null or @ContentTwo=''
set @opreat='or'
else
set @opreat='and'
set @Sql='with cte as(select top '+CONVERT(nvarchar,@CurrentCount)+' *,row_number() over(order by '+@TempIDName+' asc) as num from '+@Table+'
inner join containstable('+@Table+',(['+@fieldOne+'],['+@fieldTwo+']),''("'+@ContentOne+'") '+@opreat+' ("'+@ContentTwo+'")or("'+@ContentThree+'")'',50) as k
on '+@TempIDName+'=k.[key]
where '+@TempTypeIDName+'='+CONVERT(nvarchar,@TempTypeID)+'
order by '+@TempIDName+' asc)
select * from cte where num>'+CONVERT(nvarchar,@num)+''
exec sp_executeSQL @sql
with cte as(select QStandType.QStandTypeID,QStandType.QStandTypeName from QStand
inner join containstable(QStand,(QStandFRContent,QStandGRContent),'混凝土 and 强度',500)as k
on QStandID=k.[key]
inner join QStandType on
QStand.QStandTypeID=QStandType.QStandTypeID
)
select top 5 * from (select distinct top 5 * from cte where QStandTypeID>(select distinct top 5 * from cte order by QStandTypeID asc) order by QStandTypeID asc )t
