怎么统计表中的笔记？ [英] How count notes from table?

问题描述

您好！我需要从表格菜单中显示标题类别并计算表格汽车中的注释，其中menu.id = cars.marka。所以，我想得到下一个：宝马（4），其中4是表汽车的票据数量。

我试过这样，但它不正确：

  SELECT  menu.title，COUNT（cars.id_cars） AS 总计
  FROM 菜单
  LEFT   JOIN  cars  ON （cars.marka = menu.id  AND 菜单。 parent_id = 96 
  AND  menu.block = 3）

解决方案

< blockquote>你需要一个 GROUP BY menu.title 语句让COUNT工作，

如下所示...

  SELECT  
 menu.title，
 COUNT（cars.id_cars） AS 总计
  FROM  
 menu 
  LEFT   JOIN  
 cars 
  ON （cars.marka = menu.id  AND  menu.parent_id =  96   AND  menu.block =  3 ）
  GROUP   BY  menu.title 

Jardin1写道：

我想得到下一个：宝马（4），其中4是表汽车的票据数量。

在这种情况下你需要使用 HAVING 子句如下：

  DECLARE   @ pid   INT  
  DECLARE   @ blo   INT  
  DECLARE   @ cou   INT  
 
  SET   @ pid  =  96  
  SET   @ blo  =  3  
 < span class =code-keyword> SET   @ cou  =  4    -    no。宝马;） 
 
  SELECT  m.title 
  FROM  menu  AS  m  LEFT   JOIN  cars  AS  c  ON  c.marka = m.id 
  WHERE  m.parent_id = @pid 和 m.block = @ blo 
  GROUP   BY  m.title 
  HAVING  COUNT（c.id_cars）=  @ cou  

更多：

HAVING（T-SQL） [ ^ ]

GROUP BY（T-SQL） [ ^ ]

聚合函数（T -SQL） [ ^ ]

Hello! I need to show title categories from table Menu and count notes from table Cars, where menu.id = cars.marka. So, i wanna get the next: BMW (4), where 4 is number of notes from table Cars.
I tried such, but it not correct:

SELECT menu.title, COUNT( cars.id_cars ) AS total
FROM menu
LEFT JOIN cars ON (cars.marka = menu.id AND menu.parent_id =96
AND menu.block =3)

解决方案

You need a GROUP BY menu.title statement for the COUNT to work,

Do like below...

SELECT 
      menu.title, 
      COUNT(cars.id_cars) AS total
FROM 
      menu
LEFT JOIN 
      cars 
ON (cars.marka = menu.id AND menu.parent_id = 96 AND menu.block = 3)
GROUP BY menu.title

Jardin1 wrote:

i wanna get the next: BMW (4), where 4 is number of notes from table Cars.

In this case you need to use HAVING clause as follow:

DECLARE @pid INT
DECLARE @blo INT
DECLARE @cou INT

SET @pid = 96
SET @blo = 3
SET @cou = 4 --no. of BMW ;)

SELECT m.title
FROM menu AS m LEFT JOIN cars AS c ON c.marka = m.id 
WHERE m.parent_id =@pid AND m.block =@blo
GROUP BY m.title
HAVING COUNT(c.id_cars) = @cou

More:
HAVING (T-SQL)[^]
GROUP BY (T-SQL)[^]
Aggregate functions (T-SQL)[^]

这篇关于怎么统计表中的笔记？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！