请问一下,不用函数有办法用sql语句把
col1 col2
w abc
w 123
y cde
y dd1
写成
col1 col2
w abc123
y cdedd1
这样吗?
col1 col2
w abc
w 123
y cde
y dd1
写成
col1 col2
w abc123
y cdedd1
这样吗?
解决方案 »
- 数据库服务器的CPU经常100%,请问如何解决
- acc 导入 sql 2000 ID属性没有了····
- 在新数据表中怎么样插入image类型的数据?
- 急~~~关于msde安装和登陆的问题,在线等~(搞了一天了都不能解决)
- 如何查询代打卡嫌疑人清单
- sql server 2008 不能调试语句
- 根据条件判断触发器每天的数据是否该插入还是更新,求指教。。。。
- MS SQL SERVER2000 安装失败 提示 内部错误 请与Microsoft技术支持联系
- 通过代理服务器访问外网SQLSERVER的问题。
- 急!急!急!解决必给分!
- sqlagent 里面的cmd作业提交报错
- 关于 insert 语句中的空格问题
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([col1] varchar(1),[col2] varchar(3))
insert [TB]
select 'w','abc' union all
select 'w','123' union all
select 'y','cde' union all
select 'y','dd1'
GOSELECT a.col1,
col2 =stuff((
select ''+convert(varchar(20),col2) FROM [TB] WHERE col1 = a.col1 for xml path(''))
,1,1,'')
FROM [TB] a
group by a.col1SELECT a.col1,
status =STUFF(REPLACE(REPLACE(
(select col2
FROM [TB]
where col1 = a.col1
FOR XML AUTO
), '<TB col2="', ''), '"/>', ''), 1, 1, '')
FROM [TB] a
group by a.col1 --> 查询结果
SELECT * FROM [TB]
--> 删除表格
--DROP TABLE [TB]
insert into tb values('w', 'abc')
insert into tb values('w', '123')
insert into tb values('y', 'cde')
insert into tb values('y', 'dd1')
goselect col1 , col2 = min(col2) + max(col2) from tb group by col1drop table tb/*
col1 col2
---------- --------------------
w 123abc
y cdedd1(所影响的行数为 2 行)*/如果是多个,sql 2000得用函数,2005用xml,参考如下:
/*
标题:按某字段合并字符串之一(简单合并)
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-11-06
地点:广东深圳描述:将如下形式的数据按id字段合并value字段。
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果:
id value
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即:group by id, 求 value 的和(字符串相加)
*/
--1、sql2000中只能用自定义的函数解决
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
gocreate function dbo.f_str(@id varchar(10)) returns varchar(1000)
as
begin
declare @str varchar(1000)
select @str = isnull(@str + ',' , '') + cast(value as varchar) from tb where id = @id
return @str
end
go--调用函数
select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_str
drop table tb
--2、sql2005中的方法
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
goselect id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')
from tb
group by iddrop table tb
--3、使用游标合并数据
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
declare @t table(id int,value varchar(100))--定义结果集表变量
--定义游标并进行合并处理
declare my_cursor cursor local for
select id , value from tb
declare @id_old int , @id int , @value varchar(10) , @s varchar(100)
open my_cursor
fetch my_cursor into @id , @value
select @id_old = @id , @s=''
while @@FETCH_STATUS = 0
begin
if @id = @id_old
select @s = @s + ',' + cast(@value as varchar)
else
begin
insert @t values(@id_old , stuff(@s,1,1,''))
select @s = ',' + cast(@value as varchar) , @id_old = @id
end
fetch my_cursor into @id , @value
END
insert @t values(@id_old , stuff(@s,1,1,''))
close my_cursor
deallocate my_cursorselect * from @t
drop table tb
表fresult是主表,fresultdetail表是子表,froles表跟子表有关系。可以直接使用第一种情况吗?
select top 50
a.fid as col1,
c.frname +'-'+ b.totype +'-'+ b.frvalue +'-'+ b.fsqltype as col2
from fresult a,fresultdetail b,froles c
where
a.fid = b.frid and
b.froleid = c.fid and
a.fdate >= '2010-11-02 00:00:00' and
a.fdate <= '2010-11-02 23:59:59'
;with cte as
(select top 50
a.fid as col1,
c.frname +'-'+ b.totype +'-'+ b.frvalue +'-'+ b.fsqltype as col2
from fresult a,fresultdetail b,froles c
where
a.fid = b.frid and
b.froleid = c.fid and
a.fdate >= '2010-11-02 00:00:00' and
a.fdate <= '2010-11-02 23:59:59'
) select col1,col2 =stuff((
select ''+convert(varchar(20),a.col2) FROM [cte] a WHERE a.col1 = col1 for xml path(''))
,1,1,'')
FROM cte
group by col1
感谢大大,每个col1最多有2个值,也可能只有1个。