表A,如下:
ID BBBH XMDM XMMC
1 Z1 A001 库存现金
2 Z1 A002 贷款
3 Z5 A001 罚款
4 Z1 A003 管理费
5 Z1 A004 应收款
表B,如下:
BBBH JG BBRQ XMDM YE
Z1 AA 2011/6/11 A002 300
Z1 AA 2011/6/11 A004 100
Z5 AA 2011/6/11 A001 200
Z1 BB 2011/6/11 A001 500
Z1 BB 2011/6/11 A003 100
要查询出以A表中“BBBH”为“Z1”的所有“XMDM”在表B中“BBRQ”为“2011/06/11”的记录。要以表A中所列的XMDM为准,如果表B中没有也要列出来,结果如下:
JG BBRQ BBBH XMDM XMMC YE
AA 2011/6/11 Z1 A001 库存现金
AA 2011/6/11 Z1 A002 贷款 300
AA 2011/6/11 Z1 A003 管理费
AA 2011/6/11 Z1 A004 应收款 100
谢谢!
ID BBBH XMDM XMMC
1 Z1 A001 库存现金
2 Z1 A002 贷款
3 Z5 A001 罚款
4 Z1 A003 管理费
5 Z1 A004 应收款
表B,如下:
BBBH JG BBRQ XMDM YE
Z1 AA 2011/6/11 A002 300
Z1 AA 2011/6/11 A004 100
Z5 AA 2011/6/11 A001 200
Z1 BB 2011/6/11 A001 500
Z1 BB 2011/6/11 A003 100
要查询出以A表中“BBBH”为“Z1”的所有“XMDM”在表B中“BBRQ”为“2011/06/11”的记录。要以表A中所列的XMDM为准,如果表B中没有也要列出来,结果如下:
JG BBRQ BBBH XMDM XMMC YE
AA 2011/6/11 Z1 A001 库存现金
AA 2011/6/11 Z1 A002 贷款 300
AA 2011/6/11 Z1 A003 管理费
AA 2011/6/11 Z1 A004 应收款 100
谢谢!
select *
from a join b on a.xmdm = b.xmdm
where a.bbbh = 'z1' and convert(varchar(10),b.bbrq,120) = '2011-06-11'-- bbrq 是 datetime 类型吧!
from a left join b
on a.bbbh=b.bbbh and b.bbrq='2011/06/11'
where a.bbbh='Z1'
select *
from a join b on a.xmdm = b.xmdm,a.bbbh = b.bbbh
where a.bbbh = 'z1' and convert(varchar(10),b.bbrq,120) = '2011-06-11'
select *
from a join b on a.xmdm = b.xmdm and a.bbbh = b.bbbh
where a.bbbh = 'z1' and convert(varchar(10),b.bbrq,120) = '2011-06-11'