34,874
社区成员
发帖
与我相关
我的任务
分享在全文检索里用order by子句排序后，前面不能加distinct，有解决方法吗？！谢谢
我在全文检索里用order by子句排序后，前面不能加distinct，sql提示是：
错误145，如果指定了select distinct,那么order by字句的项就必须出现在选择列表中，有解决方法吗？！谢谢！
SELECT distinct n.* FROM goods n,Brands b WHERE CONTAINS(n.*, @str1) or (n.BrandId=b.PKId and CONTAINS(b.name, @str1)) ORDER BY CASE WHEN CONTAINS(n.*, @str1) THEN 1 WHEN (n.BrandId=b.PKId and CONTAINS(b.name, @str1)) THEN 2 ELSE 3 END
错误145，如果指定了select distinct,那么order by字句的项就必须出现在选择列表中，有解决方法吗？！谢谢！
SELECT distinct n.* FROM goods n,Brands b WHERE CONTAINS(n.*, @str1) or (n.BrandId=b.PKId and CONTAINS(b.name, @str1)) ORDER BY CASE WHEN CONTAINS(n.*, @str1) THEN 1 WHEN (n.BrandId=b.PKId and CONTAINS(b.name, @str1)) THEN 2 ELSE 3 END
...全文
请发表友善的回复…
发表回复
CrazyFor 2003-08-18
- 打赏
- 举报
TRY:
SELECT distinct n.*, CASE WHEN CONTAINS(n.*, @str1) THEN 1 WHEN (n.BrandId=b.PKId and CONTAINS(b.name, @str1)) THEN 2 ELSE 3 END
as OrderByCol FROM goods n,Brands b WHERE CONTAINS(n.*, @str1) or (n.BrandId=b.PKId and CONTAINS(b.name, @str1)) ORDER BY OrderByCol
SELECT distinct n.*, CASE WHEN CONTAINS(n.*, @str1) THEN 1 WHEN (n.BrandId=b.PKId and CONTAINS(b.name, @str1)) THEN 2 ELSE 3 END
as OrderByCol FROM goods n,Brands b WHERE CONTAINS(n.*, @str1) or (n.BrandId=b.PKId and CONTAINS(b.name, @str1)) ORDER BY OrderByCol
alex5006 2003-08-18
- 打赏
- 举报
pengdali(大力 V2.0) ，那样的话还需要给tem建立全文索引的啊，不是更麻烦了啊！sql是这样提示的。
萝卜波 2003-08-18
- 打赏
- 举报
下面这句但愿对你有帮助
select max(newsid) as dd ,Nauthor from article group by Nauthor order by 1
select max(newsid) as dd ,Nauthor from article group by Nauthor order by 1
txlicenhe 2003-08-18
- 打赏
- 举报
Select * from (
SELECT distinct n.* FROM goods n,Brands b WHERE CONTAINS(n.*, @str1) or (n.BrandId=b.PKId and CONTAINS(b.name, @str1))
) temp
ORDER BY CASE WHEN CONTAINS(n.*, @str1) THEN 1 WHEN (n.BrandId=b.PKId and CONTAINS(b.name, @str1)) THEN 2 ELSE 3 END
SELECT distinct n.* FROM goods n,Brands b WHERE CONTAINS(n.*, @str1) or (n.BrandId=b.PKId and CONTAINS(b.name, @str1))
) temp
ORDER BY CASE WHEN CONTAINS(n.*, @str1) THEN 1 WHEN (n.BrandId=b.PKId and CONTAINS(b.name, @str1)) THEN 2 ELSE 3 END
pengdali 2003-08-18
- 打赏
- 举报
select * from (
SELECT distinct n.*,b.pkid FROM goods n,Brands b WHERE CONTAINS(n.*, @str1) or (n.BrandId=b.PKId and CONTAINS(b.name, @str1)) ) tem ORDER BY CASE WHEN CONTAINS(*, @str1) THEN 1 WHEN (BrandId=PKId and CONTAINS(name, @str1)) THEN 2 ELSE 3 END
SELECT distinct n.*,b.pkid FROM goods n,Brands b WHERE CONTAINS(n.*, @str1) or (n.BrandId=b.PKId and CONTAINS(b.name, @str1)) ) tem ORDER BY CASE WHEN CONTAINS(*, @str1) THEN 1 WHEN (BrandId=PKId and CONTAINS(name, @str1)) THEN 2 ELSE 3 END
