加入列SQL查询 [英] Join columns SQL query

问题描述

我有以下两个问题;

Hi,

I have the following two queries;

SELECT c.name as "Customer", count(p.name) as "Registered Extensions" FROM location l, phones p, customers c WHERE c.parent=2 AND p.username=l.username AND c.id=p.customer GROUP BY c.name

SELECT c.name as "Customer", count(p.name) as "Total Extensions" FROM phones p, customers c WHERE c.parent=2 AND c.id=p.customer GROUP BY c.name
ORDER BY "Registered Extensions" DESC, c.name

如何使用以下列返回结果？：



客户|注册扩展|总延期



第一个查询返回：



客户|已注册的扩展程序

Cust1 | 10

Cust2 | 12

Cust3 | 61



第二个查询返回：



客户|总延期

Cust1 | 25

Cust2 | 36

Cust3 | 84



因此最终查询应该返回：

客户|注册扩展|总延期

Cust1 | 10 | 25

Cust2 | 12 | 36

Cust3 | 61 | 84



我尝试过：



使用，加入，内部Join，Left Join，Union，With

How can I return a result with the following columns?:

Customer | Registered Extensions | Total Extensions

The first query returns:

Customer | Registered Extensions
Cust1 | 10
Cust2 | 12
Cust3 | 61

The second query returns:

Customer | Total Extensions
Cust1 | 25
Cust2 | 36
Cust3 | 84

So the final query should return:
Customer | Registered Extensions | Total Extensions
Cust1 | 10 | 25
Cust2 | 12 | 36
Cust3 | 61 | 84

What I have tried:

Using, Join, Inner Join, Left Join, Union, With

推荐答案

根据您提供的输出和预期结果，我建议使用Common Table Expression（CTE）。将查询包装在CTE中，按客户连接，然后选择所需的列。这是一个例子

Based on the output and expected result you had provided, I would recommend to use Common Table Expression (CTE). Wrap both the query in the CTE , join them by customer and them select the desired column. Here is an example

WITH cteQuery1 AS (
	SELECT c.name as "Customer", count(p.name) as "Registered Extensions" 
	FROM location l, phones p, customers c WHERE c.parent=2 AND p.username=l.username AND c.id=p.customer 
	GROUP BY c.name
),  cteQuery2 AS (
	SELECT c.name as "Customer", count(p.name) as "Total Extensions" 
	FROM phones p, customers c WHERE c.parent=2 AND c.id=p.customer GROUP BY c.name
	ORDER BY "Registered Extensions" DESC, c.name
)
SELECT c1.Customer, c1.[Registered Extensions], t.[Total  Extensions] FROM cteQuery1 c1
JOIN cteQuery2 t
ON c1.Customer = t.Customer

输出：

Output:

Customer	Registered Extensions	Total  Extensions
Cust1	        10	                25
Cust2	        12	                36
Cust3	        61	                84

select

aa.name，

aa.RegisteredExtensions，

（SELECT c.name asCustomer，count（ p.name）asRegistered ExtensionsFROM location l，phones p，customers c WHERE c.parent = 2 AND p.username = l.username AND c.id = p.customer GROUP BY c.name）as aa

内部联接

（SELECT c.name asCustomer，count（p.name）asTotal ExtensionsFROM phones p，customers c WHERE c.parent = 2 AND c.id = p.customer GROUP BY c.name）as bb

on（aa.name = bb.name）

ORDER BY Registered Extensions DESC，aa.name

select
aa.name,
aa.RegisteredExtensions,
bb.TotalExtensions from
(SELECT c.name as "Customer", count(p.name) as "Registered Extensions" FROM location l, phones p, customers c WHERE c.parent=2 AND p.username=l.username AND c.id=p.customer GROUP BY c.name)as aa
inner join
(SELECT c.name as "Customer", count(p.name) as "Total Extensions" FROM phones p, customers c WHERE c.parent=2 AND c.id=p.customer GROUP BY c.name) as bb
on(aa.name=bb.name)
ORDER BY Registered Extensions DESC, aa.name

另一个解决方案：

Another solution:

SELECT
    c.name As "Customer",
    (
        SELECT Count(1) 
        FROM phones As p 
        INNER JOIN locations As l 
        ON l.username = p.username 
        WHERE p.customer = c.id
    ) As "Registered Extensions",
    (
        SELECT Count(1) 
        FROM phones As p 
        WHERE p.customer = c.id
    ) As "Total Extensions"
FROM
    customers As C
WHERE
    c.parent = 2
;

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