需要使用join在sql server 2008中对以下输出进行sql查询 [英] Need a sql query for the following output in sql server 2008 using join

问题描述

< td> Rages

父表
Pid	姓名	地址
1	Ragu	xyz
2	yyy

< tr>

Child1
Child1Id	Pid	金额
1	1	100
2	1	200
3	2	300

Child2
Child2Id	Pid	费用
1	1	1000

输出应该是这样的



< td>姓名
< tr>

Pid	地址	Child1Id	金额	Child2Id	费用
1	Ragu	xyz	1	100	null	null
1	Ragu	xyz	1	200	null	null
1	Ragu	xyz	null	null	1	1000

Parent table
Pid	Name	Address
1	Ragu	xyz
2	Rages	yyy

Child1
Child1Id	Pid	Amount
1	1	100
2	1	200
3	2	300

Child2
Child2Id	Pid	Expense
1	1	1000

Ouput should be like this

Pid	Name	Address	Child1Id	Amount	Child2Id	Expense
1	Ragu	xyz	1	100	null	null
1	Ragu	xyz	1	200	null	null
1	Ragu	xyz	null	null	1	1000

推荐答案

SELECT p.Pid, Name, Address, Child1Id, Amount, Child2Id, Expense 
FROM 
    (SELECT [Parent table].Pid, Name, Address, Child1Id, Amount
     FROM [Parent table] LEFT OUTER JOIN Child1 ON [Parent table].Pid = Child1.Pid) p
          LEFT OUTER JOIN Child2 ON p.Pid = Child2.Pid

注意：我认为A你的意思是Ragu，你只是在展示部分示例。如果您希望将输出过滤到Pid = 1，则添加：

Notes: I've assumed that by "A" you mean "Ragu" in your example and that you were only showing part of the example. If you want ouput filtered to just Pid = 1 then add:

WHERE p.Pid = 1

经过反思，我想我会添加一些警告：

此类查询可以引入笛卡尔积（参见本文） [ ^ ]获取完整说明）。



如果您对数据集进行聚合，笛卡尔积会导致混淆并导致错误答案。聚合的典型解决方案是在嵌套的子选择中执行聚合。

On reflection, I thought I'd add some words of caution:
This type of query can introduce a "cartesian product" (see this article[^] for a full explanation).

Cartesian products can cause confusion and will lead to wrong answers if you carry out aggregation on your dataset. A typical solution for aggregation is to carry out your aggreagation in your nested sub-select.

试试这个

try this

select p.pid,p.name,p.address ,c1.childid,c1.amount,c2.child2id,c2.expence from paraent p left outer join child c1 on p.pid=c1.childid left outer join child2 c2 on p.pid=c2.child2id where c1.childid=p.pid  union all

select p.pid,p.name,p.address ,c1.childid,c1.amount ,c2.child2id,c2.expence from paraent p full join child2 c2 on p.pid=c2.child2id left outer join child c1 on p.pid=c1.childid   where c2.child2id= p.pid

你好，



检查一下...

Hi,

Check this...

SELECT a.pid,a.name,a.address,b.Child1Id,b.amount,null as 'Child2Id', null as 'Expense' from parent a
 left outer join child1 b on a.pid=b.pid
WHERE a.pid=1
union
SELECT a.pid,a.name,a.address,null as 'Child1Id', null as 'amount',b.Child2Id,b.Expense from parent a
left outer join child2 b on a.pid=b.pid
WHERE a.pid=1

希望这会给你电子邮件预期的输出。



干杯

Hope this will give you expected output.

Cheers

这篇关于需要使用join在sql server 2008中对以下输出进行sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！