写一个sql语句,比较复杂,自信的高手请进

wangjiamin 2004-07-14 01:58:20
b是客户表,a是联系人表,一个客户对应多个联系人。
输出数据

客户 联系人1 联系人2 联系人3。。。联系人n

要求:一个客户只能占一行,每一个联系人 占一列。 后面的空着。n标识最多联系人的客户的联系人数

表结构如下:


CREATE TABLE [b] (
[customerid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[customername] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cphone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[dq] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO




CREATE TABLE [a] (
[nameid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[name1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[phone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[mail] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[customerid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO


...全文
179 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
zonelive 2004-07-14
  • 打赏
  • 举报
回复
强,不献丑了
wangjiamin 2004-07-14
  • 打赏
  • 举报
回复
to: zjcxc(邹建)
确实不错,高的很。pfpf
zjcxc 2004-07-14
  • 打赏
  • 举报
回复
CREATE TABLE [b] (
[customerid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[customername] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cphone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[dq] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
insert b select '001','部门A','11','11'
union all select '002','部门B','22','22'
GO

CREATE TABLE [a] (
[nameid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[name1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[phone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[mail] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[customerid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
insert a select '001','A1','11','11','001'
union all select '002','A2','22','22','002'
union all select '003','A3','22','22','002'
union all select '004','A4','22','22','002'
union all select '005','A5','22','22','001'
GO

--查询
declare @s varchar(8000),@i int
select @s='',@i=max(rw)
from(select rw=count(*) from a group by customerid)a
while @i>0
select @s=',[联系人'+cast(@i as varchar)
+']=max(case sid when '+cast(@i as varchar)
+' then name1 else '''' end)'+@s
,@i=@i-1
exec('select customername'+@s+'
from(
select b.customername,a.name1,sid=(
select sum(1) from a aa
where customerid=b.customerid
and nameid<=a.nameid)
from a,b
where a.customerid=b.customerid
)a group by customername
')
go

--删除测试
drop table a,b

/*--测试结果

customername 联系人1 联系人2 联系人3
------------- --------- ---------- ----------
部门A A1 A5
部门B A2 A3 A4
--*/
zjcxc 2004-07-14
  • 打赏
  • 举报
回复
--上面搞错了,应该是显示名称,不是计数

declare @s varchar(8000),@i int
select @s='',@i=max(rw)
from(select rw=count(*) from a group by customerid)a
while @i>0
select @s=',[联系人'+cast(@i as varchar)
+']=max(case sid when '+cast(@i as varchar)
+' then name1 else '''' end)'+@s
,@i=@i-1
exec('select customername'+@s+'
from(
select b.customername,a.name1,sid=(
select sum(1) from a aa
where customerid=b.customerid
and nameid<=a.nameid)
from a,b
where a.customerid=b.customerid
)a group by customername
')
yesterday2000 2004-07-14
  • 打赏
  • 举报
回复
zjcxc 2004-07-14
  • 打赏
  • 举报
回复

declare @s varchar(8000),@i int
select @s='',@i=max(rw)
from(select rw=count(*) from a group by customerid)a
while @i>0
select @s=',[联系人'+cast(@i as varchar)
+']=sum(case sid when '+cast(@i as varchar)
+' then 1 else 0 end)'+@s
,@i=@i-1
exec('select customername'+@s+'
from(
select b.customername,sid=(
select sum(1) from a
where customerid=b.customerid
and nameid<=a.nameid)
from a,b
where a.customerid=b.customerid
)a group by customername
')

27,582

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧