合计字段怎样给定权值排序?
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)
但是现在希望能够在外面设定权值,怎么做呢?似乎要做一个视图,把下面两条语句里的记录合到一个表里面,然后再SUM
select 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中能做吗?