求一简单的查询SQL

Question

表结构：
prodtype(id name parentid)
很明显的自关联的表，有的parentid是0，代表没有parent
得到的查询结果：
一级类别ID 一级类别名称 二级类别ID 二级类别名称
如果没有子分类，就只要父分类。select p.id, p.name,c.id,c.name from prodtype c ,prodtype p where c.parentid = p.id order by p.id;
这样有好多分类查不到。多谢了！

Answer 1

select p.id, p.name,c.id,c.name 
from prodtype c 
right join prodtype p on c.parentid = p.id 
order by p.id;

Answer 2

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'prodtype')
BEGIN
DROP TABLE prodtype
END
GO
CREATE TABLE prodtype
(
id INT,
name VARCHAR(10),
parentid INT
)
GO
INSERT INTO prodtype
SELECT 1,'aa',0 UNION
SELECT 2,'bb',1 UNION
SELECT 3,'cc',1 
GOSELECT P.id, P.name,C.id,C.name 
FROM prodtype P 
LEFT OUTER JOIN prodtype C ON C.parentid = P.id 
ORDER BY P.idid name id name
1 aa 2 bb
1 aa 3 cc
2 bb NULL NULL
3 cc NULL NULL

Answer 3

非常感谢您的回帖，但是，跟我的要求有点差距：
table：
id      name      parentid
1       餐饮美食  0
2       夏季特卖  0
3 生活娱乐  0
10      火锅      1
11      烧烤      1
31      KTV       3
32      保龄球    3
我要的结果是：
一级类别ID 一级类别名称 二级类别ID 二级类别名称
1               餐饮美食        10               火锅
1               餐饮美食        11               烧烤
2               夏季特卖
3               生活娱乐        31               KTV
3               生活娱乐        32               保龄球
但是现在的输出结果是：
+------+----------+------+--------+
| id   | name     | id   | name   |
+------+----------+------+--------+
|    1 | 餐饮美食         |   11 | 烧烤      |
|    1 | 餐饮美食         |   10 | 火锅       |
|    2 | 夏季特卖         | NULL | NULL   |
|    3 | 生活娱乐         |   31 | KTV    |
|    3 | 生活娱乐         |   32 | 保龄球
|   10 | 火锅             | NULL | NULL   |
|   11 | 烧烤             | NULL | NULL   |
|   31 | KTV              | NULL | NULL   |
|   32 | 保龄球           | NULL | NULL   |
+------+----------+------+--------+

Answer 4

select
 a.id, a.name,b.id,b.name 
from
 prodtype b 
left join
 prodtype b on b.parentid = a.id 
order by
 a.id

Answer 5

select p.id, p.name,c.id,c.name 
from prodtype c join prodtype p on c.parentid = p.id
order by p.id;

Answer 6

感谢大家的回帖，我要的结果是：
create table prodtype (id int,name varchar(10),parentid int);
insert into prodtype values(1,'餐饮美食',0);
insert into prodtype values(2,'夏季特卖',0);
insert into prodtype values(3,'生活娱乐',0);
insert into prodtype values(10,'火锅',1);
insert into prodtype values(11,'烧烤',1);
insert into prodtype values(31,'KTV',3);
insert into prodtype values(32,'保龄球',3);
我要的结果是：
id  name       id  name
1   餐饮美食   10  火锅
1   餐饮美食   11  烧烤
2   夏季特卖
3   生活娱乐   31  KTV
3   生活娱乐   32  保龄球

Answer 7

select p.id, p.name,c.id,c.name 
from prodtype c right join prodtype p on c.parentid = p.id
where not exists (select 1 from prodtype where id = p.parentid)
order by p.id;/*****************id          name       id          name
----------- ---------- ----------- ----------
1           餐饮美食       10          火锅
1           餐饮美食       11          烧烤
2           夏季特卖       NULL        NULL
3           生活娱乐       31          KTV
3           生活娱乐       32          保龄球(5 行受影响)

Answer 8

with tabParent as--父表
(select id,name from prodtype where parentid=0),
tabChild as--子类表
(select id,name,parentid from prodtype where parentid>0)
select a.id,a.name,b.id,b.name from tabparent a left join tabChild b on a.id=b.parentid--联合查询

Answer 9

select
 a.id, a.name,b.id,b.name 
from
 prodtype b 
left join
 prodtype b on b.parentid = a.id 
order by
 a.id

Answer 10

select p.id,p.name,c.id,c.name from #TabLei p left join #TabLei c on p.id=c.parentid
where p.parentid=0

Answer 11

left join on 可以不必排序了，更方便些！
select p.id,p.name,c.id,c.name from prodtype p left join prodtype c on p.id=c.parentid
where not exists (select 1 from prodtype where id = p.parentid)

Answer 12

mysql> select p.id parent_id, p.name parent_name,c.id child_id,c.name child_name
    -> from prodtype c right join prodtype p on c.parentid = p.id;
+-----------+-------------+----------+------------+
| parent_id | parent_name | child_id | child_name |
+-----------+-------------+----------+------------+
|         1 | 餐饮美食           |       10 | 火锅           |
|         1 | 餐饮美食           |       11 | 烧烤          |
|         2 | 夏季特卖           |     NULL | NULL       |
|         3 | 生活娱乐            |       31 | KTV        |
|         3 | 生活娱乐            |       32 | 保龄球           |
|        10 | 火锅            |     NULL | NULL       |
|        11 | 烧烤           |     NULL | NULL       |
|        31 | KTV         |     NULL | NULL       |
|        32 | 保龄球            |     NULL | NULL       |
+-----------+-------------+----------+------------+
这样查出来是9条记录，
where not exists (select 1 from prodtype where id = p.parentid)
这句是怎么起到过滤作用的？

Answer 13

select a.id,a.name,b.id,b.name from prodtype a
left join prodtype b on a.id=b.parentid
where b.parentid!=0 or a.parentid=0