假设只有一个table,名为pages,有四个字段,id, url,title,body。里面储存了很多网页,网页的url地址,title和网页的内容,然后你用一个sql查询将url匹配的排在最前,title匹配的其次,body匹配最后,没有任何字段匹配的,不返回。select a.[id],a. from
(
select [page].[id],100 as from [page] where [page].[url] like '%baidu%'
union
select [page].[id],50 as from [page] where [page].[title] like '%baidu%'
union
select [page].[id],10 as from [page] where [page].[body] like '%baidu%'
) as a order by desc上面的a.[id] 和 100 50 10是上面意思啊? 求解释....
(
select [page].[id],100 as from [page] where [page].[url] like '%baidu%'
union
select [page].[id],50 as from [page] where [page].[title] like '%baidu%'
union
select [page].[id],10 as from [page] where [page].[body] like '%baidu%'
) as a order by desc上面的a.[id] 和 100 50 10是上面意思啊? 求解释....
select [page].[id],100 as from [page] where [page].[url] like '%baidu%'
union
select [page].[id],50 as from [page] where [page].[title] like '%baidu%'
union
select [page].[id],10 as from [page] where [page].[body] like '%baidu%'
这是一个表名字叫a(可以这样认为),然后从a里面获取字段。
a.[id]就是id字段。
(select *,num1 = case when url like '%baidu%' then 8 else 0 end,
num2 = case when title like '%baidu%' then 4 else 0 end,
num3 = case when body like '%baidu%' then 2 else 0 end
from pages
) a order by (a.num1+a.num2+a.num3)
在这里用到order by
select [page].[id],100 as from [page] where [page].[url] like '%baidu%'
union
select [page].[id],50 as from [page] where [page].[title] like '%baidu%'
union
select [page].[id],10 as from [page] where [page].[body] like '%baidu%'因为这整个当做一个字查询了所以必须要给一个字段名字,那么 就是充当新字段的字段名字而已了。因此也只需要在 第一条select [page].[id],100 as from [page] where [page].[url] like '%baidu%'加上这个就可以了后面的都可以不加,仅仅用来表示新字段的名字