加入列SQL查询

问题描述

我有以下两个问题;

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

推荐答案

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
    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查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！