SQL 使用 select 语句获取子表中的记录数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16560539/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting count of records in child table using select statement
提问by DotnetSparrow
I have a stored procedure in which i am trying to select all the columns of a table Table 1. There is another table which uses Table1 primary key as foreign key. I want to count number of records in this foreign key table with that select like this:
我有一个存储过程，我试图在其中选择表 1 的所有列。还有另一个表使用 Table1 主键作为外键。我想用这样的选择来计算这个外键表中的记录数：
SELECT *, count(*) VacancyCount
FROM Table1 hc
LEFT JOIN Table2 hv
on hc.CompanyID = hv.CompanyID
WHERE hc.Deleted = 0
group by hc.CompanyID
ORDER BY NameLang1
but it gives error:
但它给出了错误：
Column 'dbo.Table1.NameLang1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
列“dbo.Table1.NameLang1”在选择列表中无效，因为它既不包含在聚合函数中也不包含在 GROUP BY 子句中。
Please suggest how to fix this?
请建议如何解决这个问题？
回答by TechDo
Please try:
请尝试：
select
*,
(select COUNT(*) from Table2 hv where hv.CompanyID=hc.CompanyID) VacancyCount
from Table1 hc
where
hc.Deleted = 0
order by hc.NameLang1, VacancyCount desc
for ordering using the new column
使用新列进行订购
select * from(
select
*,
CONVERT(NVARCHAR(100), (select COUNT(*) from Table2 hv where hv.CompanyID=hc.CompanyID)) VacancyCount
from Table1 hc
where
hc.Deleted = 0
)x
Order by CASE WHEN @OrderByParam = 1 THEN NameLang1 ELSE VacancyCount END
Provided column NameLang1and VacancyCountare of same datatype.
提供的列NameLang1和VacancyCount是相同的数据类型的。
回答by ZZa
You're doing grouping wrong. You need to use all the columns from Table 1 in SELECT instead of '*' and in GROUP BY clause as well.
你分组错了。您需要在 SELECT 而不是 '*' 和 GROUP BY 子句中使用表 1 中的所有列。
Or you can try a different approach like this:
或者您可以尝试不同的方法，如下所示：
SELECT *
FROM Table1 hc
LEFT JOIN (SELECT CompanyID, COUNT(*) cnt FROM Table2 GROUP BY CompanyID) hv
on hc.CompanyID = hv.CompanyID
WHERE hc.Deleted = 0
ORDER BY NameLang1
回答by gbn
You will have to list every column in the GROUP BY clause
These columns are those in the SELECT *bit.
您必须列出 GROUP BY 子句中的每一列。
这些列是SELECT *位中的列。
This would be correct ANSI SQL anyway.
无论如何，这都是正确的 ANSI SQL。
SELECT *itself is bad anyway: it is always better to explicitly list columns
SELECT *无论如何，它本身很糟糕：明确列出列总是更好
回答by Arun Kumar
Try in this way include column list in group by
尝试以这种方式在 group by 中包含列列表
SELECT column1,column2,column3..,NameLang1,count(*) VacancyCount
FROM Table1 hc
LEFT JOIN Table2 hv
on hc.CompanyID = hv.CompanyID
WHERE hc.Deleted = 0
group by column1,column2,column3
ORDER BY NameLang1
