原表T1: 一个发票有1个以上的订单号.
Inv(发票号int)---order(char)
10001 P111
10001 P112
10001 P113
10002 P114
10002 P115查询到的结果保存在下表T2中?
Inv(int)----orders(varchar)
10001 P111;P112;P113
10002 P114;P115求SQL, 谢谢!
Inv(发票号int)---order(char)
10001 P111
10001 P112
10001 P113
10002 P114
10002 P115查询到的结果保存在下表T2中?
Inv(int)----orders(varchar)
10001 P111;P112;P113
10002 P114;P115求SQL, 谢谢!
解决方案 »
- sql如何通过代理连接
- cast 问题
- 这个sql语句错在哪了?
- 散分给高手了
- 求自动编号产生算法和编码
- 使用powerdesigner15.0逆向功能,从远程服务器的sql server2000无法得到对应的PDM?
- 表中有两个字段 "pub_date" 各 "time_span" ,值为分别为 "2004-6-14 12:32:47" 和 “3”,如何写语句,生成一个新字段 valid_date,让它
- 很简单的触发器问题.自己写的触发结果不对!
- 写福彩漏期数问题.
- 备份数据库时出现42000错误:"d:\backup\20021115.mdfIO"发生不要恢复错误,backup被中止,救命啊,谢谢!!!
- 小弟求教一个递归的存储过程或者说是sql语句
- 再问一个关于SQL注入的问题
如果2005用XML
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-11 11:14:38
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Inv] int,[order] varchar(4))
insert [tb]
select 10001,'P111' union all
select 10001,'P112' union all
select 10001,'P113' union all
select 10002,'P114' union all
select 10002,'P115'
--------------开始查询--------------------------
select inv, [order]=stuff((select ','+[order] from tb t where Inv=tb.Inv for xml path('')), 1, 1, '')
from tb
group by Inv ----------------结果----------------------------
/*inv order
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10001 P111,P112,P113
10002 P114,P115(2 行受影响)
*/
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
Go
CREATE TABLE tb(aaa INT,bbb INT)
Go
INSERT INTO tb
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 1,4 UNION ALL
SELECT 2,2 UNION ALL
SELECT 2,5
GO
--查询1
select
aaa,
[values]=stuff(replace(replace((select [bbb]
from tb
where aaa=t.aaa for xml AUTO),
'"/><tb bbb="',','),
'"/>',''),1,9,'')
from tb t
group by aaa
--查询2
SELECT *
FROM(
SELECT DISTINCT
aaa
FROM tb
)A
OUTER APPLY(
SELECT
[bbb]= STUFF(REPLACE(REPLACE(
(
SELECT [bbb] FROM tb N
WHERE aaa = A.aaa
FOR XML AUTO
), '<N bbb="', ','), '"/>', ''), 1, 1, '')
)N --查询3
select
aaa,
[values]=stuff((select ','+ltrim([bbb])
from tb t
where aaa=tb.aaa for xml path('')), 1, 1, '')
from tb
group by aaa
drop table tb
--查询结果
/*
aaa values
----------- ---------
1 2,3,4
2 2,5(2 行受影响)*/
无论是在sql 2000,还是在 sql 2005 中,都没有提供字符串的聚合函数,
所以,当我们在处理下列要求时,会比较麻烦:
有表tb, 如下:
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果:
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即, group by id, 求 value 的和(字符串相加)1. 旧的解决方法-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + value
FROM tb
WHERE id=@id
RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数SELECt id, values=dbo.f_str(id)
FROM tb
GROUP BY id
if object_id('t1') is not null drop table t1
go
create table t1(Inv varchar(5), [order] varchar(4))
insert t1 select
'10001' , 'P111' union all select
'10001' , 'P112' union all select
'10001' , 'P113' union all select
'10002' , 'P114' union all select
'10002', 'P115' -- 1. 创建处理函数
if object_id('f_str') is not null drop function f_str
goCREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ';' + [order]
FROM t1
WHERE inv=@id
RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数SELECt inv, [order]=dbo.f_str(inv)
FROM t1
GROUP BY invinv order
----- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10001 P111;P112;P113
10002 P114;P115(2 行受影响)
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-11 11:14:38
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Inv] int,[order] varchar(4))
insert [tb]
select 10001,'P111' union all
select 10001,'P112' union all
select 10001,'P113' union all
select 10002,'P114' union all
select 10002,'P115'
--------------开始查询--------------------------
---2005
select inv, [order]=stuff((select ';'+[order] from tb t where Inv=tb.Inv for xml path('')), 1, 1, '')
from tb
group by Inv
--2000
CREATE FUNCTION dbo.f_strUnite(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ';' + [order] FROM tb WHERE inv=@id
RETURN STUFF(@str, 1, 1, '')
END
GO
-- 调用函数
SELECt inv, [order] = dbo.f_strUnite(inv) FROM tb GROUP BY inv
drop table tb
drop function dbo.f_strUnite
go----------------结果----------------------------
/*inv order
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10001 P111;P112;P113
10002 P114;P115(2 行受影响)*/
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-11 11:17:24.810●●●●●
★★★★★soft_wsx★★★★★
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Inv] int,[order] varchar(4))
insert [tb]
select 10001,'P111' union all
select 10001,'P112' union all
select 10001,'P113' union all
select 10002,'P114' union all
select 10002,'P115'
if object_id('f_tb2') is not null drop function f_tb2
go
create function f_tb2(@inv nvarchar(100))
returns nvarchar(100)
as
begin
declare @sql nvarchar(4000)
set @sql=N''
select @sql=@sql+N','+cast([order] as varchar) from tb where [Inv]=@Inv
set @sql=stuff(@sql,1,1,N'')
return(@sql)
end
goselect inv,dbo.f_tb2(inv) as [order] from tb group by inv
/*
inv order
10001 P111,P112,P113
10002 P114,P115
*/这样不行吗?