SQL联合/连接查询 [英] SQL Union/Join Query

问题描述

我有一个用户个人资料表，其中包含用户名，经理等许多字段，例如金额..

I have a user profile table with columns User Name, manager and many other fields, for example amount..

示例记录： -

User    Manager  Amount
A       B       100
x       y       200
B       C       300
M       N       800
C       D       500
P       Q       1000
D       E       1000

我试图得到如下结果： -

I am trying to get the result as below:-

User    Manager Amount
A       B       100
B       C       300
C       D       500
D       E       1000

基本上，我只是想以级联的方式显示结果，以便所有行都被抓取，直到找到所有管理者在一个用户的父权制。有人可以帮忙吗？

Basically, I just want to show the results in cascading manner so that all the rows are fetched until it finds all managers in the heirarchy for a user. Can somebody help?

============

============

更新

Update

我设法使用以下查询解决了问题： -

I managed to solved the problem using the below query:-

WITH rec（c1 ，c2）
AS（SELECT c1，c2 FROM table WHERE c2 ='A'
UNION ALL
SELECT table.c1，table.c2 FROM table，rec WHERE table.c2 = rec。 c2）
SELECT c1，c2 FROM rec

WITH rec(c1, c2) AS (SELECT c1, c2 FROM table WHERE c2 = 'A' UNION ALL SELECT table.c1, table.c2 FROM table, rec WHERE table.c2 = rec.c2) SELECT c1, c2 FROM rec

感谢您的帮助。
Abhi。

Thanks for the help. Abhi.

推荐答案

没有标准sql用于执行此操作，但大多数数据库都扩展了SQL

there is no "standard" sql for doing that, However most databases extend SQL to have these options.

在Oracle中，您可以执行以下操作：

In Oracle you can do something like:

SELECT USER, Manager, Amount FROM employees CONNECT BY PRIOR USER = Manager;

请看一些例子：
http://www.ibm.com/developerworks/data/library/techarticle/dm-0510rielau/
为db2示例

please see some examples in: http://www.ibm.com/developerworks/data/library/techarticle/dm-0510rielau/ for db2 examples

这篇关于SQL联合/连接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！