导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

求条SQL语句,诸位帮忙。

sdlwhgw 2007-11-28 10:57:02
举个例子:
表结构为

CD 列1 列2 列3 列4
1 1 1 1 1
2 1 1 0 1
3 1 0 1 1
4 1 0 0 1
5 0 1 1 1
6 0 1 0 1
7 0 0 1 1
8 0 0 0 1
9 1 1 1 0
10 1 1 0 0
11 1 0 1 0
12 1 0 0 0
13 0 1 1 0
14 0 1 0 0
15 0 0 1 0
16 0 0 0 0

查询结果
列1 列2
1 A,B,C,D
2 A,B,D
.......
14 B
15 C
16 null

也就是,如果字段的值是1 就显示出对应的名字(A),是0 则不显示了,用逗号来分割
...全文
48 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
sdlwhgw 2007-12-04
帖子加分 怎么会没有权限呢?
回复
sdlwhgw 2007-11-28
好像最后一条没有显示
回复
sdlwhgw 2007-11-28
不到5天没法加分?
回复
sdlwhgw 2007-11-28
谢谢,
回复
dawugui 2007-11-28
create table tb(CD int, 列1 int , 列2 int , 列3 int , 列4 int )
insert into tb values(1 ,1, 1, 1, 1)
insert into tb values(2 ,1, 1, 0, 1)
insert into tb values(3 ,1, 0, 1, 1)
insert into tb values(4 ,1, 0, 0, 1)
insert into tb values(5 ,0, 1, 1, 1)
insert into tb values(6 ,0, 1, 0, 1)
insert into tb values(7 ,0, 0, 1, 1)
insert into tb values(8 ,0, 0, 0, 1)
insert into tb values(9 ,1, 1, 1, 0)
insert into tb values(10 ,1, 1, 0, 0)
insert into tb values(11 ,1, 0, 1, 0)
insert into tb values(12 ,1, 0, 0, 0)
insert into tb values(13 ,0, 1, 1, 0)
insert into tb values(14 ,0, 1, 0, 0)
insert into tb values(15 ,0, 0, 1, 0)
insert into tb values(16 ,0, 0, 0, 0)
go
select 列1,列2 = left(列2,len(列2) - 1) from
(
select 列1 = cd , 列2 =
(case when 列1 = 1 then 'A,' else '' end) +
(case when 列2 = 1 then 'B,' else '' end) +
(case when 列3 = 1 then 'C,' else '' end) +
(case when 列4 = 1 then 'D,' else '' end)
from tb
) t
where len(列2) > 0
drop table tb

/*
列1 列2
----------- --------
1 A,B,C,D
2 A,B,D
3 A,C,D
4 A,D
5 B,C,D
6 B,D
7 C,D
8 D
9 A,B,C
10 A,B
11 A,C
12 A
13 B,C
14 B
15 C

(所影响的行数为 15 行)
*/
回复
playwarcraft 2007-11-28

create table T(CD int identity(1,1),列1 int,列2 int,列3 int,列4 int)
insert into T select 1,1,1,1
insert into T select 1,1,0,1
insert into T select 1,0,1,1
insert into T select 1,0,0,1
insert into T select 0,1,1,1
insert into T select 0,1,0,1
insert into T select 0,0,1,0
insert into T select 0,0,0,1
insert into T select 0,0,0,0
insert into T select 0,1,1,0

select 列1, case when 列2='' then null
else case when right(列2,1)=','
then left(列2,len(列2)-1)
else 列2 end
end as 列2
from
(
select CD as 列1,
case when 列1=1 then 'A,' else '' end
+case when 列2=1 then 'B,' else '' end
+case when 列3=1 then 'C,' else '' end
+case when 列4=1 then 'D' else '' end as 列2
from T
) a

/*
列1 列2
----------- -------
1 A,B,C,D
2 A,B,D
3 A,C,D
4 A,D
5 B,C,D
6 B,D
7 C
8 D
9 NULL
10 B,C
*/

drop table T

回复
dawugui 2007-11-28
create table tb(CD int, 列1 int , 列2 int , 列3 int , 列4 int )
insert into tb values(1 ,1, 1, 1, 1)
insert into tb values(2 ,1, 1, 0, 1)
insert into tb values(3 ,1, 0, 1, 1)
insert into tb values(4 ,1, 0, 0, 1)
insert into tb values(5 ,0, 1, 1, 1)
insert into tb values(6 ,0, 1, 0, 1)
insert into tb values(7 ,0, 0, 1, 1)
insert into tb values(8 ,0, 0, 0, 1)
insert into tb values(9 ,1, 1, 1, 0)
insert into tb values(10 ,1, 1, 0, 0)
insert into tb values(11 ,1, 0, 1, 0)
insert into tb values(12 ,1, 0, 0, 0)
insert into tb values(13 ,0, 1, 1, 0)
insert into tb values(14 ,0, 1, 0, 0)
insert into tb values(15 ,0, 0, 1, 0)
insert into tb values(16 ,0, 0, 0, 0)
go
select 列1 = cd , 列2 =
(case when 列1 = 1 then 'A' else '' end) +
(case when 列2 = 1 then 'B' else '' end) +
(case when 列3 = 1 then 'C' else '' end) +
(case when 列4 = 1 then 'D' else '' end)
from tb

drop table tb

/*
列1 列2
----------- ----
1 ABCD
2 ABD
3 ACD
4 AD
5 BCD
6 BD
7 CD
8 D
9 ABC
10 AB
11 AC
12 A
13 BC
14 B
15 C
16

(所影响的行数为 16 行)
*/
回复
dawugui 2007-11-28
select 列1 = cd , 列2 =  
(case when 列1 = 1 then 'A' else '' end) +
(case when 列2 = 1 then 'B' else '' end) +
(case when 列3 = 1 then 'C' else '' end) +
(case when 列4 = 1 then 'D' else '' end)
from tb
回复
vanjayhsu 2007-11-28
没搞明白LZ是什么意思,请LZ再详细下。
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告