首页 新闻 会员 周边 捐助

with as 与 inner join 联合使用遇到的问题

0
悬赏园豆：20 [已解决问题] 解决于 2016-06-01 16:15

如题：

with result as
(
select  top 1 ROW_NUMBER() over (order by UpdatedDate desc,ActCount desc )  as RowIndex,IMEI,IMSI from Tb_A  where field_NO='xxxx'
)
select top 10 * from result a inner join Tb_B b
on a.IMEI=b.IMEI and a.IMSI=b.IMSI  order by b.CallCount desc

这条语句没有问题，可以查询出结果。但是下边的这条就无法显示结果

with result as
(
select  top 1 ROW_NUMBER() over (order by UpdatedDate desc,ActCount desc )  as RowIndex,IMEI,IMSI from Tb_A  where field_NO='xxxx'
)
select top 10 b.* from result a inner join Tb_B b
on a.IMEI=b.IMEI and a.IMSI=b.IMSI  order by b.CallCount desc

差别在于 top 10 后边加了一个 "b. "  用于查询出指定的字段。尝试过直接指定 "b.具体字段"

也无法显示查询结果，百思不得解。求高人指点。

achievie的主页 achievie | 初学一级 | 园豆：6
提问于：2016-06-01 11:52
< >
分享
最佳答案
0

额。。。

收获园豆：20
huluobo123 | 菜鸟二级 |园豆：286 | 2016-06-01 14:17
其他回答(2)
0 
with result as
(
select top 10 * from  tb_B b  inner join (
select  top 1 a.IMEI as AIMEI,a.IMSI as AIMSI from tb_A a where a.CitizenNo='211002198405312010' order by a.UpdatedDate desc,a.ActCount desc) as A
on A.AIMEI=b.IMEI and A.AIMSI=b.IMSI  order by b.CallCount desc
)
select [ID]
      ,[IMEI]
      ,[IMSI]
      ,[Phone]
      ,[Name]
      ,[LastCallDate]
      ,[CallCount]
      ,[ClearPhone]
      ,[PhoneArea]
      ,[CreatedDate]
      ,[UpdatedDate] from result

 

achievie | 园豆：6 (初学一级) | 2016-06-01 16:15
0

博主，您好；这是SQLserver的语法吗，with as

Marydon | 园豆：208 (菜鸟二级) | 2021-10-30 12:17
清除回答草稿
   您需要登录以后才能回答，未注册用户请先注册。