如何在连接三个表时包含零计数?

huangapple go评论55阅读模式
英文:

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

Demo here

答案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>

huangapple
  • 本文由 发表于 2023年5月11日 18:50:52
  • 转载请务必保留本文链接:https://go.coder-hub.com/76226820.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定