如何从3表中获取记录。 [英] How to Get record From 3 table.?
本文介绍了如何从3表中获取记录。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我第三次发布任务,
>这里有3个表
>支付表是计算表,它只有一个产品数据
> rcvamt和restamt从付款表中获取
收藏:
id(PK)clientid公司产品总额
1 2001 Company1 Product1 50000
2 2002 Company2 Product2 60000
3 2003 Company3 Product3 70000
PaymentData:
wid(PK)wcid(FK)clientid product rcvamt restamt
1 1 2001产品1 500 49500
2 1 2001产品1 800 48700
3 2 2002产品2 1500 58500
付款
id(PK)wid(FK) clientid product rcvamt restamt
1 1 2001 Product1 1300 48700
2 3 2002 Product2 1500 58500
i想要报告如
clientid公司procudt total rcvamt restamt
2001 Company1 Product1 50000 1300 48700
2002 Company2 Product2 60000 1500 58500
2003 Company3 Product3 70000
解决方案
你好,
我不太了解你的问题,
但我认为这可能会有所帮助
----创建表格脚本
创建表格集合
(
id int主键,
clientid Varchar(6),
公司Varchar(30),
产品Varchar(30 ),
总钱
)
插入收藏价值('1','2001', 'Company1','Product1','50000')
插入集合值('2','2002','Company2','Product2','60000')
插入集合值('3','2003','Company3','Product3','70000')
创建表格PaymentData
(
wid int主键,
wcid int FOREIGN KEY REFERENCES集合(id),
clientid Varchar(6),
产品Varchar(30),
rcvamt money,
res tamt money
)
插入PaymentData值('1','1','2001','Product1','500 ','49500')
插入PaymentData值('2','1','2001','Product1','800','48700')
插入PaymentData值('3','2','2002','Product2','1500','58500')
创建表格付款
(
id int主键,
wid int FOREIGN KEY REFERENCES PaymentData(wid),>
clientid Varchar(6),
产品Varchar(30),
rcvamt money,
restamt money,>
)
插入付款值('1','1','2001','Product1' ,'1300','48700')
插入付款值('2','3','2002','Product2','1500','58500')
---创建表后你的解决方案应该是
选择A.clientid,A.company,A.product,A.total,B.rcvamt,B.restamt From Collection A
Left Join Payment B
On A.id = B.id
i posting queston third time,
>here are 3 table
>payment table is computed table and its have only one product data
>rcvamt and restamt get from payment table
Collection:
id(PK) clientid company product total
1 2001 Company1 Product1 50000
2 2002 Company2 Product2 60000
3 2003 Company3 Product3 70000
PaymentData:
wid(PK) wcid(FK) clientid product rcvamt restamt
1 1 2001 Product1 500 49500
2 1 2001 Product1 800 48700
3 2 2002 Product2 1500 58500
Payment
id(PK) wid(FK) clientid product rcvamt restamt
1 1 2001 Product1 1300 48700
2 3 2002 Product2 1500 58500
i want to shaw report like
clientid company procudt total rcvamt restamt
2001 Company1 Product1 50000 1300 48700
2002 Company2 Product2 60000 1500 58500
2003 Company3 Product3 70000
解决方案
Hello,
I didn't understood your question well,
but i think this might help
----Create Table Script
Create Table Collection
(
id int Primary key ,
clientid Varchar(6),
company Varchar(30),
product Varchar(30),
total money
)
Insert Into Collection Values ('1','2001','Company1','Product1','50000')
Insert Into Collection Values ('2','2002','Company2','Product2','60000')
Insert Into Collection Values ('3','2003','Company3','Product3','70000')
Create Table PaymentData
(
wid int Primary key ,
wcid int FOREIGN KEY REFERENCES Collection(id),
clientid Varchar(6),
product Varchar(30),
rcvamt money,
restamt money
)
Insert Into PaymentData Values('1','1','2001','Product1','500','49500')
Insert Into PaymentData Values('2','1','2001','Product1','800','48700')
Insert Into PaymentData Values('3','2','2002','Product2','1500','58500')
Create Table Payment
(
id int Primary key ,
wid int FOREIGN KEY REFERENCES PaymentData(wid),
clientid Varchar(6),
product Varchar(30),
rcvamt money ,
restamt money,
)
Insert Into Payment Values('1','1','2001','Product1','1300','48700')
Insert Into Payment Values('2','3','2002','Product2','1500','58500')
---After Creating tables your solution should be
Select A.clientid,A.company,A.product,A.total,B.rcvamt,B.restamt From Collection A
Left Join Payment B
On A.id = B.id
这篇关于如何从3表中获取记录。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文