一、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;
}
支付宝扫一扫
微信扫一扫
最新评论