sql - 如何使用 Distinct 在查询选择中获取计数元素？

Question

SELECT DISTINCT 
           t1.name as t1_name, 
           t1.unit as t1_unit, 
           t1.id_producer_goods AS hi_id_producer_goods, 
          t2.name as t2_name 
FROM Table1 t1 
    left join Table2 t2 on t1.id_web_site=t2.id_web_site 
WHERE t1.id='23'

如何在查询中获取计数 t1.name？

我检查代码:

SELECT DISTINCT 
           t1.name as t1_name,
           count(t1.name) as count,           
           t1.unit as t1_unit, 
           t1.id_producer_goods AS hi_id_producer_goods, 
           t2.name as t2_name 
FROM Table1 t1 
    left join Table2 t2 on t1.id_web_site=t2.id_web_site 
WHERE t1.id='23'

但是代码有错误:

Column 'Table1.name' is invalid in the select list because
it is not contained in either an aggregate function or the GROUP BY clause.

我知道我在 count(t1.name) as count 行中写的不对， 但是如何正确计数 t1.name？

P.S.:查询得到具有唯一行 t1.name, t1.unit, t1.id_producer_goods, t2.name 的结果。 Count t1.name 应显示每个元素 t1.name 的所有计数。

Answer 1

你必须添加

GROUP BY t1.name,t1.unit,hi.id_producer_goods,t2.name 

每当您对其他列使用聚合函数时，您必须为其余列添加 group by 子句。

对于这些，您可以删除不同的。 Group by 将为您完成。