Question

请大神帮我分析一下下面的sql语句的含义。

前面是主表，这是后面左关联的语句，我看不太懂。
left join (with tmp as (select card_no,max(bill_dt) as bill_dt from lm_card_bill where bill_ind = 'Y' group by card_no)
select lc.card_no,lc.ps_due_dt,lc.cur_min_pay from lm_card_bill lc,tmp where lc.card_no=tmp.card_no and lc.bill_dt=tmp.bill_dt ) lcb
on a.card_no=lcb.card_no

Answer 1

这条语句不会出错吗？MS SQL中应该是不支持这种写法的。
简单的说，这是子查询中嵌套了一个子查询，with table as 相当于建立了一个子查询，用这个子查询的表和其他表进行关联。但是一般把这个表写在最外层，比较标准和清晰。类似下面的写法
with tmp as (select card_no,max(bill_dt) as bill_dt from lm_card_bill where bill_ind = 'Y' group by card_no)

select主表
left join (select lc.card_no,lc.ps_due_dt,lc.cur_min_pay from lm_card_bill lc,tmp where lc.card_no=tmp.card_no and lc.bill_dt=tmp.bill_dt ) lcb
on a.card_no=lcb.card_no

或者
with tmp as (select card_no,max(bill_dt) as bill_dt from lm_card_bill where bill_ind = 'Y' group by card_no),

lcb as (select lc.card_no,lc.ps_due_dt,lc.cur_min_pay from lm_card_bill lc,tmp where lc.card_no=tmp.card_no and lc.bill_dt=tmp.bill_dt )
select主表
left join lcb on a.card_no=lcb.card_no

或者
left join (
select lc.card_no,lc.ps_due_dt,lc.cur_min_pay from lm_card_bill lc,(select card_no,max(bill_dt) as bill_dt from lm_card_bill where bill_ind = 'Y' group by card_no) as tmp where lc.card_no=tmp.card_no and lc.bill_dt=tmp.bill_dt ) lcb
on a.card_no=lcb.card_no

追问

这条语句是什么意思啊，能说的更通俗点吗，谢谢

追答

首先，你知道多给关联吧？select * from 主表 a left join lcb on a.card_no=lcb.card_no，这句话等同于将主表a的card_no和另一个表lcb的card_no字段相同的记录行查询出来。
然后，(select lc.card_no,lc.ps_due_dt,lc.cur_min_pay from lm_card_bill lc,tmp where lc.card_no=tmp.card_no and lc.bill_dt=tmp.bill_dt ) lcb 相当于将()中查询出来的记录，作为一个表lcb来使用。等同于 with tmp as (select card_no,max(bill_dt) as bill_dt from lm_card_bill where bill_ind = 'Y' group by card_no) 这句话是将()中查询出来的记录，作为一个给tmp来使用。