sql跨数据库查询两个表的方法
|
admin
2011年3月15日 14:24
本文热度 7712
|
用sql，数据库文件夹下面有两个子数据库
一个是A 一个是B
A中有个表table1，有个字段id1
B中有个表table2，也有字段id2
id1和id2有交叉数据
怎么写sql语句实现，我想用id1做为条件，去查询table2中有ID1字段的数据条
该文章在 2011/3/15 14:24:22 编辑过
| |
全部评论2 |
|
admin
2011年3月15日 14:24
1.列出两个表的数据
select * from [AAA]..Table1 a inner join [BBB]..Table2 b on a.id1 = b.id2
2.只BBB表里的数据
Select * from [BBB]..Table2 b where b.id2 in(Select a.id1 from [AAA]..Table1 a)
AAA和BBB是数据库名 数据库名和表名之间放两个点
该评论在 2011/3/15 14:24:34 编辑过
|
|
admin
2011年7月1日 11:14
跨数据库查表：
SELECT *
FROM dbo.drp_SEOrder CROSS JOIN
dbo.drp_SEOrderEntry CROSS JOIN
clicksun_cuye.dbo.SEOrder CROSS JOIN
clicksun_cuye.dbo.SEOrderEntry CROSS JOIN
clicksun_yingxiao.dbo.SEOrder CROSS JOIN
clicksun_yingxiao.dbo.SEOrderEntry
跨数据库比较销售订单：
SELECT dbo.drp_SEOrder.sales_order_code AS DRP_FBillNo,
dbo.drp_SEOrder.Fdate AS Fdate,
clicksun_cuye.dbo.SEOrder.FBillNo AS cuye_FBillNo,
clicksun_yingxiao.dbo.SEOrder.FBillNo AS yingxiao_FBillNo,
dbo.drp_SEOrder.Fqty_all, dbo.drp_SEOrder.Fqty_all_c, dbo.drp_SEOrder.Fcost_all,
dbo.drp_SEOrder.Fcost_all_c, dbo.drp_SEOrderEntry.product_code,
dbo.drp_SEOrderEntry.product_name, dbo.drp_SEOrderEntry.product_spec,
dbo.drp_SEOrderEntry.FQty, dbo.drp_SEOrderEntry.FUnit_name,
dbo.drp_SEOrderEntry.FAllAmount, dbo.drp_SEOrderEntry.FAllAmount_c,
dbo.drp_SEOrderEntry.FAuxTaxPrice, dbo.drp_SEOrderEntry.FAuxTaxPrice_c,
clicksun_cuye.dbo.SEOrderEntry.FQty AS cuye_FQty,
clicksun_cuye.dbo.SEOrderEntry.FAuxTaxPrice AS cuye_FAuxTaxPrice,
clicksun_cuye.dbo.SEOrderEntry.FAllAmount AS cuye_FAllAmount,
clicksun_yingxiao.dbo.SEOrderEntry.FQty AS yingxiao_FQty,
clicksun_yingxiao.dbo.SEOrderEntry.FAuxTaxPrice AS yingxiao_FAuxTaxPrice,
clicksun_yingxiao.dbo.SEOrderEntry.FAllAmount AS yingxiao_FAllAmount,
clicksun_cuye.dbo.SEOrder.FStatus AS cuye_FStatus,
clicksun_cuye.dbo.SEOrder.FCancellation AS cuye_FCancellation,
clicksun_yingxiao.dbo.SEOrder.FStatus AS yingxiao_FStatus,
clicksun_yingxiao.dbo.SEOrder.FCancellation AS yingxiao_FCancellation
FROM clicksun_yingxiao.dbo.SEOrder RIGHT OUTER JOIN
clicksun_cuye.dbo.SEOrder RIGHT OUTER JOIN
dbo.drp_SEOrder LEFT OUTER JOIN
clicksun_cuye.dbo.SEOrderEntry RIGHT OUTER JOIN
dbo.drp_SEOrderEntry ON
clicksun_cuye.dbo.SEOrderEntry.FInterID = dbo.drp_SEOrderEntry.FInterID_c AND
clicksun_cuye.dbo.SEOrderEntry.FEntryID = dbo.drp_SEOrderEntry.FEntryID_c LEFT OUTER
JOIN
clicksun_yingxiao.dbo.SEOrderEntry ON
dbo.drp_SEOrderEntry.FInterID = clicksun_yingxiao.dbo.SEOrderEntry.FInterID AND
dbo.drp_SEOrderEntry.FEntryID = clicksun_yingxiao.dbo.SEOrderEntry.FEntryID ON
dbo.drp_SEOrder.sales_order_code = dbo.drp_SEOrderEntry.sales_order_code ON
clicksun_cuye.dbo.SEOrder.FInterID = dbo.drp_SEOrder.FInterID_c ON
clicksun_yingxiao.dbo.SEOrder.FInterID = dbo.drp_SEOrder.FInterID
WHERE (dbo.drp_SEOrder.status = 1)
该评论在 2011/7/1 11:14:53 编辑过
|
400 186 1886
