注册 登录
编程论坛 → SQL Server论坛

这样的SQL要怎么写?

sm105096496 发布于 2007-09-25 17:01， 768 次点击

大哥大姐帮帮我 。。。。
我现在一个表分别是 Users , Integral表
Users表：
Userid ChrName PWD
1 xxx 123456
2 zzz 123456

Integral表:
ID Userid inYear intMonth intGoal intcurrent
1 1 2000 1 2 0
2 1 2000 2 0 0
3 1 2000 3 1 0
. . . . . .
12 1 2000 12 1 0
13 2 2000 1 1 0
14 2 2000 2 2 0
15 2 2000 3 1 0
. . . . . .
24 2 2000 12 3 0

我想得到这样的数据

id ChrName intGoal(按照intMonth 的（1）) intGoal(按照intMonth 的（2）)....intGoal(按照intMonth 的（12）)
1 xxx 2 0 1
2 zzz 1 1 3

2 回复
#2
风之剑雨飘香2007-10-07 10:22
回复：（sm105096496）这样的SQL要怎么写?
基本就这样，根据需要再修改吧
selecct a.userid,a.chrname,m1.month01,m2.month02,m3.month03,m4.month04,m5.month05, m6.month06,m7.month07,m8.month08,m9.month09,m10.month10,m11.month11,m12.month12
from (select Userid,chrname from Users) a,
(select id,intGoal month01 from Integral where inyear=2002 and intmonth=1) m1,
(select id,intGoal month02 from Integral where inyear=2002 and intmonth=2) m2,
(select id,intGoal month03 from Integral where inyear=2002 and intmonth=3) m3,
(select id,intGoal month04 from Integral where inyear=2002 and intmonth=4) m4,
(select id,intGoal month05 from Integral where inyear=2002 and intmonth=5) m5,
(select id,intGoal month06 from Integral where inyear=2002 and intmonth=6) m6,
(select id,intGoal month07 from Integral where inyear=2002 and intmonth=7) m7,
(select id,intGoal month08 from Integral where inyear=2002 and intmonth=8) m8,
(select id,intGoal month09 from Integral where inyear=2002 and intmonth=9) m9,
(select id,intGoal month10 from Integral where inyear=2002 and intmonth=10) m10,
(select id,intGoal month11 from Integral where inyear=2002 and intmonth=11) m11,
(select id,intGoal month12 from Integral where inyear=2002 and intmonth=12) m12
where a.userid=m1.id and a.userid=m2.id and a.userid=m3.id and a.userid=m4.id and a.userid=m5.id and
a.userid=m6.id and a.userid=m7.id and a.userid=m8.id and a.userid=m9.id and a.userid=m10.id and
a.userid=m11.id and a.userid=m12.id
#3
shuzai19852007-10-07 12:08

select id,ChrName ,case intmonth when 1 then intgoal else ''end as '1月',
case intmonth when 2 then intgoal else ''end as '2月',
case intmonth when 3 then intgoal else ''end as '3月',
case intmonth when 4 then intgoal else ''end as '4月',
case intmonth when 5 then intgoal else ''end as '5月',
case intmonth when 6 then intgoal else ''end as '6月',
case intmonth when 7 then intgoal else ''end as '7月',
case intmonth when 8 then intgoal else ''end as '8月',
case intmonth when 9 then intgoal else ''end as '9月',
case intmonth when 10 then intgoal else ''end as '10月',
case intmonth when 11 then intgoal else ''end as '11月',
case intmonth when 12 then intgoal else ''end as '12月',
from Users , Integral where Users.id = Integral.id　　
　order by id


不知道行不行　　ＬＺ自己试试
1
©2025, BC-CN.NET
电脑版