第一个问题:
T1
姓名 状态
王 启用
张 未启用
李 启用
T2
项目名称 项目金额 项目状态
茶 10 未启用
酒 10 启用
水 5 未启用
面包 10 启用要求得出结果
T3
姓名 项目名称 项目金额
王 酒 10
王 面包 10
李 酒 10
李 面包 10
。
第2个问题
T1表
主编号 副编号
101 101
101 102
101 103
101 104
101 105
。结果
主编号 备注 数量
101 102,103,104,105 5
T1
姓名 状态
王 启用
张 未启用
李 启用
T2
项目名称 项目金额 项目状态
茶 10 未启用
酒 10 启用
水 5 未启用
面包 10 启用要求得出结果
T3
姓名 项目名称 项目金额
王 酒 10
王 面包 10
李 酒 10
李 面包 10
。
第2个问题
T1表
主编号 副编号
101 101
101 102
101 103
101 104
101 105
。结果
主编号 备注 数量
101 102,103,104,105 5
from T1,T2
where T1.状态 =T2.状态
and T1.状态 ='启用'
主编号 会出现 102 等副编号吗?
如有,则是展BOM 如没有,直接合并字符串,sql2000需要function
sql2005可以直接xml
2题参考下面的:
SQL code问题描述:
无论是在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-- 2. 新的解决方法
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'-- 查询处理
SELECT *
FROM(
SELECT DISTINCT
id
FROM @t
)A
OUTER APPLY(
SELECT
[values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM @t N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N/*--结果
id values
----------- ----------------
1 aa,bb
2 aaa,bbb,ccc
(2 行受影响)
--*/
declare @t1 table(姓名 nvarchar(20),状态 nvarchar(20))
insert @t1
select '王', '启用' union all
select '张', '未启用' union all
select '李', '启用'declare @t2 table(项目名称 nvarchar(20),项目金额 int,项目状态 nvarchar(20))
insert @t2
select '茶', 10, '未启用' union all
select '酒', 10, '启用' union all
select '水', 5, '未启用' union all
select '面包', 10, '启用'SELECT a.姓名 ,b.项目名称,b.项目金额
FROM @t1 a JOIN @t2 b ON a.状态 =b.项目状态
WHERE a.状态 ='启用' 姓名 项目名称 项目金额
-------------------- -------------------- -----------
王 酒 10
李 酒 10
王 面包 10
李 面包 10(4 行受影响)
create table t1(id int,vid int)
insert t1
select 101,101 union all
select 101,102 union all
select 101,103 union all
select 101,104 union all
select 101,105 create function dbo.f_str(@id int)
returns varchar(100)
as
begin
declare @r varchar(100)
select @r = isnull(@r,'') + ',' + ltrim(vid)
from t1
where id=@id
return stuff(@r, 1, 1, '')
end-------------------------------------------------------
select 主编号=id, 备注=dbo.f_str(id),数量=count(1)
FROM t1
GROUP BY id主编号 备注 数量
----------- ----------------------- -----------
101 101,102,103,104,105 5(1 行受影响)
from T1,T2
where T1.状态 =T2.状态
and T1.状态 ='启用'
if object_id('T1') is not null
drop table T1
go
create table T1(姓名 nvarchar(20),状态 nvarchar(20))
insert T1
select '王', '启用' union all
select '张', '未启用' union all
select '李', '启用'
if object_id('T2') is not null
drop table T2
go
create table T2(项目名称 nvarchar(20),项目金额 int,项目状态 nvarchar(20))
insert T2
select '茶', 10, '未启用' union all
select '酒', 10, '启用' union all
select '水', 5, '未启用' union all
select '面包', 10, '启用'select a.姓名,b.项目名称,b.项目金额 from T1 as a left join T2 as b on a.状态=b.项目状态 where b.项目状态= '启用' order by 姓名 descdrop table T1,T2
use Practice
if object_id('T1') is not null
drop table T1
gocreate table t1(id int,vid int)
insert t1
select 101,101 union all
select 101,102 union all
select 101,103 union all
select 101,104 union all
select 101,105 SELECT *FROM( SELECT DISTINCT id FROM t1) A
OUTER APPLY( SELECT vid= STUFF(REPLACE(REPLACE(( SELECT vid FROM t1 N WHERE id = A.id FOR XML AUTO),
'<N vid="', ','), '"/>', ''), 1, 1, ''))N
/*
id vid
----------- --------------
101 101,102,103,104,105(1 行受影响)
*/
insert @T1 select '王','启用'
union all select '张','未启用 '
union all select '李','启用'declare @T2 table ([项目名称 ] varchar(10),[项目金额 ] int,[项目状态]varchar(10))
insert @T2 select '茶 ',10,'未启用'
union all select '酒',10,'启用'
union all select '水 ',5,'未启用'
union all select '面包',10,'启用'select T1.[姓名],T1.[状态],T2.[项目名称],T2.[项目金额] from (select * from @T1 where [状态]='启用') T1 cross join (select * from @T2 T2 where T2.[项目状态]='启用') T2
order by T1.[姓名] desc
/*
姓名 状态 项目名称 项目金额
王 启用 酒 10
王 启用 面包 10
李 启用 面包 10
李 启用 酒 10
*/
insert @t1
select 101,101 union all
select 101,102 union all
select 101,103 union all
select 101,104 union all
select 101,105 select id,stuff((select ','+cast(vid as varchar(10)) from @t1 where id=a.id and vid<>id for xml path('')),1,1,'') 备注,count(vid) 数量
from @t1 a
group by id/*id 备注 数量
----------- ---------------------------------
101 102,103,104,105 5 */
create table t1(id int,vid int)
insert t1
select 101,101 union all
select 101,102 union all
select 101,103 union all
select 101,104 union all
select 101,105 create function dbo.f_str(@id int)
returns varchar(100)
as
begin
declare @r varchar(100)
select @r = isnull(@r,'') + ',' + ltrim(vid)
from t1
where id=@id
return stuff(@r, 1, 1, '')
end-------------------------------------------------------
select 主编号=id, 备注=dbo.f_str(id),数量=count(1)
FROM t1
GROUP BY id主编号 备注 数量
----------- ----------------------- -----------
101 101,102,103,104,105 5(1 行受影响)
insert t1
select '王', '启用' union all
select '张', '未启用' union all
select '李', '启用'create table T2(项目名称 nvarchar(20),项目金额 int,项目状态 nvarchar(20))
insert t2
select '茶', 10, '未启用' union all
select '酒', 10, '启用' union all
select '水', 5, '未启用' union all
select '面包', 10, '启用'select 姓名,项目名称,项目金额
from t1,t2
where t1.状态=t2.项目状态 AND T1.状态='启用' order by 姓名 desc
/*---------------------------
李 酒 10
李 面包 10
王 酒 10
王 面包 10
------------------------------*/
create table t1_clumns(id int,vid int)
insert t1_clumns
select 101,101 union all
select 101,102 union all
select 101,103 union all
select 101,104 union all
select 101,105
create function t1_clumns_fun(@id int)
returns varchar(500)
as
begin
declare @return varchar(500)
set @return = ''
select @return =@return+','+cast(vid as varchar) from t1_clumns where id = @id
set @return =stuff(@return,1,1,'')
return @return
end
select id ,dbo.t1_clumns_fun(id),count(1) from t1_clumns group by id
=================
主编号 备注 数量
101 102,103,104,105 5create table t1_clumns_01(姓名 nvarchar(20),状态 nvarchar(20))
insert t1_clumns_01
select '王', '启用' union all
select '张', '未启用' union all
select '李', '启用'create table t1_clumns_02(项目名称 nvarchar(20),项目金额 int,项目状态 nvarchar(20))
insert t1_clumns_02
select '茶', 10, '未启用' union all
select '酒', 10, '启用' union all
select '水', 5, '未启用' union all
select '面包', 10, '启用'select *from t1_clumns_01 , t1_clumns_02
where t1_clumns_01.状态 = '启用' and t1_clumns_02.项目状态 = '启用' order by t1_clumns_01.姓名 desc
======================
姓名 项目名称 项目金额
王 酒 10
王 面包 10
李 酒 10
李 面包 10
create table t1(主编号 int,副编号 int)
insert t1
select 101,101 union all
select 101,102 union all
select 101,103 union all
select 101,104 union all
select 101,105gocreate function beizhu(@id int)
returns varchar(100)
as
begin
declare @s varchar(100)
set @s=''
select @s=@s+','+ cast(副编号 as varchar(50))
from t1
where 主编号=@id
return stuff(@s,1,1,'')
end
go
select 主编号,
备注=dbo.beizhu(主编号),
count(*) as 数量
from t1
group by 主编号