某公司的一个面试题求解(不是很难)

Question

一家小公司的面试题:
  id       tcode_name     num      parent_id
      1        甲           0          0
      2        乙           0          0
      3        电视机       3          1
      4        冰箱         4          1
      5        冰箱         2          2
      6        电视机       1          2
      7        空调         1          2

使用1条sql语句统计出甲组和乙组各有多少种家电及各种家电的数量

Answer 1

select a.tcode_name,b.tcode_name,sum(num) num
from 
(select * from tb where parent_id=0)a,
(select * from tb where parent_id<>0)b
where a.id=b.parent_id
group by a.tcode_name,b.tcode_name

Answer 2

做一个自连接然后使用group by，
select b.tcode_name,sum(a.num) from test a join test b on(b.id=a.parent_id) group by b.tcode_name;

Answer 3

select b.tcode_name, count(*) appliance_count, sum(a.num) appliance_num
from mytt a join mytt b
on(a.parent_id=b.id)
group by b.tcode_name

Answer 4

create table tb1(id int, tcode_name varchar(50),num int,parent_id int);
insert into tb1 
select 1,'甲',0,0
union all
select 2,'已',0,0
union all
select 3,'电视机',3,1
union all
select 4,'冰箱',4,1
union all
select 5,'冰箱',2,2
union all
select 6,'电视机',1,2
union all
select 6,'空调',1,2select * from tb1
select * from tb1 t1 where t1.parent_id=0
select * from tb1 t2 where t2.parent_id <>0select a.tcode_name,b.tcode_name,  sum(b.num) '数量'
from (select * from tb1 t1 where t1.parent_id=0) a,(select * from tb1 t2 where t2.parent_id <>0) b 
where a.id=b.parent_id 
group by a.tcode_name,b.tcode_name order by a.tcode_name,b.tcode_name/**
--结果
甲 冰箱 4
甲 电视机 3
已 冰箱 2
已 电视机 1
已 空调 1
*/

Answer 5

select a.name,b.name,sum(b.num) num 
from 
(select * from tb where parent_id=0)a, 
(select * from tb where parent_id <>0)b 
where a.id=b.parent_id 
group by a.name,b.name 
这个结果符合要求：
甲          冰箱         4
乙          冰箱         2
甲          电视机      3
乙          电视机      1
乙          空调         1

Answer 6

select decode(grouping(a.tcode_name),1,'总计电器数',a.tcode_name) 个人,decode(grouping(b.tcode_name),1,'电器总数',b.tcode_name) 电器,  sum(b.num) 数量
from (select * from tb1 t1 where t1.parent_id=0) a,(select * from tb1 t2 where t2.parent_id <>0) b 
where a.id=b.parent_id 
group by rollup(a.tcode_name,b.tcode_name)

Answer 7

用connect by加上sys_connect_by_root 做两层嵌套也可以实现

Answer 8

/* Formatted on 2009/02/17 18:20 (Formatter Plus v4.8.8) */
WITH a AS
     (SELECT 1 ID, '甲' tcode_name, 0 num, 0 parent_id
        FROM DUAL
      UNION ALL
      SELECT 2, '已', 0, 0
        FROM DUAL
      UNION ALL
      SELECT 3, '电视机', 3, 1
        FROM DUAL
      UNION ALL
      SELECT 4, '冰箱', 4, 1
        FROM DUAL
      UNION ALL
      SELECT 5, '冰箱', 2, 2
        FROM DUAL
      UNION ALL
      SELECT 6, '电视机', 1, 2
        FROM DUAL
      UNION ALL
      SELECT 6, '空调', 1, 2
        FROM DUAL)
SELECT   group_name tcode_name, COUNT (*) 种类数, SUM (num) 数量
    FROM (SELECT     a.*, CONNECT_BY_ROOT (tcode_name) group_name
                FROM a
          START WITH parent_id = 0
          CONNECT BY parent_id = PRIOR ID) a
   WHERE a.parent_id > 0
GROUP BY group_nameTCODE_NAME 家电种类数 家电总数量已 3 4
甲 2 7

Answer 9

  id      tcode_name    num      parent_id 
      1        甲          0          0 
      2        乙          0          0 
      3        电视机      3          1 
      4        冰箱        4          1 
      5        冰箱        2          2 
      6        电视机      1          2 
      7        空调        1          2 使用1条sql语句统计出甲组和乙组各有多少种家电及各种家电的数量select parent_id,tcode_name,sum(num)from table
where parent_id in(
select id from table
where parent_id =0)
group by parent_id,tcode_name
order by parent_id,tcode_name

Answer 10

select a.name,b.name,sum(b.num) num
from
(select * from tb where parent_id=0)a,
(select * from tb where parent_id <>0)b
where a.id=b.parent_id
group by a.name,b.name
这个结果符合要求：
甲        冰箱        4
乙        冰箱        2
甲        电视机      3
乙        电视机      1
乙        空调        1

Answer 11

楼主是不是想得到下列结果
甲        电器种类数  2
甲        冰箱        4 
甲        电视机      3
乙        电器种类数  3
乙        冰箱        2 
乙        电视机      1 
乙        空调        1
如果这样的，我没别的办法
只能用两个SQL去Unionselect a.tcode_name,b.tcode_name,b.num
from tablename a,
     (select parent_id,'电器种类数' tcode_name,count(*) num
        from tablename
       where parent_id<>0
       group by parent_id
      union
      select parent_id,tcode_name,sum(num) num
        from tablename
       where parent_id<>0
      group by parent_id,tcode_name) b
where a.id=b.parent_id

Answer 12

WITH a AS 
    (SELECT 1 ID, '甲' tcode_name, 0 num, 0 parent_id 
        FROM DUAL 
      UNION ALL 
      SELECT 2, '已', 0, 0 
        FROM DUAL 
      UNION ALL 
      SELECT 3, '电视机', 3, 1 
        FROM DUAL 
      UNION ALL 
      SELECT 4, '冰箱', 4, 1 
        FROM DUAL 
      UNION ALL 
      SELECT 5, '冰箱', 2, 2 
        FROM DUAL 
      UNION ALL 
      SELECT 6, '电视机', 1, 2 
        FROM DUAL 
      UNION ALL 
      SELECT 6, '空调', 1, 2 
        FROM DUAL) 
        select substr(tcode_name, 1, 1) as tcode_name, count(*) as cnt, sum(num)
        from (
        select a.id, a.num, a.parent_id, ltrim(sys_connect_by_path(a.tcode_name, ','), ',') as tcode_name
        from a 
        start with parent_id = 0
        connect by  parent_id = prior id
        ) x
        where x.parent_id != 0
        group by substr(x.tcode_name, 1, 1)