select 内容,ID form table group by 内容,ID 类别区分是标准啊 看似毫无规律
汗,这种SQL语句写出来是骂自己啊
SQL 2000中需要用到函数的实现,测试表中根据你的条件增加了一个category字段,用于分类另外,你那个测试中写的东西有点脏,所以去除了create table tb ( id int , value varchar(10) , No varchar(10) , category int ) insert into tb values ( 1, 'SSS', '101', 1 ) insert into tb values ( 2, 'SSS', '102', 1 ) insert into tb values ( 3, 'SSS', '103', 1 ) insert into tb values ( 4, 'SSS', '210', 2 ) insert into tb values ( 5, 'SSS', '204', 2 ) insert into tb values ( 6, 'SSS', '201', 2 ) insert into tb values ( 7, 'SSS', '304', 3 ) insert into tb values ( 8, 'FFF', '305', 3 ) insert into tb values ( 9, 'FFF', '123', 1 ) insert into tb values ( 10, 'FFF', '112', 1 ) insert into tb values ( 11, 'FFF', '345', 3 ) insert into tb values ( 12, 'FFF', '244', 2 ) insert into tb values ( 13, 'FFF', '222', 2 ) go CREATE FUNCTION dbo.f_str ( @value varchar(10) , @category int ) RETURNS varchar(8000) AS BEGIN DECLARE @r varchar(8000) SET @r = '' SELECT @r = @r + ',' + cast(id as varchar(10)) FROM tb WHERE value = @value and category = @category RETURN STUFF(@r, 1, 1, '') END GO CREATE FUNCTION dbo.f_str1 ( @value varchar(10) , @category int ) RETURNS varchar(8000) AS BEGIN DECLARE @r varchar(8000) SET @r = '' SELECT @r = @r + ',' + No FROM tb WHERE value = @value and category= @category RETURN STUFF(@r, 1, 1, '') END GO GO -- 调用函数 SELECt value , dbo.f_str(value, category) as 内容 , dbo.f_str1(value, category)as 号码 , category FROM tb GROUP BY value , categorygo drop table tb drop function dbo.f_str drop function dbo.f_str1
用一个计算列,可能更有意思一点create table tb ( id int , value varchar(10) , No varchar(10) , category as substring(no, 1, 1) ) insert into tb values ( 1, 'SSS', '101' ) insert into tb values ( 2, 'SSS', '102' ) insert into tb values ( 3, 'SSS', '103' ) insert into tb values ( 4, 'SSS', '210' ) insert into tb values ( 5, 'SSS', '204' ) insert into tb values ( 6, 'SSS', '201' ) insert into tb values ( 7, 'SSS', '304' ) insert into tb values ( 8, 'FFF', '305' ) insert into tb values ( 9, 'FFF', '123' ) insert into tb values ( 10, 'FFF', '112' ) insert into tb values ( 11, 'FFF', '345' ) insert into tb values ( 12, 'FFF', '244' ) insert into tb values ( 13, 'FFF', '222' ) go CREATE FUNCTION dbo.f_str ( @value varchar(10) , @category char(1) ) RETURNS varchar(8000) AS BEGIN DECLARE @r varchar(8000) SET @r = '' SELECT @r = @r + ',' + cast(id as varchar(10)) FROM tb WHERE value = @value and category = @category RETURN STUFF(@r, 1, 1, '') END GO CREATE FUNCTION dbo.f_str1 ( @value varchar(10) , @category char(1) ) RETURNS varchar(8000) AS BEGIN DECLARE @r varchar(8000) SET @r = '' SELECT @r = @r + ',' + No FROM tb WHERE value = @value and category = @category RETURN STUFF(@r, 1, 1, '') END GO GO -- 调用函数 SELECt value , dbo.f_str(value, category) as 内容 , dbo.f_str1(value, category) as 号码 , category as 分类 FROM tb GROUP BY value , categorygo drop table tb drop function dbo.f_str drop function dbo.f_str1 GO
类别区分是标准啊 看似毫无规律
id int
, value varchar(10)
, No varchar(10)
, category int
)
insert into tb
values ( 1, 'SSS', '101', 1 )
insert into tb
values ( 2, 'SSS', '102', 1 )
insert into tb
values ( 3, 'SSS', '103', 1 )
insert into tb
values ( 4, 'SSS', '210', 2 )
insert into tb
values ( 5, 'SSS', '204', 2 )
insert into tb
values ( 6, 'SSS', '201', 2 )
insert into tb
values ( 7, 'SSS', '304', 3 )
insert into tb
values ( 8, 'FFF', '305', 3 )
insert into tb
values ( 9, 'FFF', '123', 1 )
insert into tb
values ( 10, 'FFF', '112', 1 )
insert into tb
values ( 11, 'FFF', '345', 3 )
insert into tb
values ( 12, 'FFF', '244', 2 )
insert into tb
values ( 13, 'FFF', '222', 2 )
go CREATE FUNCTION dbo.f_str (
@value varchar(10)
, @category int
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + cast(id as varchar(10))
FROM tb
WHERE value = @value
and category = @category
RETURN STUFF(@r, 1, 1, '')
END
GO CREATE FUNCTION dbo.f_str1 (
@value varchar(10)
, @category int
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + No
FROM tb
WHERE value = @value
and category= @category
RETURN STUFF(@r, 1, 1, '')
END
GO
GO -- 调用函数
SELECt value
, dbo.f_str(value, category) as 内容
, dbo.f_str1(value, category)as 号码
, category
FROM tb
GROUP BY value
, categorygo
drop table tb
drop function dbo.f_str
drop function dbo.f_str1
id int
, value varchar(10)
, No varchar(10)
, category as substring(no, 1, 1)
)
insert into tb
values ( 1, 'SSS', '101' )
insert into tb
values ( 2, 'SSS', '102' )
insert into tb
values ( 3, 'SSS', '103' )
insert into tb
values ( 4, 'SSS', '210' )
insert into tb
values ( 5, 'SSS', '204' )
insert into tb
values ( 6, 'SSS', '201' )
insert into tb
values ( 7, 'SSS', '304' )
insert into tb
values ( 8, 'FFF', '305' )
insert into tb
values ( 9, 'FFF', '123' )
insert into tb
values ( 10, 'FFF', '112' )
insert into tb
values ( 11, 'FFF', '345' )
insert into tb
values ( 12, 'FFF', '244' )
insert into tb
values ( 13, 'FFF', '222' )
go CREATE FUNCTION dbo.f_str (
@value varchar(10)
, @category char(1)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + cast(id as varchar(10))
FROM tb
WHERE value = @value
and category = @category
RETURN STUFF(@r, 1, 1, '')
END
GO CREATE FUNCTION dbo.f_str1 (
@value varchar(10)
, @category char(1)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + No
FROM tb
WHERE value = @value
and category = @category
RETURN STUFF(@r, 1, 1, '')
END
GO
GO -- 调用函数
SELECt value
, dbo.f_str(value, category) as 内容
, dbo.f_str1(value, category) as 号码
, category as 分类
FROM tb
GROUP BY value
, categorygo
drop table tb
drop function dbo.f_str
drop function dbo.f_str1
GO
SQL 语句所支持的汇总函数没有字符串连接的。所以,你必须分类查询后,用循环代码自行连接。
可楼主要的是 SQL ,我写出来有什么意义呢。SQL语句我不会,你的代码我也无法测试。对你的代码我也无法进行评价…………