select a.* from v_info a join( select info_id=max(a.info_id) from info a join( select kind_code=1401 union all select 1402 union all select 1403 union all select 1404 union all select 1501 union all select 1904 union all select 1801 union all select 1802 union all select 1804 union all select 1405 union all select 1901 )b on a.kind_code=b.kind_code group by a.kind_code )b on a.info_id=b.info_id
--试试那种效率高一点:select a.* from v_info a join( select kind_code=1401 union all select 1402 union all select 1403 union all select 1404 union all select 1501 union all select 1904 union all select 1801 union all select 1802 union all select 1804 union all select 1405 union all select 1901 )b on a.kind_code=b.kind_code join( select info_id=max(a.info_id) from info group by a.kind_code )c on a.info_id=b.info_id
將第一個in改為 "=",因為max隻返回一個值 select * from v_info where info_id = (select max(info_id) from info group by kind_code ) and kind_code in (1401,1402,1403,1404,1501,1904,1801,1802,1804,1405,1901)
注意要建索引啊,可大幅度提交查詢性能 改為以下試試: select * from v_info where info_id = (select max(info_id) from info group by kind_code ) and (kind_code =1401 or kind_code =1402 or ...)
將第一個in改為 "=",因為max隻返回一個值 肯定不行的
--如果要提取N条的话,试试这样: select a.* from v_info a join( select kind_code=1401 union all select 1402 union all select 1403 union all select 1404 union all select 1501 union all select 1904 union all select 1801 union all select 1802 union all select 1804 union all select 1405 union all select 1901 )b on a.kind_code=b.kind_code where a.info_id in( select top n info_id --注意这里的n用具体的数字代替 from info where kind_code=a.kind_code)
另外,你的表中,这两个字段要建立索引 kind_code,info_id
zjcxc(: 邹建 :) ,v_info是试图,还有更简便的写法么?
--上面已经写啦,提取N条的.--如果要提取N条的话,试试这样: select a.* from v_info a join( select kind_code=1401 union all select 1402 union all select 1403 union all select 1404 union all select 1501 union all select 1904 union all select 1801 union all select 1802 union all select 1804 union all select 1405 union all select 1901 )b on a.kind_code=b.kind_code where a.info_id in( select top n info_id --注意这里的n用具体的数字代替 from info where kind_code=a.kind_code)
我的意思是视图的话 where kind_code=a.kind_code 就不需要了阿 因为视图中这个关系已经存在了阿
在建視圖時就做過濾:create view v_info as select * from 原表 where (kind_code =1401 or kind_code =1402 or ...)
你仔细看看,不要的话应该结果有问题吧?当然,我的意思是你的每组kind_code取n条.如果是整个结果取n条,那我的写法就错了,就应该是:select top n a.* from v_info a join( select info_id=max(a.info_id) from info a join( select kind_code=1401 union all select 1402 union all select 1403 union all select 1404 union all select 1501 union all select 1904 union all select 1801 union all select 1802 union all select 1804 union all select 1405 union all select 1901 )b on a.kind_code=b.kind_code group by a.kind_code )b on a.info_id=b.info_id
就是提取每个栏目的前N条 每组kind_code取n条
--那就是这个没错了:select a.* from v_info a join( select kind_code=1401 union all select 1402 union all select 1403 union all select 1404 union all select 1501 union all select 1904 union all select 1801 union all select 1802 union all select 1804 union all select 1405 union all select 1901 )b on a.kind_code=b.kind_code where a.info_id in( select top n info_id --注意这里的n用具体的数字代替 from info where kind_code=a.kind_code)
--你用的是那一个? select a.* from v_info a join( select kind_code=1401 union all select 1402 union all select 1403 union all select 1404 union all select 1501 union all select 1904 union all select 1801 union all select 1802 union all select 1804 union all select 1405 union all select 1901 )b on a.kind_code=b.kind_code join( select info_id=max(a.info_id) from info group by a.kind_code )c on a.info_id=c.info_id --这里错了
select a.* from v_info a join( select kind_code=1401 union all select 1402 union all select 1403 union all select 1404 union all select 1501 union all select 1904 union all select 1801 union all select 1802 union all select 1804 union all select 1405 union all select 1901 )b on a.kind_code=b.kind_code join( select info_id=max(a.info_id) from info group by a.kind_code )c on a.info_id=c.info_id --这里错了 还是不对 :(
--这样应该可以了.select a.* from v_info a join( select kind_code=1401 union all select 1402 union all select 1403 union all select 1404 union all select 1501 union all select 1904 union all select 1801 union all select 1802 union all select 1804 union all select 1405 union all select 1901 )b on a.kind_code=b.kind_code join( select info_id=max(info_id) --这里多了一个别名 from info group by kind_code )c on a.info_id=c.info_id --这里错了
select top n a.* from v_info a join( select info_id=max(a.info_id) from info a join( select kind_code=1401 union all select 1402 union all select 1403 union all select 1404 union all select 1501 union all select 1904 union all select 1801 union all select 1802 union all select 1804 union all select 1405 union all select 1901 )b on a.kind_code=b.kind_code group by a.kind_code )b on a.info_id=b.info_id这个不是从结果集中提取前N条 而是从每个栏目中提起一条
select a.*
from v_info a join(
select info_id=max(a.info_id)
from info a join(
select kind_code=1401
union all select 1402
union all select 1403
union all select 1404
union all select 1501
union all select 1904
union all select 1801
union all select 1802
union all select 1804
union all select 1405
union all select 1901
)b on a.kind_code=b.kind_code
group by a.kind_code
)b on a.info_id=b.info_id
from v_info a join(
select kind_code=1401
union all select 1402
union all select 1403
union all select 1404
union all select 1501
union all select 1904
union all select 1801
union all select 1802
union all select 1804
union all select 1405
union all select 1901
)b on a.kind_code=b.kind_code join(
select info_id=max(a.info_id)
from info
group by a.kind_code
)c on a.info_id=b.info_id
select * from v_info where info_id = (select max(info_id) from info group by kind_code ) and kind_code in (1401,1402,1403,1404,1501,1904,1801,1802,1804,1405,1901)
info_id,info_title,info_content,kind_codekind表
kind_id,kind_codev_info
是info表和kind表的视图
改為以下試試:
select * from v_info where info_id = (select max(info_id) from info group by kind_code ) and
(kind_code =1401 or kind_code =1402 or ...)
肯定不行的
select a.*
from v_info a join(
select kind_code=1401
union all select 1402
union all select 1403
union all select 1404
union all select 1501
union all select 1904
union all select 1801
union all select 1802
union all select 1804
union all select 1405
union all select 1901
)b on a.kind_code=b.kind_code
where a.info_id in(
select top n info_id --注意这里的n用具体的数字代替
from info
where kind_code=a.kind_code)
kind_code,info_id
select a.*
from v_info a join(
select kind_code=1401
union all select 1402
union all select 1403
union all select 1404
union all select 1501
union all select 1904
union all select 1801
union all select 1802
union all select 1804
union all select 1405
union all select 1901
)b on a.kind_code=b.kind_code
where a.info_id in(
select top n info_id --注意这里的n用具体的数字代替
from info
where kind_code=a.kind_code)
where kind_code=a.kind_code
就不需要了阿
因为视图中这个关系已经存在了阿
as
select * from 原表
where (kind_code =1401 or kind_code =1402 or ...)
from v_info a join(
select info_id=max(a.info_id)
from info a join(
select kind_code=1401
union all select 1402
union all select 1403
union all select 1404
union all select 1501
union all select 1904
union all select 1801
union all select 1802
union all select 1804
union all select 1405
union all select 1901
)b on a.kind_code=b.kind_code
group by a.kind_code
)b on a.info_id=b.info_id
每组kind_code取n条
from v_info a join(
select kind_code=1401
union all select 1402
union all select 1403
union all select 1404
union all select 1501
union all select 1904
union all select 1801
union all select 1802
union all select 1804
union all select 1405
union all select 1901
)b on a.kind_code=b.kind_code
where a.info_id in(
select top n info_id --注意这里的n用具体的数字代替
from info
where kind_code=a.kind_code)
from v_info a join(
select kind_code=1401
union all select 1402
union all select 1403
union all select 1404
union all select 1501
union all select 1904
union all select 1801
union all select 1802
union all select 1804
union all select 1405
union all select 1901
)b on a.kind_code=b.kind_code join(
select info_id=max(a.info_id)
from info
group by a.kind_code
)c on a.info_id=c.info_id --这里错了
from v_info a join(
select kind_code=1401
union all select 1402
union all select 1403
union all select 1404
union all select 1501
union all select 1904
union all select 1801
union all select 1802
union all select 1804
union all select 1405
union all select 1901
)b on a.kind_code=b.kind_code join(
select info_id=max(a.info_id)
from info
group by a.kind_code
)c on a.info_id=c.info_id --这里错了
还是不对
:(
from v_info a join(
select kind_code=1401
union all select 1402
union all select 1403
union all select 1404
union all select 1501
union all select 1904
union all select 1801
union all select 1802
union all select 1804
union all select 1405
union all select 1901
)b on a.kind_code=b.kind_code join(
select info_id=max(info_id) --这里多了一个别名
from info
group by kind_code
)c on a.info_id=c.info_id --这里错了
from v_info a join(
select info_id=max(a.info_id)
from info a join(
select kind_code=1401
union all select 1402
union all select 1403
union all select 1404
union all select 1501
union all select 1904
union all select 1801
union all select 1802
union all select 1804
union all select 1405
union all select 1901
)b on a.kind_code=b.kind_code
group by a.kind_code
)b on a.info_id=b.info_id这个不是从结果集中提取前N条
而是从每个栏目中提起一条