有一个表 t1,字段如下:
t d v1 v2
a 1 1 1
a 2 2 2
b 1 1 1求一sql语句,得出以下结果
t d v1 v2
a 1\2 3 3
b 1 1 1 就是按照t来分组,v1和v2求和,d用\来连接
t d v1 v2
a 1 1 1
a 2 2 2
b 1 1 1求一sql语句,得出以下结果
t d v1 v2
a 1\2 3 3
b 1 1 1 就是按照t来分组,v1和v2求和,d用\来连接
解决方案 »
- dword 如何转换成 string
- 发送附件问题?
- 关于进销存数据库的问题!
- 如何快速确定给定的一个IP地址在本地局域网内是否存在,要求判断时间不能超过1秒!!
- 还是关于SQL.Add(...)的问题
- 奇怪问题,当数据集打开时,DBGrid1.SelectedRows.Count=0 为什么?在DBGRID中却有蓝色光标。
- label1.caption:=string(inttohex(10));为什么不行?
- IdFTPServer 问题
- 截取字符串?
- 急需:解决:corrupt table-index-header-file:c\*****\?????.db的修复工具
- 分可加+在线等:用程序实现将网络文件下载到指定文件夹
- 用等号(=) 判断和StrComp 函数判断 字符串相等有什么区别?
CREATE FUNCTION dbo.GetDStr(@t VARCHAR(10))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @tmp VARCHAR(100)
SET @tmp=''
SELECT @tmp=@tmp+CAST(d AS NVARCHAR(4)) FROM t WHERE t1=@t
IF @tmp<>'' SET @tmp=LEFT(@tmp,LEN(@tmp)-1)
RETURN @tmp
END
GO
--如果數據庫中有建立上面的函數,則查詢衹需一條語句
SELECT t, dbo.GetDStr(t), SUM(V1) AS v1 ,SUM(v2) AS v2 FROM t1 GROUP BY t
CREATE FUNCTION dbo.GetDStr(@t VARCHAR(10))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @tmp VARCHAR(100)
SET @tmp=''
SELECT @tmp=@tmp+CAST(d AS NVARCHAR(4))+'\' FROM t WHERE t1=@t
IF @tmp<>'' SET @tmp=LEFT(@tmp,LEN(@tmp)-1)
RETURN @tmp
END
GO--如果數據庫中有建立上面的函數,則查詢衹需一條語句
SELECT t, dbo.GetDStr(t), SUM(V1) AS v1 ,SUM(v2) AS v2 FROM t1 GROUP BY t
你测试了吗?我测试了怎么dbo.GetDStr(t)返回的是空值呢?
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @tmp VARCHAR(100)
SET @tmp=''
SELECT @tmp=@tmp+CAST(d AS NVARCHAR(4))+'\' FROM t1 WHERE t=@t
IF @tmp<>'' SET @tmp=LEFT(@tmp,LEN(@tmp)-1)
RETURN @tmp
END
GO--如果數據庫中有建立上面的函數,則查詢衹需一條語句
SELECT t, dbo.GetDStr(t), SUM(V1) AS v1 ,SUM(v2) AS v2 FROM t1 GROUP BY t
a 3 3
b 1 1
go
select 'a' as t, '1' as d, 1 as v1, 1 as v2
into t1
union select 'a', '2', 2, 2
union select 'b', '1', 1, 1
go
-------------------------------------------------------------------
if object_id('testfunc') is not null drop function testfunc
go
create function testfunc (@t varchar(10)) returns varchar(200)
as
begin
declare @s varchar(200)
select @s = isnull(@s + '\', '') + d from t1 where t = @t
return @s
end
go
select t, dbo.testfunc(t) as d, sum(v1) as v1, sum(v2) as v2
from t1
group by t
order by t
/*
t d v1 v2
---- ---- ---- ----
a 1\2 3 3
b 1 1 1
*/
go
-------------------------------------------------------------------
drop table t1