有这么一个表存放着用户的基本信息[u_base_info]
u_id,u_name,u_pay,u_regtime
分别代表
id,注册名,汇款额,注册时间.
有另一个表存放每次给会员发放的分红,分红是月发[u_draws_bonus]
u_id,u_draws_bonus,u_time
分别代表
id,本月分红额,当次分红时间
两表通过u_id关联..
问下.用户想这样显示(N假设用户同意有最高限额比如可分10次)
用户名|汇款额|注册时间|第1次分红额|第2次分红额|....|第N次分红额

我很木纳.怎么办?有没有好的实现方式?
请教了.

简单的说 a表这样
张三 男 21岁
李四 女 20岁

b表
张三 100元 2003.3.3
张三 130元 2003.4.4
李四 38元 2003.3.3
李四 100元 2003.4.4
李四 200元 2003.5.5
现要这么实现

张三 男 21岁 100元 130元
李四 女 20岁 38元 100元 200元

晕那

你要查询的列数是不确定的,这没办法用select实现.
如果列数确定,比如有三次分红,那可以这样查询


select ua.u_name 用户名,ua.u_pay 汇款额,ua.u_regtime 注册时间,
(select ub.u_draws_bonus from ub where
(select count(1) from u_draws_bonus uc where ub.u_time<=uc.u_time and ub.u_id=uc.u_id)=3)
as 第一次分红额,
(select ub.u_draws_bonus from ub where
(select count(1) from u_draws_bonus uc where ub.u_time<=uc.u_time and ub.u_id=uc.u_id)=2)
as 第二次分红额,
(select ub.u_draws_bonus from ub where
(select count(1) from u_draws_bonus uc where ub.u_time<=uc.u_time and ub.u_id=uc.u_id)=1)
as 第三次分红额
from u_base_info ua,u_draws_bonus ub where ua.u_id=ub.u_id

[此贴子已经被作者于2007-4-7 10:37:56编辑过]

create table a
(
[name] nvarchar(10) primary key not null,
sex nvarchar(2),
age int
)
create table b
(
[name] nvarchar(10),
gongzi money,
data smalldatetime
)
insert into a select'张三','男',21
union select '李四','女',20
insert into b select'张三',100,'2003.3.3'
union select '张三',130,'2003.4.4'
union select '李四',38,'2003.3.3'
union select '李四',100,'2003.4.4'
union select '李四',200,'2003.5.5'

select a.[name],sex,
sum(case data when '2003.3.3' then gongzi else 0 end) as [三月份],
sum(case data when '2003.4.4' then gongzi else 0 end) as [四月份],
sum(case data when '2003.5.5' then gongzi else 0 end) as [五月份]
from a,b where a.[name]=b.[name] group by a.[name],sex

~~~~~~~~~从天空的左岸到右岸,歌声掉进了果实的睡眠~~~~~~~~~~~~~~

以上是针对你那两个表( a和b)来做的!
~~~~~~~~~从天空的左岸到右岸,歌声掉进了果实的睡眠~~~~~~~~~~~~~~

谢谢以上两位的解答,我理解下!

问题解决,非常感谢!有小红花的话我一定给你们两位带上.呵..
顺便问道下:
select a.[name],sex,
sum(case data when '2003.3.3' then gongzi else 0 end) as [三月份],
sum(case data when '2003.4.4' then gongzi else 0 end) as [四月份],
sum(case data when '2003.5.5' then gongzi else 0 end) as [五月份]
from a,b where a.[name]=b.[name] group by a.[name],sex
标记为红色的部份与 a inner join b on a.[name]=b.[name]
这两种写法有什么不同?

[此贴子已经被作者于2007-4-7 12:47:54编辑过]

我以前写过一个动态的,和你问题类似
if object_id('shiyan') is not null
drop table shiyan
go
create table shiyan(id varchar(3),kk varchar(3),t datetime,tt varchar(10))
insert into shiyan select '002','yy','2007-01-02','07:30:00'
insert into shiyan select '002','yy','2007-01-02','11:20:00'
insert into shiyan select '002','yy','2007-01-02','08:30:00'
insert into shiyan select '002','yy','2007-01-02','12:20:00'
insert into shiyan select '001','yy','2007-01-02','07:30:00'
insert into shiyan select '001','yy','2007-01-02','11:20:00'
insert into shiyan select '001','yy','2007-01-02','08:30:00'
insert into shiyan select '001','yy','2007-01-02','12:20:00'
select id,kk,convert(varchar(10),t,120) as t,convert(varchar(8),tt,108) as tt from shiyan
结果
id kk t tt
---- ---- ---------- --------
002 yy 2007-01-02 07:30:00
002 yy 2007-01-02 11:20:00
002 yy 2007-01-02 08:30:00
002 yy 2007-01-02 12:20:00
001 yy 2007-01-02 07:30:00
001 yy 2007-01-02 11:20:00
001 yy 2007-01-02 08:30:00
001 yy 2007-01-02 12:20:00

（所影响的行数为 8 行）
if object_id('sy') is not null
drop table sy
go
select *into sy from (
select bj=(select count(1) from shiyan where s.id=id and s.kk=kk and s.t=t and tt<=s.tt ),id,kk,convert(varchar(10),t,120) as t,convert(varchar(8),tt,108) as tt from shiyan s) m
select * from sy
结果
bj id kk t tt
----------- ---- ---- ---------- --------
1 002 yy 2007-01-02 07:30:00
3 002 yy 2007-01-02 11:20:00
2 002 yy 2007-01-02 08:30:00
4 002 yy 2007-01-02 12:20:00
1 001 yy 2007-01-02 07:30:00
3 001 yy 2007-01-02 11:20:00
2 001 yy 2007-01-02 08:30:00
4 001 yy 2007-01-02 12:20:00

（所影响的行数为 8 行）

alter table sy alter column bj varchar(20)
update sy set bj='time'+bj
select * from sy order by bj asc
结果
bj id kk t tt
-------------------- ---- ---- ---------- --------
time1 002 yy 2007-01-02 07:30:00
time1 001 yy 2007-01-02 07:30:00
time2 001 yy 2007-01-02 08:30:00
time2 002 yy 2007-01-02 08:30:00
time3 002 yy 2007-01-02 11:20:00
time3 001 yy 2007-01-02 11:20:00
time4 001 yy 2007-01-02 12:20:00
time4 002 yy 2007-01-02 12:20:00

（所影响的行数为 8 行）

declare @sql varchar(4000)
set @sql=' '
select @sql=@sql+','+quotename(bj,N'''')+'=max(case bj when '+quotename(bj,N'''')+' then tt else ''00:00'' end)' from sy group by bj
select @sql='select id,kk,t'+@sql+' from sy group by id,kk,t'
exec(@sql)

结果
id kk t time1 time2 time3 time4
---- ---- ---------- -------- -------- -------- --------
001 yy 2007-01-02 07:30:00 08:30:00 11:20:00 12:20:00
002 yy 2007-01-02 07:30:00 08:30:00 11:20:00 12:20:00

去查看行转列的精华帖子第二个
建一个函数就可以实现

....
棉老大写这么一大堆呀....汗...

行转换列?嗯..学习下.

declare @sql varchar(4000)
set @sql=' '
select @sql=@sql+','+quotename(bj,N'''')+'=max(case bj when '+quotename(bj,N'''')+' then tt else ''00:00'' end)' from sy group by bj
select @sql='select id,kk,t'+@sql+' from sy group by id,kk,t'
exec(@sql)
行列转换不是在这里吗

不用行列转换也可以的
先找到最多的层数 再拼sql语句的列数