SQL Server查询解决

Question

我有一个数据库，其中有多个公司名称。我需要做一个查询，将找到:公司名称，条目数，最新条目的日期。

最好按最新条目的日期排序(但我甚至可以在excel中排序)

SELECT 
/*this is the list of companies with distinct name */
DISTINCT(K.COMPANY) AS company_name,
/*where I count entries*/
(SELECT COUNT(KK.COMPANIES) FROM COMPANIES KK 
WHERE KK.COMPANY = K.company_name) AS company_count,
/*where I try to get newest entry date*/
(SELECT TOP 1 ENTRY_DATE FROM COMPANIES KKK 
WHERE KKK.COMPANY = K.company_name 
ORDER BY KKK.ENTRY_DATE DESC) AS company_date
FROM 
COMPANIES K 
WHERE
K.ENTRY_DATE > '20211004 00:00:00'  
ORDER BY
K.ID_KAMSOFT DESC

我得到一个错误:

无效的列名

中的子查询。基本上，子查询在条件xxx.COMPANY = K.company_name中看不到来自外部查询的数据。

这应该是非常简单的，但它没有工作。

Answer 1

您不需要使用子查询，尝试直接GROUP BY

SELECT 
K.COMPANY AS company_name,
COUNT(1) AS company_count,
MAX(ENTRY_DATE) AS company_date
FROM 
COMPANIES K 
WHERE
K.ENTRY_DATE > '20211004 00:00:00'   
GROUP BY    
K.COMPANY
ORDER BY
3 DESC

Answer 2

选择的答案显然更好，但这也可以工作:

(正如@Nikola markovinovic所说，你只需要使用非别名)

SELECT 
/*this is the list of companies with distinct name */
DISTINCT(K.COMPANY) AS company_name,
/*where I count entries*/
(SELECT COUNT(KK.COMPANIES) FROM COMPANIES KK 
WHERE KK.COMPANY = K.COMPANY) AS company_count,
/*where I try to get newest entry date*/
(SELECT TOP 1 ENTRY_DATE FROM COMPANIES KKK 
WHERE KKK.COMPANY = K.COMPANY
ORDER BY KKK.ENTRY_DATE DESC) AS company_date
FROM 
COMPANIES K 
WHERE
K.ENTRY_DATE > '20211004 00:00:00'  
ORDER BY
K.ID_KAMSOFT DESC