select top 2 *,number=
(select COUNT(*) from et_ztea_images as i where i.tea_albumid=a.id),
imagepath=(select top 1 imagepath from et_ztea_images as z where
z.tea_albumid=a.id),picid=(select top 1 id from et_ztea_images as z
where z.tea_albumid=a.id) from et_ztea_Album as a where teaid=326
order by AlbumTime desc
我想要让 number为0 数据不要（聚合函数方面）。结果就是把第一条数据去掉。。。。。。。。求救各位大哥~
id userId userName teaid tealevel teaAlbum AlbumTime number imagepath picid
139 NULL NULL 326 1 xx 2012-08-14 0 NULL NULL
47 NULL NULL 326 2 xx 2012-07-27 3 201272778567.jpg 60
------解决方案--------------------
改下，with语句中不能含有order by,
换成下面的
- SQL code
select * from( select top 2 *,number= (select COUNT(*) from et_ztea_images as i where i.tea_albumid=a.id), imagepath=(select top 1 imagepath from et_ztea_images as z where z.tea_albumid=a.id),picid=(select top 1 id from et_ztea_images as z where z.tea_albumid=a.id) from et_ztea_Album as a where teaid=326 order by AlbumTime desc) t where number<>0
------解决方案--------------------
- SQL code
SELECT TOP 2 * , number = ( SELECT COUNT(*) FROM et_ztea_images AS i WHERE i.tea_albumid = a.id) , imagepath = ( SELECT TOP 1 imagepath FROM et_ztea_images AS z WHERE z.tea_albumid = a.id) , picid = ( SELECT TOP 1 id FROM et_ztea_images AS z WHERE z.tea_albumid = a.id)FROM et_ztea_Album AS aWHERE teaid = 326 AND (SELECT COUNT(*) FROM et_ztea_images AS i WHERE i.tea_albumid = a.id) <> 0ORDER BY AlbumTime DESC
------解决方案--------------------
- SQL code
SELECT TOP 2 * , number = ( SELECT COUNT(*) FROM et_ztea_images AS i WHERE i.tea_albumid = a.id ) , imagepath = ( SELECT TOP 1 imagepath FROM et_ztea_images AS z WHERE z.tea_albumid = a.id ) , picid = ( SELECT TOP 1 id FROM et_ztea_images AS z WHERE z.tea_albumid = a.id )FROM et_ztea_Album AS aWHERE teaid = 326AND EXISTS(SELECT 1 FROM et_ztea_images b WHERE a.id = b.tea_albumid) --加了这句ORDER BY AlbumTime DESC