34,593
社区成员
发帖
与我相关
我的任务
分享
declare @t table(a varchar(30), b varchar(30), c varchar(30), d varchar(30))
insert into @t select '1' , '', '', ''
insert into @t select '' , '2', '', ''
insert into @t select '1' , '', '3', '4'
insert into @t select '' , '', '', '4'
insert into @t select '' , '', '2', ''
insert into @t select 'x' , '', '2', ''
insert into @t select '' , 'y', '2', ''
insert into @t select '' , 'y', '2', 'z'
--显示的原始数据
select * from @t
--这些是我过滤出来的数据,只是里边有很多的空格
select a,b,c,d from @t
where 1=1
and (a='1' or a='')
and (b='2' or b='')
and (c='3' or c='')
and (d='4' or d='')
--想得到这个效果,即打扁成一行
select a='1', b='2', c='3', d='4'
最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?
declare @t table(frm varchar(30), grd varchar(30), col varchar(30), text varchar(30), width varchar(30))
insert into @t select 'f1', '', 'c1', 's11', '100'
insert into @t select '', 'g1', 'c1', 's12', '200'
insert into @t select '', '', 'c2', 's21', '300'
insert into @t select '', 'g1', 'c2', 's22', '400'
insert into @t select 'f1', 'g2', 'c1', 's13', '500'
insert into @t select '', '', 'c1', 's14', '600'
insert into @t select 'f1', 'g1', 'c1', 's15', '700'
insert into @t select '', '', 'c5', 's51', '800'
insert into @t select '', '', 'c3', 's31', '900'
insert into @t select 'f1', 'g3', '', 's16', '000'
--显示的原始数据
select * from @t
--
declare @t2 table(id int identity(1,1), frm varchar(30), grd varchar(30), col varchar(30), text varchar(30), width varchar(30))
insert into @t2(frm, grd, col, text, width)
select
max(frm) as frm,
max(grd) as grd,
col,
text, width
from @t
where 1=1
and (frm='f1' or frm='')
and (grd='g1' or grd='') --1111
-- --and (grd='g1' or grd='') --2222
group by col, text, width
order by col, grd, frm
select * from @t2 where id in(
select max(id) from @t2 group by col --1111
--select max(id) from @t2 group by col,grd --2222
)
declare @t table(frm varchar(30), grd varchar(30), col varchar(30), text varchar(30), width varchar(30))
insert into @t select 'f1', '', 'c1', 's1', '100'
insert into @t select '', 'g1', 'c1', 's2', '200'
insert into @t select '', '', 'c2', 's2', '100'
insert into @t select '', 'g1', 'c2', 's2', '100'
insert into @t select 'f1', 'g2', 'c1', 's1', '200'
insert into @t select '', '', 'c1', 's3', '100'
insert into @t select 'f1', 'g1', 'c1', 's1', '200'
insert into @t select '', '', 'c5', 's3', '100'
insert into @t select '', '', 'c3', 's2', '200'
insert into @t select 'f1', 'g3', '', 's1', '100'
--显示的原始数据
select * from @t
--这些是我过滤出来的数据,只是里边有很多的空格
select
max(frm) as frm,
max(grd) as grd,
col,
text, width
from @t
where 1=1
and (frm='f1' or frm='')
and (grd='g1' or grd='')
group by col, text, width
order by col, grd, frm
declare @t table(a varchar(30), b varchar(30), c varchar(30), d varchar(30))
insert into @t select '1' , '', '', ''
insert into @t select '' , '2', '', ''
insert into @t select '1' , '', '3', '4'
insert into @t select '' , '', '', '4'
insert into @t select '' , '', '2', ''
insert into @t select 'x' , '', '2', ''
insert into @t select '' , 'y', '2', ''
insert into @t select '' , 'y', '2', 'z'
--显示的原始数据
--select * from @t
--这些是我过滤出来的数据,只是里边有很多的空格
select a=max(a),b=max(b),c=max(c),d=max(d) from @t
where 1=1
and (a='1' or a='')
and (b='2' or b='')
and (c='3' or c='')
and (d='4' or d='')
a b c d
------------------------------ ------------------------------ ------------------------------ ------------------------------
1 2 3 4
(1 行受影响)
declare @t table(a varchar(30), b varchar(30), c varchar(30), d varchar(30))
insert into @t select '1' , '', '', ''
insert into @t select '' , '2', '', ''
insert into @t select '1' , '', '3', '4'
insert into @t select '' , '', '', '4'
insert into @t select '' , '', '2', ''
insert into @t select 'x' , '', '2', ''
insert into @t select '' , 'y', '2', ''
insert into @t select '' , 'y', '2', 'z'
--显示的原始数据
select * from @t
--这些是我过滤出来的数据,只是里边有很多的空格
select a,b,c,d from @t
where 1=1
and (a='1' or a='')
and (b='2' or b='')
and (c='3' or c='')
and (d='4' or d='')
--想得到这个效果,即打扁成一行
select a='1', b='2', c='3', d='4'
select max(a),max(b),max(c),max(d) from (
select a,b,c,d from @t
where 1=1
and (a='1' or a='')
and (b='2' or b='')
and (c='3' or c='')
and (d='4' or d='')
)t
declare @t table(a varchar(30), b varchar(30), c varchar(30), d varchar(30))
insert into @t select '1' , '', '', ''
insert into @t select '' , '2', '', ''
insert into @t select '1' , '', '3', '4'
insert into @t select '' , '', '', '4'
insert into @t select '' , '', '2', ''
insert into @t select 'x' , '', '2', ''
insert into @t select '' , 'y', '2', ''
insert into @t select '' , 'y', '2', 'z'
--显示的原始数据
select * from @t
--这些是我过滤出来的数据,只是里边有很多的空格
select max(a) as a,max(b) as b,max(c) as c,max(d) as d from @t
where 1=1
and (a='1' or a='')
and (b='2' or b='')
and (c='3' or c='')
and (d='4' or d='')