- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- [原创]请问如何通过SQL生成一个动态表格 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=189163)
-- 发布时间：2023/11/14 16:01:00
-- [原创]请问如何通过SQL生成一个动态表格
如下表（产品表A）字段内容，产品分类和产品名称 都在同一个表中，JB字段有可能是N级，该字段最大值 的行则为 产品名称数据，其他均为 产品分类
id（产品ID） name（产品名称） sjid（上级ID） jb（级别）
0001 化妆品 0 1
0002 日用品 0 1
0003 家电 0 1
……
00010001 家护系列 0001 2
00010002 防晒系列 0001 2
00010003 美白系列 0001 2
……
00020001 环保系列 0002 2
……
00030001 电视 0003 2
00030002 热水器 0003 2
00030003 煤气炉 0003 2
……
000100010001 洗面奶 00010001 3
000100010002 身体乳 00010001 3
000300010001 液晶电视 00030001 3
000300010002 智能电视 00030001 3
……
0001000100010001 芦荟洗面奶150ml 000100010001 4
0001000100010002 牛奶身体乳750ml 000100010002 4
0003000100010001 创维液晶65寸电视 000100010002 4
0003000100010002 TCL液晶65寸电视 000100010002 4
……
需求：我想通过上表 生成如下表格式，请问这个如何直接通过一句SQL语句实现呢？
-- 发布时间：2023/11/14 16:11:00
--
select d.id,d.name,a.name as 一级分类,b.name as 二级分类,c.name as 三级分类 from (select id,name from 表A where jb=1) As a inner join (select id,name,sjid from 表A where jb=2) As b on a.id = b.sjid inner join (select id,name,sjid from 表A where jb=3) As c on b.id = c.sjid inner join (select id,name,sjid from 表A where jb=4) As d on c.id = d.sjid
-- 发布时间：2023/11/14 16:19:00
--
以下是引用有点蓝在2023/11/14 16:11:00的发言：
select d.id,d.name,a.name as 一级分类,b.name as 二级分类,c.name as 三级分类 from (select id,name from 表A where jb=1) As a inner join (select id,name,sjid from 表A where jb=2) As b on a.id = b.sjid inner join (select id,name,sjid from 表A where jb=3) As c on b.id = c.sjid inner join (select id,name,sjid from 表A where jb=4) As d on c.id = d.sjid
select d.id,d.name,a.name as 一级分类,b.name as 二级分类,c.name as 三级分类 from (select id,name from 表A where jb=1) As a inner join (select id,name,sjid from 表A where jb=2) As b on a.id = b.sjid inner join (select id,name,sjid from 表A where jb=3) As c on b.id = c.sjid inner join (select id,name,sjid from 表A where jb=4) As d on c.id = d.sjid
但是 jb（级别）这个字段是动态的，有可能不断的添加到 N，那么这样的话，这句SQL如何改呢
-- 发布时间：2023/11/14 16:27:00
--
先查询获取最大的级别，然后遍历动态生成sql
-- 发布时间：2023/11/14 16:34:00
--
我懂了，谢谢您
-- 发布时间：2023/11/15 16:38:00
--
以下是引用有点蓝在2023/11/14 16:11:00的发言：
select d.id,d.name,a.name as 一级分类,b.name as 二级分类,c.name as 三级分类 from (select id,name from 表A where jb=1) As a inner join (select id,name,sjid from 表A where jb=2) As b on a.id = b.sjid inner join (select id,name,sjid from 表A where jb=3) As c on b.id = c.sjid inner join (select id,name,sjid from 表A where jb=4) As d on c.id = d.sjid
select d.id,d.name,a.name as 一级分类,b.name as 二级分类,c.name as 三级分类 from (select id,name from 表A where jb=1) As a inner join (select id,name,sjid from 表A where jb=2) As b on a.id = b.sjid inner join (select id,name,sjid from 表A where jb=3) As c on b.id = c.sjid inner join (select id,name,sjid from 表A where jb=4) As d on c.id = d.sjid
请问这句要改成 SQLJoinTableBuilder 如何写呢？
-- 发布时间：2023/11/15 16:47:00
--
……
jb1.Add Table("s elect id,name from cs_cpb where jb=1", "id", "s elect id,name,sjid from cs_cpb where jb=2", "sjid")
jb1.Add Table("s elect id,name,sjid from cs_cpb where jb=2", "id", "s elect id,name,sjid from cs_cpb where jb=3", "sjid")
jb1.Add Table("s elect id,name,sjid from cs_cpb where jb=3", "id", "s elect id,name,sjid from cs_cpb where jb=4", "sjid")
……
Dim sl As String = jb1.BuildSQL()
Output.Show(sl)
主要是红色字体那里 如何加上 as
-- 发布时间：2023/11/15 16:48:00
--
Dim jb1 As New SQLJoinTableBuilder("临时表2", "s elect id,name from cs_cpb where jb=1")
\'Dim dt1 As fxDataSource
jb1.Conne ction Name = "数据源名称"
jb1.AddTable("s elect id,name from cs_cpb where jb=1", "id", "s elect id,name,sjid from cs_cpb where jb=2", "sjid")
jb1.AddTable("s elect id,name,sjid from cs_cpb where jb=2", "id", "s elect id,name,sjid from cs_cpb where jb=3", "sjid")
jb1.AddTable("s elect id,name,sjid from cs_cpb where jb=3", "id", "s elect id,name,sjid from cs_cpb where jb=4", "sjid")
jb1.AddExp("一级分类", "a.name")
jb1.AddExp("二级分类", "b.name")
jb1.AddExp("三级分类", "c.name")
jb1.AddExp("产品名称", "d.name")
\'dt1 = jb1.BuildDataSource()
\'dt1.Show("临时表2") \'显示统计结果
Dim sl As String = jb1.BuildSQL()
Output.Show(sl)
主要是红色字体那里 如何加上 as
[此贴子已经被作者于2023/11/15 16:49:47编辑过]
-- 发布时间：2023/11/15 16:53:00
--
用不了，AddTable只能添加表，无法使用sql
-- 发布时间：2023/11/15 16:55:00
--
那就是说只能 老老实实 的写SQL语句了