来自一个查询中的一个表的SQL子组
问题描述:
我有一个分类表,其中包含具有相应类型的国家,州,地区和城镇。项目的父项是它所在的区域,州或国家。然后是一个包含这些ID的对象的表格。特定的类别和对象可能会被发布与否。来自一个查询中的一个表的SQL子组
table: category
id title parent type published
75 Spain 0 1 1
76 Mallorca 75 2 1
144 North 76 3 1
149 South 76 3 1
148 East 76 3 1
147 West 76 3 1
170 Cala 144 4 1
195 Puebla 144 4 0
166 Pica 149 4 1
189 Playa 148 4 0
199 Santa Ana 148 4 1
247 Puerto 147 4 1
...
table: objects
id title country_id state_id region_id town_id published
4 Alba 75 76 149 195 1
5 Gris 75 76 144 170 1
8 Casa 75 76 148 199 1
8 Pueblo 75 76 147 247 1
8 Don Carlo 75 76 148 199 1
...
结果应该是按类别类型是正确数量在每一个国家,州,区,镇对象的组合名称的列表。
id title parent type obj_count
75 Spain 0 1 12
76 Mallorca 75 2 4
144 North 76 3 2
149 South 76 3 3
148 East 76 3 0
147 West 76 3 1
170 Cala 144 4 0
166 Pica 149 4 1
189 Playa 148 4 0
195 Puebla 144 4 1
247 Puerto 147 4 1
199 Santa Ana 148 4 2
and so on...
查询我到目前为止返回名称的完整列表,以正确的顺序,但只有一个完整的计数(包括未公布)的城镇,但一旦我尝试检查“WHERE ob.published = 1 AND c.published = 1'它只返回城镇,而不是国家,州或地区。
SELECT
c.id as id, c.title as title, c.type as type, c.parent as parent,
count(ob.town_id) as obj_count
FROM category AS c
LEFT JOIN category AS r ON c.id = r.parent
LEFT JOIN category AS u ON r.id = u.parent
LEFT JOIN category AS o ON u.id = o.parent
LEFT JOIN objects AS ob ON ob.town_id = c.id
WHERE 1
GROUP BY type, id, parent
ORDER BY parent, title
任何人都可以帮助我在一个查询中做到这一点?谢谢。
答
SELECT c.id, c.title, c.type, count(c.id) as cnt
FROM category c
LEFT JOIN objects o ON (o.country_id = c.id OR o.state_id = c.id OR o.region_id = c.id OR o.town_id = c.id)
GROUP BY c.id
ORDER by type
在id的基础上,你需要计数? –