UC知道ACCESS如何进行这样的多表查询?
来源：百度知道 编辑：UC知道 时间：2024/05/31 01:17:54
表class有 code,name
1 第一类
2 第二类
3 第三类
表info有 class,tt
1 a
2 b
2 c
1 d
1 e
我想得到的是每类下边一共有多少条数据,如果没有的话则为0
现在我要是这样查询的话,则没在info表中出现的就列不出来,怎么办?
SELECT c.code, c.name, count(*) AS num
FROM info AS i, class AS c
WHERE i.class=c.code
GROUP BY c.code, c.name
ORDER BY c.code;
应该怎么写才行啊?
其实里边还有个where，这样left join报错啊
SELECT c.code, c.name, count(*) AS num
FROM info AS i, class AS c
WHERE i.class=c.code and len(c.code)>1
GROUP BY c.code, c.name
ORDER BY c.code;
1 第一类
2 第二类
3 第三类
表info有 class,tt
1 a
2 b
2 c
1 d
1 e
我想得到的是每类下边一共有多少条数据,如果没有的话则为0
现在我要是这样查询的话,则没在info表中出现的就列不出来,怎么办?
SELECT c.code, c.name, count(*) AS num
FROM info AS i, class AS c
WHERE i.class=c.code
GROUP BY c.code, c.name
ORDER BY c.code;
应该怎么写才行啊?
其实里边还有个where，这样left join报错啊
SELECT c.code, c.name, count(*) AS num
FROM info AS i, class AS c
WHERE i.class=c.code and len(c.code)>1
GROUP BY c.code, c.name
ORDER BY c.code;
改用left join就可以了!
SELECT Class.Code, Class.Name, count(Info.tt) as Num
FROM Class left join Info
on Class.Code=Info.Class
group by Class.Code, Class.Name
order by Class.Code
SELECT Class.Code, Class.Name, Count(Info.tt) AS Num
FROM Class LEFT JOIN Info ON Class.Code = Info.Class
GROUP BY Class.Code, Class.Name
HAVING len(Class.Code)>1
ORDER BY Class.Code;
group by 后面加having,相当于where,where不可以和group by 一起用，有group by 的时候应该改为having!