CREATE TABLE tb2( id varchar(10),name varchar(10),name2 varchar(10),isOk bit)
INSERT tb2
SELECT '3','a0','b0',0
UNION ALL SELECT '1','a1','b1',1
UNION ALL SELECT '1','a2','b2',1
UNION ALL SELECT '2','a3','b3',0
UNION ALL SELECT '2','a4','b4',1/*-----------------
select * from tb2
也就是说
显示这一如果 isok 是1的话 那么names=name+name2的值
如果不是1那么就names就是空值
case when isok=1 then name+name2然后从上面的结果
再按照id分组,按照id分组,取max(isok)[也就是只要是1就取1,如果都为0则才是0]
,同时取names的合并值,(也就是字符串合并)
同时问题也出现了,结果如下
网上看了下说用函数进行处理,本人水平不行,写不好,也怕有效率问题
同时说明一下,这个tb2可能是个临时表或者就是一个select出来的结果集(select * from tb2)
所以感觉如果用函数也会挺麻烦的。
求高手帮忙解决。最终结果
----------------
id isOk names
1 1 a1,b1,a2,b2
2 1 a4,b4,
3 0 */-------end-------/
INSERT tb2
SELECT '3','a0','b0',0
UNION ALL SELECT '1','a1','b1',1
UNION ALL SELECT '1','a2','b2',1
UNION ALL SELECT '2','a3','b3',0
UNION ALL SELECT '2','a4','b4',1/*-----------------
select * from tb2
也就是说
显示这一如果 isok 是1的话 那么names=name+name2的值
如果不是1那么就names就是空值
case when isok=1 then name+name2然后从上面的结果
再按照id分组,按照id分组,取max(isok)[也就是只要是1就取1,如果都为0则才是0]
,同时取names的合并值,(也就是字符串合并)
同时问题也出现了,结果如下
网上看了下说用函数进行处理,本人水平不行,写不好,也怕有效率问题
同时说明一下,这个tb2可能是个临时表或者就是一个select出来的结果集(select * from tb2)
所以感觉如果用函数也会挺麻烦的。
求高手帮忙解决。最终结果
----------------
id isOk names
1 1 a1,b1,a2,b2
2 1 a4,b4,
3 0 */-------end-------/
解决方案 »
- 帮忙看看下面的SQL语句什么意思.
- 怎样用一条语句按日期查询多个表中相同字段的记录之和
- 等待用于页 (1:153858),数据库 ID 5 的缓冲区闩锁类型 2 时发生超时。
- 急,去NULL值问题!
- ~UPDATE语句实现不了更新.请大家帮忙.(或 求参考"在ASP+SQLsever同一页面中同时可以操作"更新","删除","显示")
- try---catch 中的error_procdure() 问题
- 怎么知道update一共修改了多少条记录?
- 颜色显示
- 请高手指救:我想实现把A表的几个字段的内容,插入到B表
- 怎么对比两个数据库的表数据?
- SQLserver通过链接服务器访问db2
- 请问:考虑到程序效能问题,下面哪种做法比较好?
INSERT tb2
SELECT '3','a0','b0',0
UNION ALL SELECT '1','a1','b1',1
UNION ALL SELECT '1','a2','b2',1
UNION ALL SELECT '2','a3','b3',0
UNION ALL SELECT '2','a4','b4',1select
id,
max(cast(isok as int)) as isOK,
names=stuff((select ','+name+','+name2 from tb2 where id=t.id and isok=1 for xml path('')),1,1,'')
from
tb2 t
group by id/**
id isOK names
---------- ----------- ------------------------------
1 1 a1,b1,a2,b2
2 1 a4,b4
3 0 NULL(所影响的行数为 3 行)
**/
CREATE TABLE tb2( id varchar(10),name varchar(10),name2 varchar(10),isOk bit)
INSERT tb2
SELECT '3','a0','b0',0
UNION ALL SELECT '1','a1','b1',1
UNION ALL SELECT '1','a2','b2',1
UNION ALL SELECT '2','a3','b3',0
UNION ALL SELECT '2','a4','b4',1select id,
value = stuff((select ','+name+','+name2 from tb2 where id = a.id and isok = 1 for xml path('')),1,1,'')
from tb2 a
group by id
name=stuff((select ','+name+','+name2 from tb2 where id=a.id for xml path('')),1,1,'')
from tb2 a
group by id
/*
id isok name
---------- ----------- ---------------
1 1 a1,b1,a2,b2
2 1 a3,b3,a4,b4
3 0 a0,b0
*/
-- Author: T.O.P
-- Create date: 2009/11/25
-- Version: SQL SERVER 2005
-- =============================================CREATE TABLE tb2( id varchar(10),name varchar(10),name2 varchar(10),isOk bit)
INSERT tb2
SELECT '3','a0','b0',0
UNION ALL SELECT '1','a1','b1',1
UNION ALL SELECT '1','a2','b2',1
UNION ALL SELECT '2','a3','b3',0
UNION ALL SELECT '2','a4','b4',1;with cte as
(
select id, name+','+name2 as value
from tb2 a
where not exists(select 1 from tb2 where a.id=id and a.isok<isok)
)select id, [values]=stuff((select ','+[value] from cte t where id=tb.id for xml path('')), 1, 1, '')
from cte tb
group by id DROP TABLE tb2--测试结果:
/*
id values
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 a1,b1,a2,b2
2 a4,b4
3 a0,b0(3 row(s) affected)
*/
这样应该对了~~-- =============================================
-- Author: T.O.P
-- Create date: 2009/11/25
-- Version: SQL SERVER 2005
-- =============================================CREATE TABLE tb2( id varchar(10),name varchar(10),name2 varchar(10),isOk bit)
INSERT tb2
SELECT '3','a0','b0',0
UNION ALL SELECT '1','a1','b1',1
UNION ALL SELECT '1','a2','b2',1
UNION ALL SELECT '2','a3','b3',0
UNION ALL SELECT '2','a4','b4',1;with cte as
(
select id, (case when isok=1 then name+','+name2 end) as value
from tb2 a
where not exists(select 1 from tb2 where a.id=id and a.isok<isok)
)select id, [values]=stuff((select ','+[value] from cte t where id=tb.id for xml path('')), 1, 1, '')
from cte tb
group by id DROP TABLE tb2--测试结果:
/*
id values
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 a1,b1,a2,b2
2 a4,b4
3 NULL
*/
name=case when max(cast(isok as int))=0 then '' else
stuff((select ','+name+','+name2 from tb2 where id=a.id for xml path('')),1,1,'')
end
from tb2 a
group by id
/*
id isok name
---------- ----------- ---------------
1 1 a1,b1,a2,b2
2 1 a3,b3,a4,b4
3 0
*/
唉, 失败, 少了一列
-- =============================================
-- Author: T.O.P
-- Create date: 2009/11/25
-- Version: SQL SERVER 2005
-- =============================================CREATE TABLE tb2( id varchar(10),name varchar(10),name2 varchar(10),isOk bit)
INSERT tb2
SELECT '3','a0','b0',0
UNION ALL SELECT '1','a1','b1',1
UNION ALL SELECT '1','a2','b2',1
UNION ALL SELECT '2','a3','b3',0
UNION ALL SELECT '2','a4','b4',1;with cte as
(
select id, name+','+name2 as value, isOk
from tb2 a
where not exists(select 1 from tb2 where a.id=id and a.isok<isok)
)select id, max(cast(isok as int)) as isok,
[values]=stuff((select ','+[value] from cte t where id=tb.id and isok=1 for xml path('')), 1, 1, '')
from cte tb
group by id DROP TABLE tb2--测试结果:
/*
id isok values
---------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 a1,b1,a2,b2
2 1 a4,b4
3 0 NULL(3 row(s) affected)
*/
select id,isok=max(cast(isok as int)),
names=isnull(stuff((select ','+name+','+name2 from tb2 where id=a.id and isok='true' for xml path('')),1,1,''),'')
from tb2 a
group by id
/*
id isok names
---------- ----------- -------------------
1 1 a1,b1,a2,b2
2 1 a4,b4
3 0
*/
INSERT tb2
SELECT '3','a0','b0',0
UNION ALL SELECT '1','a1','b1',1
UNION ALL SELECT '1','a2','b2',1
UNION ALL SELECT '2','a3','b3',0
UNION ALL SELECT '2','a4','b4',1
drop function dbo.FC_str
create function dbo.FC_str(@id varchar(10))
returns varchar(4000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+name+','+name2 from tb2 where id=@id and isOk=1
return stuff(@sql,1,1,'')
endselect id,max(cast(isOk as int)) isOk,names from
(
select distinct dbo.FC_str(id) names,isOk,id from tb2
)TT group by id,names order by id ascid isOk names
---------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 a1,b1,a2,b2
2 1 a4,b4
3 0 NULL
INSERT tb2
SELECT '3','a0','b0',0
UNION ALL SELECT '1','a1','b1',1
UNION ALL SELECT '1','a2','b2',1
UNION ALL SELECT '2','a3','b3',0
UNION ALL SELECT '2','a4','b4',1
drop function dbo.FC_str
create function dbo.FC_str(@id varchar(10))
returns varchar(4000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+name+','+name2 from tb2 where id=@id and isOk=1
return stuff(@sql,1,1,'')
endselect id,max(cast(isOk as int)) isOk,names from
(
select distinct dbo.FC_str(id) names,isOk,id from tb2
)TT group by id,names order by id ascid isOk names
---------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 a1,b1,a2,b2
2 1 a4,b4
3 0 NULL
把select出来的结果插入一个临时表,再查询select ......
into #temp
from xxxxx