sql结果集合并问题

[啾啾我]

select *
from(
	select Id,Mid,Picture,ids=row_number()over(partition by Mid order by getdate()) 
	from News 
	where Mid in (118,260,123,144,143,288) and Picture<>''
)a
where ids<3
order by Mid,Id

union all

select *
from(
	select Id,Mid,Picture,ids=row_number()over(partition by Mid order by getdate()) 
	from News 
	where Mid in (118,260,123,144,143,288) and Picture=''
)b
where ids<8
order by Mid,Id

这个查询提示“关键字 'union' 附近有语法错误。”请各位帮忙合并一下。或者有没有其他更好的办法来写这个sql语句

[發糞塗牆]

select * from( select Id,Mid,Picture,ids=row_number()over(partition by Mid order by getdate()) from News where Mid in (118,260,123,144,143,288) and Picture<>'' )a where ids<3 --order by Mid,Id union all select * from( select Id,Mid,Picture,ids=row_number()over(partition by Mid order by getdate()) from News where Mid in (118,260,123,144,143,288) and Picture='' )b where ids<8 order by Mid,Id

[發糞塗牆]

union/union all /except/intercept 这四种集合操作中，只能有一个order by在最后

[啾啾我]

引用 2 楼 DBA_Huangzj 的回复:

union/union all /except/intercept 这四种集合操作中，只能有一个order by在最后

受教了，谢谢了， 这个sql查询语句能简化吗？字符太多了。

[發糞塗牆]

是2000还是非2000版本？

[發糞塗牆]

好像简化不了多少

;
WITH    cte
          AS ( SELECT   Id ,
                        Mid ,
                        Picture
               FROM     News
               WHERE    Mid IN ( 118, 260, 123, 144, 143, 288 )
             )
    SELECT  *
    FROM    ( SELECT    *
              FROM      ( SELECT    * ,
                                    ids = ROW_NUMBER() OVER ( PARTITION BY Mid ORDER BY GETDATE() )
                          FROM      cte
                          WHERE     Picture <> ''
                        ) a
              WHERE     ids < 3
              UNION ALL
              SELECT    *
              FROM      ( SELECT    * ,
                                    ids = ROW_NUMBER() OVER ( PARTITION BY Mid ORDER BY GETDATE() )
                          FROM      cte
                          WHERE     Picture = ''
                        ) b
              WHERE     ids < 8
            ) c
    ORDER BY mid ,
            id

[啾啾我]

引用 4 楼 DBA_Huangzj 的回复:

是2000还是非2000版本？

2005

[啾啾我]

引用 5 楼 DBA_Huangzj 的回复:

好像简化不了多少

;
WITH    cte
          AS ( SELECT   Id ,
                        Mid ,
                        Picture
               FROM     News
               WHERE    Mid IN ( 118, 260, 123, 144, 143, 288 )
             )
    SELECT  *
    FROM    ( SELECT    *
              FROM      ( SELECT    * ,
                                    ids = ROW_NUMBER() OVER ( PARTITION BY Mid ORDER BY GETDATE() )
                          FROM      cte
                          WHERE     Picture <> ''
                        ) a
              WHERE     ids < 3
              UNION ALL
              SELECT    *
              FROM      ( SELECT    * ,
                                    ids = ROW_NUMBER() OVER ( PARTITION BY Mid ORDER BY GETDATE() )
                          FROM      cte
                          WHERE     Picture = ''
                        ) b
              WHERE     ids < 8
            ) c
    ORDER BY mid ,
            id

谢谢了 http://bbs.csdn.net/topics/390894275 这个帖子你能帮我看看怎么解决吗？

[jayzhihui]

ORDER BY 只能用在最后！