SELECT A.ab,B.bc,count(1)
FROM A,B
WHERE A.id=B.id
GROUP BY A.ab,B.bc;
因为B表里没有和A表相对应的id,所以查询结果没有记录,
但想在没有记录的时候使count(1)=0怎么写?
SELECT count(1)
FROM A,B
WHERE A.id=B.id
GROUP BY A.ab,B.bc;
这样是可以得到count(1)=0,但我也想当A.id=B.id的时候,取得字段A.ab的值,所以
FROM A,B
WHERE A.id=B.id
GROUP BY A.ab,B.bc;
因为B表里没有和A表相对应的id,所以查询结果没有记录,
但想在没有记录的时候使count(1)=0怎么写?
SELECT count(1)
FROM A,B
WHERE A.id=B.id
GROUP BY A.ab,B.bc;
这样是可以得到count(1)=0,但我也想当A.id=B.id的时候,取得字段A.ab的值,所以
FROM A,B
WHERE A.id=B.id(+)
GROUP BY A.ab,B.bc;
SELECT
A.ab,
B.bc,
count(B.id)
FROM A,B
WHERE A.id=B.id(+)
GROUP BY A.ab,B.bc;
FROM A,B
WHERE A.id=B.id
GROUP BY A.ab,B.bc;没有记录就直接为空,有什么问题???显示0的话,则去掉AB字段即可SELECT count(1)
FROM A,B
WHERE A.id=B.id
GROUP BY A.ab,B.bc;
select count(1)
from
(
SELECT A.ab,B.bc
FROM A,B
WHERE A.id=B.id
) C
GROUP BY A.ab,B.bc
当虚拟表C没有记录,怎样使count(1)=0?
select case when count(1)=0 then C.ab
when count(1)>0 then C.bc
else 'xxx' end
from
(
SELECT A.ab,B.bc
FROM A,B
WHERE A.id=B.id
) C
GROUP BY A.ab,B.bc
当虚拟表C没有记录,怎样使count(1)=0?
select case when count(1)=0 then C.ab
when count(1)>0 then C.bc
else 'xxx' end
from
(
SELECT A.ab,B.bc
FROM A,B
WHERE A.id=B.id
UNION ALL
SELECT NULL,NULL
FROM DUAL
) C
GROUP BY A.ab,B.bc
这样也可以实现效果
FROM A,B
WHERE A.id=B.id
GROUP BY A.ab,B.bc;
FROM A,B
WHERE A.id=B.id(+)
GROUP BY A.ab,B.bc;使用右关联,并判断,如果b.bc是空的时候,值为0,否则值为1.这样计算出来到值应该不会有偏差.