T_tag(title,tag)select title, count(tag) from T_tag group by title where tag="tag1" or tag="tag2" order by count(tag) desc这样得到的结果是所有标有tag1或tag2的title按照标签数的降序排列。现在希望对tag1和tag2设权值,以影响排序结果,例如
tag1.weight=100
tag2.weight=50希望order by (100*count(tag1)+50*count(tag2)),也就是按照一个自定义的权值来实现排序,这个权值并不在表中。如果两者权值相同是可以用sum函数实现的:
select title, sum(count1) from (select title, count(*) as count1 from t_tag where tag="tag1" or tag="tag2" group by title and tag) group by title order by sum(count1)但是现在希望能够在外面设定权值,怎么做呢?似乎要做一个视图,把下面两条语句里的记录合到一个表里面,然后再SUMselect title, 100*count(*) as count1 from t_tag where tag="tag1" group by title
select title, 50*count(*) as count1 from t_tag where tag="tag2" group by title
这样的视图怎么用SQL写呢?ACCESS中能做吗?
tag1.weight=100
tag2.weight=50希望order by (100*count(tag1)+50*count(tag2)),也就是按照一个自定义的权值来实现排序,这个权值并不在表中。如果两者权值相同是可以用sum函数实现的:
select title, sum(count1) from (select title, count(*) as count1 from t_tag where tag="tag1" or tag="tag2" group by title and tag) group by title order by sum(count1)但是现在希望能够在外面设定权值,怎么做呢?似乎要做一个视图,把下面两条语句里的记录合到一个表里面,然后再SUMselect title, 100*count(*) as count1 from t_tag where tag="tag1" group by title
select title, 50*count(*) as count1 from t_tag where tag="tag2" group by title
这样的视图怎么用SQL写呢?ACCESS中能做吗?
还有权值我基本没看懂不如给点数据,给个结果好理解
前面7行看懂了吧?就是说能自己设定标签的重要性来排序,这可是今后web2.0的发展方向啊。。
insert into tb select 'a'
insert into tb select 'a'
insert into tb select 'b'
insert into tb select 'b'select name,sum(case when name='a' then 100 when name='b' then 50 end) as [sum]
from tb
group by name
order by sum(case when name='a' then 100 when name='b' then 50 end)b 100
a 200