求一SQL语句,

Question

主表连接从表的时候，如果从表有多个ID对应主表，那么就取从表中最后一个ID来于主表连接，
比如主表ID  name
1   小明
2   小红从表
ID  age  AID
1   23    1
2   24    1
3   24    2
4   27    2那么就是用B表的ID为2,4的 的与A表连接--------------我项目中的问题是下面的-------------SELECT     
ck.rpmt_id AS ID, 
cus.customer_name AS 客户, 
bat.batch_code AS 批次编号, 
cd.case_id AS 案件编号, 
biz.name AS 债务人姓名, 
dict.item_name AS 催收状态, 
cd.id_no AS 证件号码, 
cd.card_no AS 卡号, 
cd.hand AS 手次, 
cd.collect_amount AS [催收金额(委案金额)], 
ck.repayment AS 还款金额, 
ck.repayment_date AS 还款日期, 
ck.operate_date AS 对账日期,
isnull(Isnull(u.[user_name],u2.[user_name]),'')AS 业务员,
Isnull(isnull(u.login_name,u2.login_name),'')AS 业务员登录名,
cd.rates AS 费率, 
ISNULL(cd.rates * cd.repayment, '0.00') AS 佣金
FROM    dbo.t_check_log AS ck INNER JOIN
      dbo.t_business_case AS biz ON biz.case_id = ck.case_id INNER JOIN
      dbo.t_card_info AS cd ON cd.card_id = ck.card_id INNER JOIN
      dbo.t_batch AS bat ON bat.batch_id = biz.batch_id LEFT OUTER JOIN
      dbo.t_customer AS cus ON cus.customer_id = bat.customer_id LEFT OUTER JOIN
      dbo.t_dict_item AS dict ON dict.item_value = biz.collect_status AND dict.dict_code = 'CollectionStatus' left outer join
      t_case_assign_log as tca on  tca.case_id = ck.case_id AND (CONVERT(date, ck.repayment_date) BETWEEN CONVERT(date, tca.assign_date) AND CONVERT(date, tca.retrieve_date)) AND (tca.is_closed = 1)  left outer join
      t_user as u on u.[user_id]=tca.clerk  left outer join 
      t_user u2 on u2.[user_id]=biz.clerk 
WHERE     (ck.is_deleted = 0) 

Answer 1

select max(id),aid from 从表 group by aid用这个来关联

Answer 2

create table 主表
(ID int, name varchar(10))insert into 主表
 select 1, '小明' union all
 select 2, '小红'create table 从表
(ID int, age int, AID int)insert into 从表
 select 1, 23, 1 union all
 select 2, 24, 1 union all
 select 3, 24, 2 union all
 select 4, 27, 2
select a.ID,a.name,b.age
 from 主表 a
 inner join
 (select AID,age,
         row_number() over(partition by AID order by ID desc) 'rn'
  from 从表) b on a.ID=b.AID and b.rn=1/*
ID          name       age
----------- ---------- -----------
1           小明         24
2           小红         27(2 row(s) affected)
*/

Answer 3

create table #taba
(ID int, name varchar(10))insert into #taba
 select 1, '小明' union all
 select 2, '小红'create table #tabb
(ID int, age int, AID int)insert into #tabb
 select 1, 23, 1 union all
 select 2, 24, 1 union all
 select 3, 24, 2 union all
 select 4, 27, 2 ;with cte as
 (
 select ROW_NUMBER()over(partition by aid order by id)num,* from #tabb
 ),cte2 as
 (
 select * from cte where num=2
 )
 select #taba.ID,#taba.name,cte2.age from #taba
 left join cte2 on #taba.ID=cte2.AID
------------------------------------------------------------------------------------------------
ID          name       age
----------- ---------- -----------
1           小明         24
2           小红         27(2 行受影响)

Answer 4

if object_id('M_user') is not null
drop table M_user
go
create table M_user
(
id int primary key identity,
name nvarchar(10)
)
go
insert into M_user
select '小明' union all
select '小红'go
if object_id('S_user') is not null
drop table S_user
go
create table S_user
(
id int primary key identity,
age int,
aid int
)
go
insert into S_user
select 23,1 union all
select 24,1 union all
select 24,2 union all
select 27,2 go
select M_user.*,S_user_1.id as s_id,S_user.age from M_user
inner join
(
select max(id)as id,aid from S_user group by aid
)S_user_1
on M_user.id = S_user_1.aid
inner join
S_user on S_user.id =S_user_1.id

Answer 5

create table #主表(ID int, name varchar(10)) 
insert into #主表 select 1, '小明' union all select 2, '小紅' create table #从表(ID int, age int, AID int)
 insert into #从表 select 1, 23, 1 
 union all select 2, 24, 1 
 union all select 3, 24, 2
  union all select 4, 27, 2
 
 select * from #主表
 select * from #从表
  
 
 select a.*,ab.age 
 from #主表 as a
 cross apply ( select top 1 (b.id) ,b.age from  #从表  b
 where b.AID=a.ID
 order by b.id  desc
 )  as ab
 
  
  drop table #主表
  drop table #从表

Answer 6

上面的都用row_number来实现，效率都不是太好，下面的效率就好很多：
create table #taba
(ID int, name varchar(10))insert into #taba
 select 1, '小明' union all
 select 2, '小红'create table #tabb
(ID int, age int, AID int)insert into #tabb
 select 1, 23, 1 union all
 select 2, 24, 1 union all
 select 3, 24, 2 union all
 select 4, 27, 2
;with cte as
(
select tt.AID,
       tt.age
from
(
select AID,
   MAX(ID) as id
from #tabb
group by AID
)t
inner join #tabb tt
        on tt.AID = t.AID
           and tt.ID = t.id
)select #taba.ID,
       #taba.name,
       cte.age
       cte
from #taba
left join cte 
       on #taba.ID=cte.AID

Answer 7

不好意思，后面有些错了，修改一下：create table #taba
(ID int, name varchar(10))insert into #taba
 select 1, '小明' union all
 select 2, '小红'create table #tabb
(ID int, age int, AID int)insert into #tabb
 select 1, 23, 1 union all
 select 2, 24, 1 union all
 select 3, 24, 2 union all
 select 4, 27, 2
;with cte as
(
select tt.AID,
       tt.age
from
(
select AID,
   MAX(ID) as id
from #tabb
group by AID
)t
inner join #tabb tt
        on tt.AID = t.AID
           and tt.ID = t.id
)select #taba.ID,
       #taba.name,
       cte.age
from #taba
left join cte 
       on #taba.ID=cte.AID

Answer 8

直接对比2个语句的性能：
--我的语句：查询开销26%
;with cte as
(
select tt.AID,
       tt.age
from
(
select AID,
   MAX(ID) as id
from #tabb
group by AID
)t
inner join #tabb tt
        on tt.AID = t.AID
           and tt.ID = t.id
)select #taba.ID,
       #taba.name,
       cte.age
from #taba
left join cte 
       on #taba.ID=cte.AID

Answer 9

WITH temp_1(id, name )
     AS (SELECT 1    AS id,
                '小明' AS name
         UNION ALL
         SELECT 2,
                '小红'),
     temp_2(id, age, aid)
     AS (SELECT 1  AS id,
                23 age,
                1  aid
         UNION ALL
         SELECT 2,
                24,
                1
         UNION ALL
         SELECT 3,
                24,
                2
         UNION ALL
         SELECT 4,
                27,
                2)
SELECT *
FROM   temp_1
       INNER JOIN (SELECT Max(id) AS id,
                          aid
                   FROM   temp_2
                   GROUP  BY aid) a
         ON temp_1.id = a.aid 

Answer 10

本帖最后由 DBA_Huangzj 于 2013-10-09 13:52:04 编辑