Union用法

一、Union用法：

SQL Statement 1
UNION ALL
SQL Statement 2

(1)
Select * from (
(
Select Top 10 n.* ,m.Menu_Name from  News as n
inner join Menu as m On n.Menu_Pk=m.Menu_Pk where n.Menu_Pk='21'
)
union all
(
Select Top 10 n.* ,m.Menu_Name from  News as n
inner join Menu as m On n.Menu_Pk=m.Menu_Pk where n.Menu_Pk='22'
)
)as t1
Order by News_Time Desc

(2)

Select * from
(
Select * from (Select Top 10 n.* ,m.Menu_Name from  News as n
inner join Menu as m On n.Menu_Pk=m.Menu_Pk where n.Menu_Pk='21'  Order by News_Time desc

) as ti

union all
Select * from

(Select Top 10 n.* ,m.Menu_Name from  News as n
inner join Menu as m On n.Menu_Pk=m.Menu_Pk where n.Menu_Pk='22'

)as t2
 )as t
Order by News_Time Desc

Union有两种:union ，union all区别是是否记录出现重复，UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。

注意：如果想对用Union写的语句查询出的结果集进行排序，在最后排序。

 /// <summary>
        /// 根据栏目kind查询出各个栏目的最新十条新闻并且是审核通过///////////////////////////////创建缓存
        /// </summary>
        public DataTable GetKindTenNews(int Kind)
        {
            StringBuilder strSql = new StringBuilder();
            string sts = "审核通过";
                strSql.Append("Select * from (");
                if (Kind == 3)
                {
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=21 And News_Check='" + sts + "') ");
                    strSql.Append(" Union ");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=22 And News_Check='" + sts + "') ");
                    strSql.Append(" Union ");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=23 And News_Check='" + sts + "' ) ");
                    strSql.Append(" Union");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=25 And News_Check='" + sts + "' ) ");
                    strSql.Append(" Union");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=26  And News_Check='" + sts + "') ");
                    strSql.Append(" Union");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=27 And News_Check='" + sts + "') ");
                    strSql.Append(" Union");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=28 And News_Check='" + sts + "') ");
                    strSql.Append(" Union");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=29 And News_Check='" + sts + "') ");
                }
                else if (Kind == 4)
                {
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=30 And News_Check='" + sts + "') ");
                    strSql.Append(" Union ");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=36 And News_Check='" + sts + "') ");
                    strSql.Append(" Union ");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=37 And News_Check='" + sts + "') ");
                    strSql.Append(" Union");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=38 And News_Check='" + sts + "') ");
                    strSql.Append(" Union");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=39 And News_Check='" + sts + "') ");
                    strSql.Append(" Union");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=40 And News_Check='" + sts + "' ) ");
                    strSql.Append(" Union");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=41 And News_Check='" + sts + "') ");
                    strSql.Append(" Union");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk And News_Check='" + sts + "'");
                    strSql.Append("where n.Menu_Pk=42 ) ");
                }
                else if (Kind == 5)
                {
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=43 And News_Check='" + sts + "') ");
                    strSql.Append(" Union ");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=44 And News_Check='" + sts + "') ");
                    strSql.Append(" Union ");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=45 And News_Check='" + sts + "') ");
                    strSql.Append(" Union");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=46 And News_Check='" + sts + "') ");
                    strSql.Append(" Union");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=47 And News_Check='" + sts + "') ");
                    strSql.Append(" Union");
                    strSql.Append("( Select Top 10 n.* ,m.Menu_Name from ");
                    strSql.Append("News as n inner join Menu as m ");
                    strSql.Append("On n.Menu_Pk=m.Menu_Pk ");
                    strSql.Append("where n.Menu_Pk=48 And News_Check='" + sts + "') ");
                }
            strSql.Append(" ) as t1 ");
            strSql.Append("Order By News_Time Desc ");
            dp.CommandText = strSql.ToString();
            dp.Parameters.Add("News_Kind", Kind);
            DataTable Studt = dp.ExecuteDataTable();
            return Studt;
        }