现有主表Table1,副表Table2。
(表结构和SQL语句等经过极度简化,只是为了描述问题)Table1表结构如下:
ManageNo 主键,
UserName, (人名)
UserGroup(所属小组)Table2表结构如下:
ManageNo 主键,(与Table1关联)
SequenceNo 主键,(从1到99的编号)
FavoriteFood(爱吃的食物)以上所有项目类型皆为字符。主表的每条数据,在副表中可能有n条对应的数据。(n可能为0)
(每个人可能有多种爱吃的食物,也可能没有)需求:
检索UserGroup为"A"的所有数据,
表中现有如下数据:Table1的内容:
ManageNo UserName UserGroup
"M001" "张三" "A"
"M002" "李四" "A"
"M003" "王五" "A"Table2的内容:
ManageNo SequenceNo FavoriteFood
"M001" "01" "红苹果"
"M001" "02" "绿苹果"
"M001" "03" "蓝苹果"
"M002" "01" "炸猪排"
"M002" "02" "炸鸡排"要求结果格式如下:ManageNo UserName FavoriteFood
"M001" "张三" "红苹果,绿苹果,蓝苹果"
"M002" "李四" "炸猪排,炸鸡排"
"M003" "王五" ""我设想的SQL如下: SELECT UNIQUE
T1.ManageNo AS ManageNo,
NVL((SELECT
MAX(FavoriteFood) as FavoriteFood
FROM
(SELECT
LTRIM(SYS_CONNECT_BY_PATH(FavoriteFood, ','), ',') FavoriteFood
FROM
(SELECT
T2.FavoriteFood as FavoriteFood,
ROWNUM as RN
FROM
Table2 T2
WHERE
T1.ManageNo = T2.ManageNo
)
START WITH RN=1
CONNECT BY PRIOR RN = RN -1
)
), '') AS FavoriteFood
FROM
Table1 T1
WHERE
(T1.ManageNo = ?
OR ? IS NULL)问题在于
WHERE
T1.ManageNo = T2.ManageNo
这行,找不到T1这个表。请帮忙看看,谢谢啦
这个处理必须在一个SQL语句里完成,不能用存储过程,不能在程序里处理
(表结构和SQL语句等经过极度简化,只是为了描述问题)Table1表结构如下:
ManageNo 主键,
UserName, (人名)
UserGroup(所属小组)Table2表结构如下:
ManageNo 主键,(与Table1关联)
SequenceNo 主键,(从1到99的编号)
FavoriteFood(爱吃的食物)以上所有项目类型皆为字符。主表的每条数据,在副表中可能有n条对应的数据。(n可能为0)
(每个人可能有多种爱吃的食物,也可能没有)需求:
检索UserGroup为"A"的所有数据,
表中现有如下数据:Table1的内容:
ManageNo UserName UserGroup
"M001" "张三" "A"
"M002" "李四" "A"
"M003" "王五" "A"Table2的内容:
ManageNo SequenceNo FavoriteFood
"M001" "01" "红苹果"
"M001" "02" "绿苹果"
"M001" "03" "蓝苹果"
"M002" "01" "炸猪排"
"M002" "02" "炸鸡排"要求结果格式如下:ManageNo UserName FavoriteFood
"M001" "张三" "红苹果,绿苹果,蓝苹果"
"M002" "李四" "炸猪排,炸鸡排"
"M003" "王五" ""我设想的SQL如下: SELECT UNIQUE
T1.ManageNo AS ManageNo,
NVL((SELECT
MAX(FavoriteFood) as FavoriteFood
FROM
(SELECT
LTRIM(SYS_CONNECT_BY_PATH(FavoriteFood, ','), ',') FavoriteFood
FROM
(SELECT
T2.FavoriteFood as FavoriteFood,
ROWNUM as RN
FROM
Table2 T2
WHERE
T1.ManageNo = T2.ManageNo
)
START WITH RN=1
CONNECT BY PRIOR RN = RN -1
)
), '') AS FavoriteFood
FROM
Table1 T1
WHERE
(T1.ManageNo = ?
OR ? IS NULL)问题在于
WHERE
T1.ManageNo = T2.ManageNo
这行,找不到T1这个表。请帮忙看看,谢谢啦
这个处理必须在一个SQL语句里完成,不能用存储过程,不能在程序里处理
from mtba a
,(
select id
,ltrim(max(sys_connect_by_path(food,',')),',') as food
from (select id,seno,food
,row_number()over(order by id,seno)
+dense_rank()over(order by id) rn
,min(seno)over(partition by id) mno
from mtbb b
)
start with seno=mno
connect by rn-1=prior rn
group by id
) b
where a.id=b.id(+)
大概是类似下列的情形:
SQL> select no,q from test
2 /
NO Q
---------- ------------------------------
001 n1
001 n2
001 n3
001 n4
001 n5
002 m1
003 t1
003 t2
003 t3
003 t4
003 t5
003 t6
12 rows selected
最后要得到类似于如下的结果:
001 n1;n2;n3;n4;n5
002 m1
003 t1;t2;t3;t4;t5;t6
通常大家都认为这类问题无法用一句SQL解决,本来我也这么认为,可是今天无意中突然有了灵感,原来是可以这么做的:
前几天有人提到过sys_connect_by_path的用法,我想这里是不是也能用到这个方法,如果能做到的话,不用函数或存贮过程也可以做到了;要用到sys_connect_by_path,首先要自己构建树型的结构,并且树的每个分支都是单根的,例如1-〉2-〉3-〉4,不会存在1-〉2,1-〉3的情况;
我是这么构建树,很简单的,看下面的结果就会知道了:
SQL> select no,q,rn,lead(rn) over(partition by no order by rn) rn1
2 from (select no,q,row_number() over(order by no,q desc) rn from test)
3 /
NO Q RN RN1
---------- ------------------------------ ---------- ----------
001 n5 1 2
001 n4 2 3
001 n3 3 4
001 n2 4 5
001 n1 5
002 m1 6
003 t6 7 8
003 t5 8 9
003 t4 9 10
003 t3 10 11
003 t2 11 12
003 t1 12
12 rows selected
有了这个树型的结构,接下来的事就好办了,只要取出拥有全路径的那个path,问题就解决了,先看no=‘001’的分组:
select no,sys_connect_by_path(q,';') result from
(select no,q,rn,lead(rn) over(partition by no order by rn) rn1
from (select no,q,row_number() over(order by no,q desc) rn from test)
)
start with no = '001' and rn1 is null connect by rn1 = prior rn
SQL>
6 /
NO RESULT
---------- --------------------------------------------------------------------------------
001 ;n1
001 ;n1;n2
001 ;n1;n2;n3
001 ;n1;n2;n3;n4
001 ;n1;n2;n3;n4;n5
上面结果的最后1条就是我们要得结果了
要得到每组的结果,可以下面这样
select t.*,
(
select max(sys_connect_by_path(q,';')) result from
(select no,q,rn,lead(rn) over(partition by no order by rn) rn1
from (select no,q,row_number() over(order by no,q desc) rn from test)
)
start with no = t.no and rn1 is null connect by rn1 = prior rn
) value
from (select distinct no from test) t
SQL>
10 /
NO VALUE
---------- --------------------------------------------------------------------------------
001 ;n1;n2;n3;n4;n5
002 ;m1
003 ;t1;t2;t3;t4;t5;t6
对上面结果稍加处理就可以了,希望对大家有帮助:)
您给出的方案可以正常使用,非常感谢!Eric_1999(╙@^@╜) ( )
谢谢redbridge(雪燃)
不知道sql server有没有定义类似的函数