现有一表table1,记录如下:mc lx nodes
-----------------------------------------
站点一 Y 3
站点二 Y 5
站点一 M 2
站点三 N 4想查询结果显示如下mc lx
--------------------------------
站点一 3Y,2M
站点二 5Y
站点三 4N 应该如何写sql语句?
-----------------------------------------
站点一 Y 3
站点二 Y 5
站点一 M 2
站点三 N 4想查询结果显示如下mc lx
--------------------------------
站点一 3Y,2M
站点二 5Y
站点三 4N 应该如何写sql语句?
2000用函数
2005 XML
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-21 10:43:40
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)---------------------------------*/
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([mc] NVARCHAR(10),[lx] NVARCHAR(10),[nodes] INT)
INSERT [tb]
SELECT N'站点一','Y',3 UNION ALL
SELECT N'站点二','Y',5 UNION ALL
SELECT N'站点一','M',2 UNION ALL
SELECT N'站点三','N',4
GO
--SELECT * FROM [tb]-->SQL查询如下:
SELECT DISTINCT mc,
nodes=STUFF((SELECT ','+LTRIM(nodes)+lx FROM tb WHERE mc=t.mc FOR XML PATH('')),1,1,'')
FROM tb t
/*
mc nodes
---------- -------------------
站点二 5Y
站点三 4N
站点一 3Y,2M(3 行受影响)*/
insert tb
select 001, 'AA' union all
select 001, 'BB' union all
select 001, 'CC' union all
select 002, 'DD' union all
select 002, 'EE' union all
select 003, 'FF'
go
CREATE FUNCTION dbo.f_tb(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ' ' + CardName FROM tb WHERE CardNo=@id
RETURN STUFF(@str, 1, 1, '')
END
GO SELECT CardNo, CardName = dbo.f_tb(CardNo) FROM tb GROUP BY CardNo
drop table tb
drop function dbo.f_tb
go
--2005 XML
if object_id('tb') is not null
drop table tb
create table tb(aa varchar(20),bb varchar(20))
insert into tb select '奥米茄', '地板' union all
select '瓷砖' ,'地板' union all
select '蒙娜丽莎','厨房设备' union all
select '实木复合地板' ,'涂料' select [values]=stuff((select '|'+aa from tb t where bb=tb.bb for xml path('')), 1, 1, ''),bb
from tb
group by bb
/*bb values
-------------------- --------------------
厨房设备 蒙娜丽莎
地板 奥米茄|瓷砖
涂料 实木复合地板(3 行受影响)
*/
自己参考~
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c)
1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86>
(Build 2600: Service Pack 3)
愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-21 10:47:56.217●●●●●
★★★★★soft_wsx★★★★★
*/if object_ID('TB') IS NOT NULL DROP TABLE TB
go
create table tb(mc nvarchar(20),lx nvarchar(20),nodes int)
go
insert tb
select
'站点一', 'Y' , 3 union all select
'站点二', 'Y' , 5 union all select
'站点一', 'M', 2 union all select
'站点三', 'N', 4
if object_id('f_tb') is not null drop function f_tb
go
create function f_tb(@mc nvarchar(100))
returns nvarchar(4000)
as
begin
declare @sql nvarchar(4000)
set @sql=N''
select @sql=@sql+N','+cast(nodes as nvarchar)+lx from tb where mc=@mc
set @sql=stuff(@sql,1,1,N'')
return(@sql)
end
go select mc,lx=dbo.f_tb(mc) from tb group by mc order by lx/*
mc lx
站点一 3Y,2M
站点三 4N
站点二 5Y
*/这样
看下可以不
DROP TABLE [tb]
GO
CREATE TABLE [tb]([mc] NVARCHAR(10),[lx] NVARCHAR(10),[nodes] INT)
INSERT [tb]
SELECT N'站点一','Y',3 UNION ALL
SELECT N'站点二','Y',5 UNION ALL
SELECT N'站点一','M',2 UNION ALL
SELECT N'站点三','N',4
GO
SELECT * FROM [tb]
select mc as '站点名',left(nodes,len(nodes)-1) as '合并' from
(select mc,
nodes=(select cast(nodes as varchar)+lx+',' from tb where mc=a.mc for xml path(''))
from tb a group by a.mc) b
drop table tb
go
create table tb(mc nvarchar(25),lx nvarchar(10),nodes nvarchar(10))
insert into tb
select '站点一' , 'Y' , '3' union all
select '站点二' , 'Y' , '5' union all
select '站点一' , 'M' , '2' union all
select '站点三' , 'N' , '4'
alter function test()
returns @table1 table
(
mc nvarchar(25),lx nvarchar(10)
)as
begin
declare @mc nvarchar(25)
declare @lx2 nvarchar(25)
declare @nodes2 nvarchar(10)
declare @nodes1 nvarchar(10)
declare @lx1 nvarchar(25)
declare @lx nvarchar(25) declare @Count int
declare @i int
declare @j int
declare @first nvarchar(25)
declare @last nvarchar(25) insert into @table1(mc,lx) select mc,lx from (select *,row_number() over(order by mc) as k from tb) d
set @Count=cast((select count(*) from tb) as int)
set @i=1
while @i<@Count
begin set @first=cast((select mc from (select *,row_number() over(order by mc) as k from tb) q where k=@i) as nvarchar)
set @j=@i+1
while @j<=@Count
begin
set @last=cast((select mc from (select *,row_number() over(order by mc) as k from tb) q where k=@j) as nvarchar)
set @lx2=cast((select lx from (select *,row_number() over(order by mc) as k from tb) q where k=@j) as nvarchar)
set @lx1=cast((select lx from (select *,row_number() over(order by mc) as k from tb) q where k=@i) as nvarchar)
set @nodes2=cast((select nodes from (select *,row_number() over(order by mc) as k from tb) q where k=@j) as nvarchar)
set @nodes1=cast((select nodes from (select *,row_number() over(order by mc) as k from tb) q where k=@i) as nvarchar) set @j=@j+1
if @first=@last
begin
set @lx=@nodes1+@lx1+','+@nodes2+@lx2
update @table1 set lx=@lx where mc=@last
end
end
set @i=@i+1
end
return
end
select distinct mc,lx from test()
楼主试试这个,成功了的