求个sql 哈```
一张表,2个字段
表A
id, value
我现在想统计value in (3,4,5) 各自的记录数,即类似如下
value count
3 15
4 50
5 18有个问题哈``如果value=3的记录没有
那么如何让sql查出来的结果集是这样的
value count
3 0
4 50
5 18求sql`````
一张表,2个字段
表A
id, value
我现在想统计value in (3,4,5) 各自的记录数,即类似如下
value count
3 15
4 50
5 18有个问题哈``如果value=3的记录没有
那么如何让sql查出来的结果集是这样的
value count
3 0
4 50
5 18求sql`````
INSERT INTO serialno SELECT 1 FROM dual
UNION ALL SELECT 2 FROM dual
UNION ALL SELECT 3 FROM dual
UNION ALL SELECT 4 FROM dual
UNION ALL SELECT 5 FROM dual
UNION ALL SELECT 6 FROM dual
UNION ALL SELECT 7 FROM dual
UNION ALL SELECT 8 FROM dual
UNION ALL SELECT 9 FROM dual
UNION ALL SELECT 10 FROM dual
UNION ALL SELECT 11 FROM dual;
COMMIT;CREATE TABLE serialvalue(serial_no NUMBER(9),serial_value NUMBER(9));
INSERT INTO serialvalue(serial_no ,serial_value )
SELECT 2 serial_no,10 serial_value FROM dual
UNION ALL SELECT 2 serial_no,10 serial_value FROM dual
UNION ALL SELECT 3 serial_no,10 serial_value FROM dual
UNION ALL SELECT 3 serial_no,10 serial_value FROM dual
UNION ALL SELECT 4 serial_no,10 serial_value FROM dual
UNION ALL SELECT 5 serial_no,10 serial_value FROM dual
UNION ALL SELECT 9 serial_no,10 serial_value FROM dual
UNION ALL SELECT 11 serial_no,10 serial_value FROM dual ;
COMMIT;SELECT a.serial_no,nvl(sum(b.serial_value),0) FROM serialno a ,serialvalue b WHERE a.serial_no = b.serial_no (+)
GROUP BY a.serial_no
SERIAL_NO NVL(SUM(B.SERIAL_VALUE),0)
1 1 0
2 2 20
3 3 20
4 4 10
5 5 10
6 6 0
7 7 0
8 8 0
9 9 10
10 10 0
11 11 10DROP TABLE serialno;
DROP TABLE serialvalue;
SELECT a.serial_no,nvl(COUNT(b.serial_value),0) FROM serialno a ,serialvalue b WHERE a.serial_no = b.serial_no (+)
GROUP BY a.serial_no
http://topic.csdn.net/u/20081002/00/f8d90ba2-e2bb-412a-a0c5-1b6d518fc22a.html
我这里有个表可作为临时表...但是...DB中有脏数据!``sql就报错了``哎``又不能建表``烦了!``正在测试 mantisXF 的方法
很强大的说``谢谢你们哈``散分