英文:
How to Include zero-count when joining 3 tables?
问题
以下是您要的翻译内容:
我有3个表:Vehicle(车辆)、Type(类型)和Color(颜色)
**Vehicle(车辆)**
    id	brand	type_id	color_id
    1	Toyota	   1	   2
    2	Toyota	   2	   3
    3	GMC	       2	   1
    4	BMW	       2	   1
**Type(类型)**
    id	name
    1	Truck(卡车)
    2	Car(汽车)
**Color(颜色)**
    id	name
    1	White(白色)
    2	Red(红色)
    3	Black(黑色)
我想要获取每种类型和颜色组合的计数,包括0值,就像这样:
    type.name	color.name	        count
    Truck(卡车)	White(白色)	  0
    Truck(卡车)	Red(红色)	    1
    Truck(卡车)	Black(黑色)	  0
    Car(汽车)	White(白色)	  2
    Car(汽车)	Red(红色)	    0
    Car(汽车)	Black(黑色)	  1
我尝试了下面的查询:
```sql
SELECT type.name, color.name, count(vehicle.id) count
FROM vehicle
RIGHT JOIN type on vehicle.type_id = type.id
RIGHT JOIN color on color.id = vehicle.color_id
GROUP BY type.name, color.name
但它没有产生零值:
type.name	color.name	        count
Truck(卡车)	Red(红色)	    1
Car(汽车)	White(白色)	  2
Car(汽车)	Black(黑色)	  1
有没有一种简洁的查询可以实现第一个结果?
英文:
I have 3 tables: Vehicle, type and color
Vehicle
id	brand	type_id	color_id
1	Toyota	   1	   2
2	Toyota	   2	   3
3	GMC	       2	   1
4	BMW	       2	   1
Type
id	name
1	Truck
2	Car
Color
id	name
1	White
2	Red
3	Black
I want to get the count of each combination of type and color including 0 values, like this:
type.name	color.name	        count
Truck	      White	              0
Truck	       Red	              1
Truck	      Black	              0
Car	          White	              2
Car	           Red	              0
Car	          Black	              1
I tried this query below
SELECT type.name, color.name, count(vehicle.id) count
FROM vehicle
RIGHT JOIN type on vehicle.type_id = type.id
RIGHT JOIN color on color.id = vehicle.color_id
GROUP BY type.name, color.name
but it doesn't produce zero values:
type.name	color.name	        count
Truck	       Red	              1
Car	          White	              2
Car	          Black	              1
Is there a neat query to achieve the first results?
答案1
得分: 2
根据评论中提到的@jarlh,可以使用CROSS JOIN来完成:
WITH CTE AS (
  SELECT t.id as type_id, t.name as type_name, c.id as color_id, c.name as color_name
  FROM type t
  CROSS JOIN color c
)
SELECT c.type_name, c.color_name, count(t.color_id) as count
FROM CTE c
LEFT JOIN vehicle t on t.type_id = c.type_id and t.color_id = c.color_id
GROUP BY c.type_name, c.color_name
order by c.type_name desc, c.color_name desc
或者简单一点:
SELECT t.name as type_name, c.name as color_name, count(v.id) as count
FROM type t
CROSS JOIN color c
LEFT JOIN vehicle v on v.type_id = t.id and v.color_id = c.id
GROUP BY t.name, c.name
order by t.name desc, c.name desc
结果:
type_name	color_name	count
Truck	    White	    0
Truck	    Red	        1
Truck	    Black	    0
Car	        White	    2
Car	        Red	        0 
Car	        Black	    1
英文:
As mention @jarlh in comments, this can be done using CROSS JOIN :
WITH CTE AS (
  SELECT t.id as type_id, t.name as type_name, c.id as color_id, c.name as color_name
  FROM type t
  CROSS JOIN color c
)
SELECT c.type_name, c.color_name, count(t.color_id) as count
FROM CTE c
LEFT JOIN vehicle t on t.type_id = c.type_id and t.color_id = c.color_id
GROUP BY c.type_name, c.color_name
order by c.type_name desc, c.color_name desc
Or simply :
SELECT t.name as type_name, c.name as color_name, count(v.id) as count
FROM type t
CROSS JOIN color c
LEFT JOIN vehicle v on v.type_id = t.id and v.color_id = c.id
GROUP BY t.name, c.name
order by t.name desc, c.name desc
Results :
type_name	color_name	count
Truck	    White	    0
Truck	    Red	        1
Truck	    Black	    0
Car	        White	    2
Car	        Red	        0 
Car	        Black	    1
答案2
得分: 0
以下是翻译好的部分:
这是另一种做法。
用record_count作为(
选择
b.id [typeid],
b.name [typename],
c.id [colorid],
c.name,
[colorname],
计数(*) record_count
从车辆a
左连接类型b on a.type_id = b.id
左连接颜色c on a.color_id = c.id
按b.id,b.name,c.id,c.name分组
)。
选择a.typename,a.colorname,isnull(b.record_count,0)计数
从(
选择独特的类型.id [typeid],type.name [typename],color.id [colorid],color.name [colorname]
从车辆,类型,颜色
)作为a
左连接记录计数b on a.typeid = b.typeid
并且a.colorid = b.colorid
英文:
Here's another way of doing it.<br/>
with record_count as (
select <br>
b.id [typeid], <br>
b.name [typename], <br>
c.id [colorid], <br>
c.name, <br>
[colorname], <br>
count(*) record_count <br>
from vehicle a <br>
left join type b on a.type_id = b.id <br>
left join color c on a.color_id = c.id <br>
group by b.id, b.name, c.id, c.name <br>
) <br>
select a.typename, a.colorname, isnull(b.record_count,0) count <br>
from ( <br>
<br>
select distinct type.id [typeid],  type.name [typename], color.id <br> [colorid], color.name [colorname] <br>
from vehicle , type, color <br>
) as a <br>
left join record_count b on a.typeid = b.typeid <br>
and a.colorid = b.colorid <br>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论