with cte_table1 as ( select * from table1 union all select * from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID ) select * from cte_table1 where id=3; sql2005以及以上可以用,sql2000的话要写程序递归或迭代了。
select * from TEMPFCYTEST start with parentid is null connect by prior id = parentid有个函数,专门处理这内问题
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID
'TopLEvel' 附近的语法不正确。如果它要用作公用表表达式,需要使用分号显式终止前一个语句。
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊把你写的语句 贴出来
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊把你写的语句 贴出来 with cte_table1 as ( select * from GoodsTags union all select * from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID ) select * from cte_table1
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊把你写的语句 贴出来 with cte_table1 as ( select * from GoodsTags union all select * from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID ) select * from cte_table1改为 select c.* from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊把你写的语句 贴出来 with cte_table1 as ( select * from GoodsTags union all select * from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID ) select * from cte_table1改为 select c.* from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID真的可以了,谢谢,但是这是为什么呢
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊把你写的语句 贴出来 with cte_table1 as ( select * from GoodsTags union all select * from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID ) select * from cte_table1改为 select c.* from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID 还是不行,只是能出结果了,但是结果的顺序不对,我要的是列一个toplevel是0然后下边跟着他的子类的
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊把你写的语句 贴出来 with cte_table1 as ( select * from GoodsTags union all select * from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID ) select * from cte_table1改为 select c.* from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID 还是不行,只是能出结果了,但是结果的顺序不对,我要的是列一个toplevel是0然后下边跟着他的子类的select * from GoodsTags where toplevel=0 union all 要自己想问题
我看上面都弄得挺复杂的,我觉得一句简单的条件查询就可以了吧 select (case when TopLevel =0 then ID else TopLevel end) as 排序列,* from category order by 排序列,ID
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊把你写的语句 贴出来 with cte_table1 as ( select * from GoodsTags union all select * from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID ) select * from cte_table1改为 select c.* from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID 还是不行,只是能出结果了,但是结果的顺序不对,我要的是列一个toplevel是0然后下边跟着他的子类的select * from GoodsTags where toplevel=0 union all 要自己想问题我也很想自己想问题,但是无从想起啊,这都是第一次见的东西
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊把你写的语句 贴出来 with cte_table1 as ( select * from GoodsTags union all select * from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID ) select * from cte_table1改为 select c.* from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID 还是不行,只是能出结果了,但是结果的顺序不对,我要的是列一个toplevel是0然后下边跟着他的子类的select * from GoodsTags where toplevel=0 union all 要自己想问题你这个出的结果也不对,算了,我还是递归吧,不走捷径了。
写代码去处理吧 也是用递归 大致可以这样写: StringBuilder str = new StringBuilder(); public void GetTree() { 先取得根节点的数据源,假设是个DataTable dt if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { str.Append(GetChildTree(dr["ID"].ToString())); } } }public string GetChildTree(string ID) { StringBuilder str1 = new StringBuilder(); 根据ID取得子节点的数据源,假设是个DataTable dt if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { str1.Append(GetChildTree(dr["ID"].ToString())); } } return str1.ToString(); } 纯手写的代码,凑合着看吧,LZ可以参考下
只适合两层的,三层的就不对了 自连接一次就可以处理3层分类 select (case when TopLevel =0 then ID when pTopLevel=0 then TopLevel else pTopLevel end) as 排序列,(case when pTopLevel =0 then ID else TopLevel end) as 排序列2,* from ( select a.*,b.TopLevel as pTopLevel from category a left join category b on a.TopLevel = b.ID ) temp order by 排序列,排序列2,ID
只适合两层的,三层的就不对了 自连接一次就可以处理3层分类 select (case when TopLevel =0 then ID when pTopLevel=0 then TopLevel else pTopLevel end) as 排序列,(case when pTopLevel =0 then ID else TopLevel end) as 排序列2,* from ( select a.*,b.TopLevel as pTopLevel from category a left join category b on a.TopLevel = b.ID ) temp order by 排序列,排序列2,ID 32个赞
create table TESTTABLE_A
(
ID varchar2(50),
NAME varchar2(50),
TOPLEVEL varchar2(50)
)insert into TESTTABLE_A values('1','家具','0');
insert into TESTTABLE_A values('2','建材','0');
insert into TESTTABLE_A values('3','卧室','1');
insert into TESTTABLE_A values('4','客厅','1');
insert into TESTTABLE_A values('5','餐厅','1');
insert into TESTTABLE_A values('6','床','3');
insert into TESTTABLE_A values('7','床垫','3');
insert into TESTTABLE_A values('8','床头柜','3');select *
From TESTTABLE_A
Start With TOPLEVEL = '0' Connect By Prior ID=TOPLEVEL-----------------------------------------------------------
ID NAME TOPLEVEL
1 家具 0
3 卧室 1
6 床 3
7 床垫 3
8 床头柜 3
4 客厅 1
5 餐厅 1
2 建材 0
大哥这是为什么
但是,你这个表结构没有体现出层级关系的字段,比如3,我这么知道对应哪个父类目录?就只有2层?
SELECT b.ID ,
b.CategoryName ,
b.OrganID
FROM dbo.WebCategory a
JOIN dbo.WebCategory b ON b.OrganID LIKE a.OrganID
+ '%'
这是我自己用过的 你可以参考下
(
select * from table1
union all
select * from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID
)
select * from cte_table1 where id=3;
sql2005以及以上可以用,sql2000的话要写程序递归或迭代了。
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。
http://bbs.csdn.net/topics/390725979
start with parentid is null
connect by prior id = parentid有个函数,专门处理这内问题
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID
'TopLEvel' 附近的语法不正确。如果它要用作公用表表达式,需要使用分号显式终止前一个语句。
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊把你写的语句 贴出来
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊把你写的语句 贴出来
with cte_table1 as
(
select * from GoodsTags
union all
select * from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID
)
select * from cte_table1
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊把你写的语句 贴出来
with cte_table1 as
(
select * from GoodsTags
union all
select * from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID
)
select * from cte_table1改为
select c.* from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊把你写的语句 贴出来
with cte_table1 as
(
select * from GoodsTags
union all
select * from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID
)
select * from cte_table1改为
select c.* from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID真的可以了,谢谢,但是这是为什么呢
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊把你写的语句 贴出来
with cte_table1 as
(
select * from GoodsTags
union all
select * from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID
)
select * from cte_table1改为
select c.* from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID
还是不行,只是能出结果了,但是结果的顺序不对,我要的是列一个toplevel是0然后下边跟着他的子类的
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊把你写的语句 贴出来
with cte_table1 as
(
select * from GoodsTags
union all
select * from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID
)
select * from cte_table1改为
select c.* from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID
还是不行,只是能出结果了,但是结果的顺序不对,我要的是列一个toplevel是0然后下边跟着他的子类的select * from GoodsTags where toplevel=0
union all 要自己想问题
select (case when TopLevel =0 then ID else TopLevel end) as 排序列,* from category
order by 排序列,ID
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊把你写的语句 贴出来
with cte_table1 as
(
select * from GoodsTags
union all
select * from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID
)
select * from cte_table1改为
select c.* from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID
还是不行,只是能出结果了,但是结果的顺序不对,我要的是列一个toplevel是0然后下边跟着他的子类的select * from GoodsTags where toplevel=0
union all 要自己想问题我也很想自己想问题,但是无从想起啊,这都是第一次见的东西
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。select c.* from table1 as c inner join cte_table1 as p on c.TopLevel = p.ID还是提示哪个,不对啊把你写的语句 贴出来
with cte_table1 as
(
select * from GoodsTags
union all
select * from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID
)
select * from cte_table1改为
select c.* from GoodsTags as c inner join cte_table1 as p on c.TopLevel = p.ID
还是不行,只是能出结果了,但是结果的顺序不对,我要的是列一个toplevel是0然后下边跟着他的子类的select * from GoodsTags where toplevel=0
union all 要自己想问题你这个出的结果也不对,算了,我还是递归吧,不走捷径了。
大致可以这样写:
StringBuilder str = new StringBuilder();
public void GetTree()
{
先取得根节点的数据源,假设是个DataTable dt
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
str.Append(GetChildTree(dr["ID"].ToString()));
}
}
}public string GetChildTree(string ID)
{
StringBuilder str1 = new StringBuilder();
根据ID取得子节点的数据源,假设是个DataTable dt
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
str1.Append(GetChildTree(dr["ID"].ToString()));
}
}
return str1.ToString();
}
纯手写的代码,凑合着看吧,LZ可以参考下
只适合两层的,三层的就不对了
自连接一次就可以处理3层分类
select (case when TopLevel =0 then ID when pTopLevel=0 then TopLevel else pTopLevel end) as 排序列,(case when pTopLevel =0 then ID else TopLevel end) as 排序列2,* from
(
select a.*,b.TopLevel as pTopLevel from category a left join category b on a.TopLevel = b.ID
) temp
order by 排序列,排序列2,ID
只适合两层的,三层的就不对了
自连接一次就可以处理3层分类
select (case when TopLevel =0 then ID when pTopLevel=0 then TopLevel else pTopLevel end) as 排序列,(case when pTopLevel =0 then ID else TopLevel end) as 排序列2,* from
(
select a.*,b.TopLevel as pTopLevel from category a left join category b on a.TopLevel = b.ID
) temp
order by 排序列,排序列2,ID
32个赞