拓狼
-
个人空间
- 组别:管理员
- 性别:
- 来自:北京 海淀
- 积分:1112
- 帖子:615
- 注册:
2007-05-23
|
回复: 帮我看看这个语句怎么写?谢谢
--产生测试数据 create table a(Date datetime,Code int,Type int,Name varchar(50))insert into a select '1999-07-17 0:00:00', 3, 1, '信达'union all select '1994-03-29 0:00:00', 3, 1, '深圳蔚深'union all select '1993-02-27 0:00:00', 3, 1, '深圳国际'union all select '1999-07-17 0:00:00', 3, 2, '通商'union all select '1999-07-17 0:00:00', 3, 3, '深圳中审'union all select '1999-07-17 0:00:00', 3, 3, '广发华福'union all select '1999-07-17 0:00:00', 3, 3, '湘财'--解决方法，可以参见 http://www.windbi.com/showtopic-962.aspxwith tb(code,type,name)as( SELECT distinct code,type,STUFF((SELECT ',' + name AS [text()] FROM a N WHERE n.code = m.code and n.type=m.type FOR XML PATH('')), 1, 1, '') as name FROM a m)select a.code,a.name as '主销',b.name as '分销',c.name as '其他' from tb a inner join tb b on a.code=b.code inner join tb c on a.code=c.code where a.type=1 and b.type=2 and c.type=3貌似性能上有些欠缺。 -------------------------------------------------------------------------------------------------- 突然想起可以改用pivot来实现，具体如下： with tb(code,type,name)as( SELECT distinct code,type,STUFF((SELECT ',' + name AS [text()] FROM a N WHERE n.code = m.code and n.type=m.type FOR XML PATH('')), 1, 1, '') as name FROM a m)SELECT code,[1] as 主销,[2] as 分销,[3] as 其他 FROM tbPIVOT( max(name) FOR type IN ([1],[2],[3])) AS pvt性能应该有很大改进。
 拓狼 最后编辑于 2008-04-16 16:16:31
虽有智慧,不如乘势;虽有鎡基,不如待时。
君子学以聚之,问以辨之,宽以居之,仁以行之。
独学而无友，则孤陋而寡闻。
|
