高分求解决关于联合查询的排序问题

waiyzm 2009-09-05 02:58:18
我有三个表：BrandMessage,ALLCompany和MessageBoard,我把这三个表进行联合查询，Sql语句是这样写的：

select * from (
select top 100 percent brandID,brandJoinDate,tableName='a' from BrandMessage order by brandJoinDate desc

union
select top 100 percent companyID,companyJoinDate,tableName='b' from ALLCompany order by companyJoinDate
desc

union
select top 100 percent messageID,messageDate,tableName='c' from MessageBoard order by messageDate desc

) a order by brandJoinDate desc

tableName 是一个新添加的字段，以区分是哪个表的数据，我用group by tableName加到括号外面排结果都是一样的，
加在里面排就报错为：列名：tableName 无效

显示的结果是:


我想要的效果是:
brandID brandJoinDate tableName

7 2009-08-21 18:15:12.000 a
4 2009-08-21 15:07:59.000 a
3 2009-08-20 10:26:24.000 a
2 2009-08-14 19:04:27.000 a
1 2009-08-05 15:03:04.000 a
7 2009-08-21 18:16:45.000 b
5 2009-08-21 15:07:14.000 b
4 2009-08-20 10:24:46.000 b
2 2009-08-14 19:02:52.000 b
1 2009-08-05 15:00:34.000 b
5 2009-08-11 18:06:44.437 c
4 2009-08-10 17:21:49.107 c
3 2009-08-10 17:14:13.450 c
2 2009-08-10 17:11:27.250 c
1 2009-08-10 17:01:45.140 c

请问我应该怎么排，才能达到我想要结果，请大哥们赐教，有点急？？
...全文
104 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
waiyzm 2009-09-05
  • 打赏
  • 举报
 回复
没想到大哥们这么热心，我用了三楼的方法，可以，谢谢啦，也谢谢ws_hgo的热心回答，谢谢啦！！结贴！！
ws_hgo 2009-09-05
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 js_szy 的回复:]
果果很强大！鉴定完毕！呵呵


最好有表结构，再贴几个记录，会更快解决
[/Quote]
ws_hgo 2009-09-05
  • 打赏
  • 举报
 回复 
if object_id('tempdb.dbo.#BrandMessage') is not null drop table #BrandMessage

create table #BrandMessage([brandID] int,[brandJoinDate] datetime)

insert #BrandMessage

select 7,'2009-08-21 18:15:12.000' union all

select 4,'2009-08-21 15:07:59.000' union all

select 3,'2009-08-20 10:26:24.000' union all

select 2,'2009-08-14 19:04:27.000' union all

select 1,'2009-08-05 15:03:04.000'

--> 测试数据：#ALLCompany

if object_id('tempdb.dbo.#ALLCompany') is not null drop table #ALLCompany

create table #ALLCompany([companyID] int,[companyJoinDate] datetime)

insert #ALLCompany

select 7,'2009-08-21 18:16:45.000' union all

select 5,'2009-08-21 15:07:14.000' union all

select 4,'2009-08-20 10:24:46.000' union all

select 2,'2009-08-14 19:02:52.000' union all

select 1,'2009-08-05 15:00:34.000'

--> 测试数据：#MessageBoard

if object_id('tempdb.dbo.#MessageBoard') is not null drop table #MessageBoard

create table #MessageBoard([messageID] int,[messageDate] datetime)

insert #MessageBoard

select 5,'2009-08-11 18:06:44.437' union all

select 4,'2009-08-10 17:21:49.107' union all

select 3,'2009-08-10 17:14:13.450' union all

select 2,'2009-08-10 17:11:27.250' union all

select 1,'2009-08-10 17:01:45.140'





select * from

( 

  select brandID,brandJoinDate,'a' as tableName from #BrandMessage 

  union  all  

  select companyID,companyJoinDate,'b' as tableName from #ALLCompany    

  union all

  select messageID,messageDate,'c' as tableName from #MessageBoard  

) a  

order by tableName,brandJoinDate desc 



brandID     brandJoinDate           tableName

----------- ----------------------- ---------

7           2009-08-21 18:15:12.000 a

4           2009-08-21 15:07:59.000 a

3           2009-08-20 10:26:24.000 a

2           2009-08-14 19:04:27.000 a

1           2009-08-05 15:03:04.000 a

7           2009-08-21 18:16:45.000 b

5           2009-08-21 15:07:14.000 b

4           2009-08-20 10:24:46.000 b

2           2009-08-14 19:02:52.000 b

1           2009-08-05 15:00:34.000 b

5           2009-08-11 18:06:44.437 c

4           2009-08-10 17:21:49.107 c

3           2009-08-10 17:14:13.450 c

2           2009-08-10 17:11:27.250 c

1           2009-08-10 17:01:45.140 c



(15 行受影响)
华夏小卒 2009-09-05
  • 打赏
  • 举报
 回复
果果很强大！鉴定完毕！呵呵


最好有表结构，再贴几个记录，会更快解决
百年树人 2009-09-05
  • 打赏
  • 举报
 回复 
--> 测试数据：#BrandMessage

if object_id('tempdb.dbo.#BrandMessage') is not null drop table #BrandMessage

create table #BrandMessage([brandID] int,[brandJoinDate] datetime)

insert #BrandMessage

select 7,'2009-08-21 18:15:12.000' union all

select 4,'2009-08-21 15:07:59.000' union all

select 3,'2009-08-20 10:26:24.000' union all

select 2,'2009-08-14 19:04:27.000' union all

select 1,'2009-08-05 15:03:04.000'

--> 测试数据：#ALLCompany

if object_id('tempdb.dbo.#ALLCompany') is not null drop table #ALLCompany

create table #ALLCompany([companyID] int,[companyJoinDate] datetime)

insert #ALLCompany

select 7,'2009-08-21 18:16:45.000' union all

select 5,'2009-08-21 15:07:14.000' union all

select 4,'2009-08-20 10:24:46.000' union all

select 2,'2009-08-14 19:02:52.000' union all

select 1,'2009-08-05 15:00:34.000'

--> 测试数据：#MessageBoard

if object_id('tempdb.dbo.#MessageBoard') is not null drop table #MessageBoard

create table #MessageBoard([messageID] int,[messageDate] datetime)

insert #MessageBoard

select 5,'2009-08-11 18:06:44.437' union all

select 4,'2009-08-10 17:21:49.107' union all

select 3,'2009-08-10 17:14:13.450' union all

select 2,'2009-08-10 17:11:27.250' union all

select 1,'2009-08-10 17:01:45.140'



-->查询

select * from  ( 

  select brandID,brandJoinDate,tableName='a' from #BrandMessage 

  union  all  

  select companyID,companyJoinDate,tableName='b' from #ALLCompany    

  union all

  select messageID,messageDate,tableName='c' from #MessageBoard  

) a  

order by  

  tableName,

  brandJoinDate  desc 



/**

brandID     brandJoinDate           tableName

----------- ----------------------- ---------

7           2009-08-21 18:15:12.000 a

4           2009-08-21 15:07:59.000 a

3           2009-08-20 10:26:24.000 a

2           2009-08-14 19:04:27.000 a

1           2009-08-05 15:03:04.000 a

7           2009-08-21 18:16:45.000 b

5           2009-08-21 15:07:14.000 b

4           2009-08-20 10:24:46.000 b

2           2009-08-14 19:02:52.000 b

1           2009-08-05 15:00:34.000 b

5           2009-08-11 18:06:44.437 c

4           2009-08-10 17:21:49.107 c

3           2009-08-10 17:14:13.450 c

2           2009-08-10 17:11:27.250 c

1           2009-08-10 17:01:45.140 c



(15 行受影响)



**/
oyctzl 2009-09-05
  • 打赏
  • 举报
 回复
3樓的正解
luckyleaf521 2009-09-05
  • 打赏
  • 举报
 回复
支持3楼
ws_hgo 2009-09-05
  • 打赏
  • 举报
 回复 
create table #TB

(

  A char(10),

  B char(10),

  C char(10)

)

insert into #TB select 'a1','b1','c1'

union all select 'a2','b2','c2'

union all select 'a3','b3','c3'



select * from

(

 select *,'a' as tableName from #TB

union all

select *,'b' as tableName from #TB

union all

select *,'c' as tableName from #TB

)T



A          B          C          tableName

---------- ---------- ---------- ---------

a1         b1         c1         a

a2         b2         c2         a

a3         b3         c3         a

a1         b1         c1         b

a2         b2         c2         b

a3         b3         c3         b

a1         b1         c1         c

a2         b2         c2         c

a3         b3         c3         c
ws_hgo 2009-09-05
  • 打赏
  • 举报
 回复 
select * from  ( 

  select top 100 percent  brandID,brandJoinDate,'a' as a from BrandMessage order by brandJoinDate desc 



  union    

  select top 100 percent companyID,companyJoinDate,'b' as b from ALLCompany order by companyJoinDate    

  desc 



  union 

  select top 100 percent messageID,messageDate,'c' as c from MessageBoard order by messageDate desc 



) a  order by  brandJoinDate  desc
struggle1 2009-09-05
  • 打赏
  • 举报
 回复
学习中。。。。
试试三楼的 。。
waiyzm 2009-09-05
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 ws_hgo 的回复:]
SQL codeselect*from (selecttop100percent brandID,brandJoinDate,'a'from BrandMessageorderby brandJoinDatedescunionselecttop100percent companyID,companyJoinDate,'b'from ALLCompanyorderby companyJoinDa¡­
[/Quote]
ws_hgo 你好，我执行的时候报错，说：服务器: 消息 8155，级别 16，状态 2，行 1
没有为第 3 列（属于 'a'）指定列。
百年树人 2009-09-05
  • 打赏
  • 举报
 回复 
select * from  ( 

  select brandID,brandJoinDate,tableName='a' from BrandMessage 

  union  all  

  select companyID,companyJoinDate,tableName='b' from ALLCompany    

  union all

  select messageID,messageDate,tableName='c' from MessageBoard  

) a  

order by  

  tableName,

  brandJoinDate  desc
ws_hgo 2009-09-05
  • 打赏
  • 举报
 回复 


select * from  ( 

  select top 100 percent  brandID,brandJoinDate,'a' from BrandMessage order by brandJoinDate desc 



  union    

  select top 100 percent companyID,companyJoinDate,'b' from ALLCompany order by companyJoinDate    

  desc 



  union 

  select top 100 percent messageID,messageDate'c' from MessageBoard order by messageDate desc 



) a  order by  brandJoinDate  desc
ws_hgo 2009-09-05
  • 打赏
  • 举报
 回复
0
软考高项论文50分秘籍——心法篇
讲师以52分和53分通过论文，2次高分通过，方法论经得起考验
解决 NDVI 按年查找最大值问题
MySQL 使用 Group By 进行最大值查询
Elasticsearch（Es搜索（简单使用、全文查询、复合查询）、地理位置查询、特殊查询、聚合操作、桶聚合、管道聚合)
match_phrase_prefix 是针对分片级别的查询，假设 max_expansions 为 1，可能返回多个文档，但是只有一个词，这是我们预期的结果。有的时候实际返回结果和我们预期结果并不一致，原因在于这个查询是分片级别的，不同...
2021最新华为面经分享：Java高分面试指南（25分类1000题50w字解析
20、TreeMap（可排序） 21、LinkHashMap（记录插入顺序） 22、泛型类 23、类型通配符? 24、类型擦除 第三分类 Java中的IO与NIO面试题 第四分类 Java反射面试题 第五分类 Java序列化面试题 ...
2021年Java高分面试指南横空出世！1000道面试题+300W字解析！
一次偶然，从朋友那里得到一份“java高分面试指南”，里面涵盖了25个分类的面试题以及详细的解析：JavaOOP、Java集合/泛型、Java中的IO与NIO、Java反射、Java序列化、Java注解、多线程&并发、JVM、Mysql、Redis...
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章