This topic has been archived. It cannot be replied.
-
工作学习 / 专业技术讨论 / 这里SQL高手还是很多的。俺出个题给你们做做吧。某表A中有两列 id, type。顾名思义，ID是unique的。但是一个ID可以对应有N多个type，N不定。
sample data:
1 A
1 C
2 B
2 C
2 D
3 A
3 E
...
需要一个View得到 id, all types in comma delimited string。
上述sample data输出应该是
1 'A, C'
2 'B, C, D'
3 'A, E'
string 中各个type的顺序不作要求。
-bdbs(不多不少);
2006-11-8
{341}
(#3309538@0)
-
1）这个ID明显不是unique的，估计是combination primary key。2）如果是一个view，我想用cursor最简单明了。
-luoboyang(萝卜秧);
2006-11-8
(#3309604@0)
-
是combination primary key。id unique不是指在这个表里unique。您的答案是？
-bdbs(不多不少);
2006-11-8
(#3309609@0)
-
现在在脑子里的是：大致是建立一个cursor，然后foreach中，for one ID，用一个变量循环累加type name，完成一个后，insert到一个临时表。然后在view的最后,select * from temptable。
-luoboyang(萝卜秧);
2006-11-8
(#3309621@0)
-
我的做法：1, UDF: SELECT @t = ISNULL( @t, '' ) + ',' + type FROM table WHERE id=@id
2, SELECT udf( ID) FROM table GROUP BY id
-canadiantire(轮胎-pax et lux);
2006-11-8
{123}
(#3309632@0)
-
嗯，不错。：）// 加个id在2）里面，就是LZ想要得了。：））
-luoboyang(萝卜秧);
2006-11-8
(#3309658@0)
-
呵呵，这还不是我想要的。因为作为VIEW，在跟其它TABLE或VIEW JOIN的时候，UDF作为一个sub query，performance及其糟糕。比方说假如有1000个unique id，这个UDF就要被CALL1000次。我想知道有没有更好的解。顺便说一下，轮胎的UDF里，应该用COALESCE(@t + ',', '') + type，这样可以避免leading comma。
-bdbs(不多不少);
2006-11-8
{93}
(#3309694@0)
-
我想标准的SQL是做不了的，不同的数据库应该有不同的方案。ORACLE里面也要用到自定义函数，虽然这函数不需要访问表。写起来有一大堆。
-newkid(newkid);
2006-11-8
(#3309717@0)
-
Oracle solution (函数来自asktom)
-newkid(newkid);
2006-11-8
{2194}
(#3309731@0)
-
很Cool, 不懂Oracle，看上去好像是Aggregation 的时候，Ｏｒａｃｌｅ会调用这么个Ｈｏｏｋ似的东西，所以就不必另外查表了？
-canadiantire(轮胎-pax et lux);
2006-11-8
(#3309757@0)
-
我猜也是这样，这函数似乎是很底层的东西。
-newkid(newkid);
2006-11-8
(#3309768@0)
-
嗯，看来确实如此。这篇文章说的就是如何用Ｃ＃写一段User Defined Aggregate Function，应该符合搂住的意思
-canadiantire(轮胎-pax et lux);
2006-11-8
(#3309782@0)
-
狗了一下，发现有这么一段* Extensibility hook to aggregate values over a group during query processing似乎SQL 2005也能做类似的事情。
-canadiantire(轮胎-pax et lux);
2006-11-8
(#3309764@0)
-
for SQL Server 2005, --->SELECT DISTINCT
a.id,
(
SELECT LTrim(RTrim( type )) + ', ' AS [text()]
FROM #t1 b
WHERE b.id = a.id
ORDER BY id, type FROM XML PATH('')
) AS lst
FROM #t1 a
-digitworm(digitworm);
2006-11-8
{169}
(#3309775@0)
-
这是Ｈａｃｋ！ ：）
-canadiantire(轮胎-pax et lux);
2006-11-8
(#3309787@0)
-
for SQL Server 2000 or 7.0, I wrote a simple function (tested) -->
-digitworm(digitworm);
2006-11-9
{733}
(#3310597@0)
-
不会用view ，用两个cursor做 .实现功能。
-hard20(hard20);
2006-11-8
{990}
(#3310118@0)
-
failed! :(
-bdbs(不多不少);
2006-11-8
(#3310224@0)
-
failed ? 虽然代码比function的长，但效率不一定比那个差 。
-hard20(hard20);
2006-11-8
(#3310280@0)
-
题目要求做个view呀。：）
-bdbs(不多不少);
2006-11-9
(#3310538@0)
-
SQL 2005 ，用PIVOT做，简单又方便，外面再套一层就行了。SELECT ID, [A] AS A, [B] AS B , C AS [C] , D AS [D] , E AS [E],
F AS [F] , J AS [J] ,K AS [K] ,L AS [L]
FROM
(SELECT id,[type]
FROM test1) t1
PIVOT
(
MAX([type])
FOR [type] IN
( A, B, C, D, E ,F, J, K, L)
) AS pvt;
-hard20(hard20);
2006-11-8
{226}
(#3310120@0)
-
select ID, '('+ListAGG(type)+)' from table1 group by ID
-super__7(中奖大家有份);
2006-11-8
(#3310150@0)
-
good! 简单! 不知道sql server 2005以前的版本有没有这个listagg?
-poohbear(毛毛熊);
2006-11-8
(#3310166@0)
-
现在也没有呀。这只是一个mockup function而已。楼上那是放狗搜出来的吧。好像有点闹笑话了。呵呵。
-bdbs(不多不少);
2006-11-8
(#3310214@0)
-
Msg 195, Level 15, State 10, Line 1
'ListAGG' is not a recognized built-in function name.
--sqlserver 2005
-hard20(hard20);
2006-11-8
(#3310176@0)
-
奇怪... 我没有sql server 2005, 没办法验证...
-poohbear(毛毛熊);
2006-11-8
(#3310181@0)
-
我有。验证不过
Msg 195, Level 15, State 10, Line 2
'LISTAGG' is not a recognized built-in function name.
-hard20(hard20);
2006-11-8
(#3310190@0)
-
Although this aggregate is NOT built into SQL Server 2005....
-hard20(hard20);
2006-11-8
(#3310195@0)
-
From you link, it says
-bdbs(不多不少);
2006-11-8
{568}
(#3310221@0)
-
o, 我只看了code... google listagg有三页结果... 原来是未来世界的东西... anyhow, 跟我没有关系, 我没有....
-poohbear(毛毛熊);
2006-11-8
(#3310247@0)
-
如果你有幸用MySQL, 很简单CREATE VIEW TABLE1VIEW AS select id, GROUP_CONCAT(type) from table1 group by id;
-looi500(looi);
2006-11-8
(#3310469@0)
-
爽
-helloyou(你好！);
2006-11-8
(#3310484@0)
-
good! 这是oracle的, 估计sql server 2005以下也得按这个思路做
-poohbear(毛毛熊);
2006-11-8
(#3310499@0)
-
MySQL挺不错的，缺陷是对语法要求很低，对SQL不很熟的人很容易因为错误的语法得到莫名其妙的结果。可惜大公司还没用MySQL作数据库平台的。：（
-bdbs(不多不少);
2006-11-9
(#3310859@0)