英文:
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>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论