问一个SQL语句~~~~~~

shaoml 2008-04-26 04:02:03 
<HTML>

<HEAD>

<TITLE>PL/SQL Developer Export</TITLE>

</HEAD>

<BODY>

<TABLE BORDER="1">

<TR><TH>   </TH><TH>DIVISION_ID</TH><TH>NATION_ID</TH><TH>ZDRS</TH><TH>WEEKNUM</TH></TR>

<TR><TD>1</TD><TD>海淀</TD><TD>3</TD><TD>34</TD><TD>6</TD></TR>

<TR><TD>2</TD><TD>海淀</TD><TD>2</TD><TD>33</TD><TD>6</TD></TR>

<TR><TD>3</TD><TD>海淀</TD><TD>1</TD><TD>22</TD><TD>6</TD></TR>

<TR><TD>4</TD><TD>昌平</TD><TD>3</TD><TD>44</TD><TD>6</TD></TR>

<TR><TD>5</TD><TD>昌平</TD><TD>2</TD><TD>32</TD><TD>6</TD></TR>

<TR><TD>6</TD><TD>昌平</TD><TD>1</TD><TD>54</TD><TD>6</TD></TR>

<TR><TD>7</TD><TD>西城</TD><TD>1</TD><TD>42</TD><TD>6</TD></TR>

<TR><TD>8</TD><TD>西城</TD><TD>2</TD><TD>44</TD><TD>6</TD></TR>

<TR><TD>9</TD><TD>海淀</TD><TD>1</TD><TD>45</TD><TD>5</TD></TR>

<TR><TD>10</TD><TD>海淀</TD><TD>2</TD><TD>55</TD><TD>5</TD></TR>

<TR><TD>11</TD><TD>海淀</TD><TD>3</TD><TD>176</TD><TD>5</TD></TR>

<TR><TD>12</TD><TD>昌平</TD><TD>3</TD><TD>233</TD><TD>5</TD></TR>

<TR><TD>13</TD><TD>昌平</TD><TD>2</TD><TD>4</TD><TD>5</TD></TR>

<TR><TD>14</TD><TD>西城</TD><TD>2</TD><TD>6</TD><TD>5</TD></TR>

<TR><TD>15</TD><TD>西城</TD><TD>1</TD><TD>73</TD><TD>5</TD></TR>

</TABLE>

</BODY>

</HTML>


上面是一张表，名为 test
我想要的结果是：
division_id nation_id weeknum=6 weeknum=5
海淀 3 34 176
海淀 2 33 55
海淀 1 22 45
昌平 3 44 233
昌平 2 32 4
昌平 1 54 0
西城 1 6 73
西城 2 44 42
...全文
175 17 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
Limpire 2008-04-26
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 shaoml 的回复:]
在weeknum 列 表示周数 即为第几周 weeknum中最大的为本周，weeknum-1为上周，weeknum-4为上月，

我想写sql语句 实现查本周数据，上周数据。 然后本周与上周比 即 本周/上周
我这样写：
select DIVISION_ID,
NATION_ID,
sum(case WEEKNUM when max(weeknum)-1 then ZDRS else 0 end) [weeknum=5],
sum(case WEEKNUM when max(weeknum) then ZDRS else 0 end) [weeknum=6]
from tb
group by DIVISION_ID,NATION_ID
…
[/Quote]

这样写：

--> 测试数据: #T

if object_id('tempdb.dbo.#T') is not null drop table #T

create table #T (DIVISION_ID varchar(4),NATION_ID int,ZDRS int,WEEKNUM int)

insert into #T

select '海淀',3,34,6 union all

select '海淀',2,33,6 union all

select '海淀',1,22,6 union all

select '昌平',3,44,6 union all

select '昌平',2,32,6 union all

select '昌平',1,54,6 union all

select '西城',1,42,6 union all

select '西城',2,44,6 union all

select '海淀',1,45,5 union all

select '海淀',2,55,5 union all

select '海淀',3,176,5 union all

select '昌平',3,233,5 union all

select '昌平',2,4,5 union all

select '西城',2,6,5 union all

select '西城',1,73,5



declare @max int

select @max = max(weeknum) from #T

select

	DIVISION_ID,

	NATION_ID,

	prevWeek = sum(case WEEKNUM when @max-1 then ZDRS else 0 end),

	thisWeek = sum(case WEEKNUM when @max then ZDRS else 0 end)

from #T

group by DIVISION_ID,NATION_ID

order by DIVISION_ID,NATION_ID



/*

DIVISION_ID NATION_ID   prevWeek    thisWeek

----------- ----------- ----------- -----------

昌平          1           0           54

昌平          2           4           32

昌平          3           233         44

海淀          1           45          22

海淀          2           55          33

海淀          3           176         34

西城          1           73          42

西城          2           6           44

*/
shaoml 2008-04-26
  • 打赏
  • 举报
 回复
是在oracle中。
Larry 2008-04-26
  • 打赏
  • 举报
 回复
使用的SQL 2005 么，是的话可以考虑使用以下pivot进行转置
lsd123 2008-04-26
  • 打赏
  • 举报
 回复
学习中
shaoml 2008-04-26
  • 打赏
  • 举报
 回复
在weeknum 列 表示周数 即为第几周 weeknum中最大的为本周，weeknum-1为上周，weeknum-4为上月，

我想写sql语句 实现查本周数据，上周数据。 然后本周与上周比 即 本周/上周
我这样写：
select DIVISION_ID,
NATION_ID,
sum(case WEEKNUM when max(weeknum)-1 then ZDRS else 0 end) [weeknum=5],
sum(case WEEKNUM when max(weeknum) then ZDRS else 0 end) [weeknum=6]
from tb
group by DIVISION_ID,NATION_ID
order by DIVISION_ID,NATION_ID

==========================================================
不能对包含聚合或子查询的表达式执行聚合函数。



该怎么实现？ 本周/上周怎么实现？？
谢谢！！！
shaoml 2008-04-26
  • 打赏
  • 举报
 回复
谢谢各位的关注！
dawugui 2008-04-26
  • 打赏
  • 举报
 回复 
create table tb(DIVISION_ID varchar(20),NATION_ID int,ZDRS int,WEEKNUM int)

insert into tb values('海淀',3,34,6)

insert into tb values('海淀',2,33,6)

insert into tb values('海淀',1,32,6)

insert into tb values('昌平',3,44,6)

insert into tb values('昌平',2,32,6)

insert into tb values('昌平',1,54,6)

insert into tb values('西城',1,42,6)

insert into tb values('西城',2,44,6)

insert into tb values('海淀',1,45,5)

insert into tb values('海淀',2,55,5)

insert into tb values('海淀',3,176,5)

insert into tb values('昌平',3,233,5)

insert into tb values('昌平',2,4,5)

insert into tb values('西城',2,6,5)

insert into tb values('西城',1,73,5)

go

--方法一

select DIVISION_ID, 

       NATION_ID, 

       sum(case WEEKNUM when 5 then ZDRS else 0 end) [weeknum=5], 

       sum(case WEEKNUM when 6 then ZDRS else 0 end) [weeknum=6] 

from tb 

group by DIVISION_ID,NATION_ID 

order by DIVISION_ID,NATION_ID 



--方法二

select DIVISION_ID, 

       NATION_ID, 

       [weeknum=5] = isnull((select sum(ZDRS) from tb where WEEKNUM = 5 and DIVISION_ID = t.DIVISION_ID and NATION_ID = t.NATION_ID),0) , 

       [weeknum=6] = isnull((select sum(ZDRS) from tb where WEEKNUM = 6 and DIVISION_ID = t.DIVISION_ID and NATION_ID = t.NATION_ID),0)

from tb t

group by DIVISION_ID,NATION_ID 

order by DIVISION_ID,NATION_ID 



drop table tb



/*

DIVISION_ID          NATION_ID   weeknum=5   weeknum=6   

-------------------- ----------- ----------- ----------- 

昌平                   1           0           54

昌平                   2           4           32

昌平                   3           233         44

海淀                   1           45          32

海淀                   2           55          33

海淀                   3           176         34

西城                   1           73          42

西城                   2           6           44



（所影响的行数为 8 行）

*/
dawugui 2008-04-26
  • 打赏
  • 举报
 回复 
create table tb(DIVISION_ID varchar(20),NATION_ID int,ZDRS int,WEEKNUM int)

insert into tb values('海淀',3,34,6)

insert into tb values('海淀',2,33,6)

insert into tb values('海淀',1,32,6)

insert into tb values('昌平',3,44,6)

insert into tb values('昌平',2,32,6)

insert into tb values('昌平',1,54,6)

insert into tb values('西城',1,42,6)

insert into tb values('西城',2,44,6)

insert into tb values('海淀',1,45,5)

insert into tb values('海淀',2,55,5)

insert into tb values('海淀',3,176,5)

insert into tb values('昌平',3,233,5)

insert into tb values('昌平',2,4,5)

insert into tb values('西城',2,6,5)

insert into tb values('西城',1,73,5)

go



select DIVISION_ID, 

       NATION_ID, 

       sum(case WEEKNUM when 5 then ZDRS else 0 end) [weeknum=5], 

       sum(case WEEKNUM when 6 then ZDRS else 0 end) [weeknum=6] 

from tb 

group by DIVISION_ID,NATION_ID 

order by DIVISION_ID,NATION_ID 



drop table tb



/*

DIVISION_ID          NATION_ID   weeknum=5   weeknum=6   

-------------------- ----------- ----------- ----------- 

昌平                   1           0           54

昌平                   2           4           32

昌平                   3           233         44

海淀                   1           45          32

海淀                   2           55          33

海淀                   3           176         34

西城                   1           73          42

西城                   2           6           44



（所影响的行数为 8 行）

*/
kingmax54212008 2008-04-26
  • 打赏
  • 举报
 回复
select DIVISION_ID,NATION_ID,
sum(case when weeknum=6 then zdrs end) as 'weeknum=6',
sum(case when weeknum=5 then zdrs end) as 'weeknum=5'
from 表
group by DIVISION_ID,NATION_ID
dawugui 2008-04-26
  • 打赏
  • 举报
 回复
SQL codeselect DIVISION_ID,
NATION_ID,
sum(case WEEKNUM when 5 then ZDRS else 0 end) [weeknum=5],
sum(case WEEKNUM when 6 then ZDRS else 0 end) [weeknum=6]
from tb
group by DIVISION_ID,NATION_ID
order by DIVISION_ID,NATION_ID
dawugui 2008-04-26
  • 打赏
  • 举报
 回复 
select DIVISION_ID,

       NATION_ID,

       sum(case WEEKNUM when 5 then ZDRS else 0 end) [weeknum=5],

       sum(case WEEKNUM when 5 then ZDRS else 0 end) [weeknum=6]

from tb

group by DIVISION_ID,NATION_ID

order by DIVISION_ID,NATION_ID


火星求索 2008-04-26
  • 打赏
  • 举报
 回复
说一下条件，没看明白
wzy_love_sly 2008-04-26
  • 打赏
  • 举报
 回复 


declare @tb table (DIVISION_ID varchar(20),NATION_ID int,ZDRS int,WEEKNUM int)

insert into @tb select '海淀',3,34,6

insert into @tb select '海淀',2,33,6

insert into @tb select '海淀',1,32,6

insert into @tb select '昌平',3,44,6

insert into @tb select '昌平',2,32,6

insert into @tb select '昌平',1,54,6

insert into @tb select '西城',1,42,6

insert into @tb select '西城',2,44,6



insert into @tb select '海淀',1,45,5

insert into @tb select '海淀',2,55,5

insert into @tb select '海淀',3,176,5



insert into @tb select '昌平',3,233,5

insert into @tb select '昌平',2,4,5

insert into @tb select '西城',2,6,5

insert into @tb select '西城',1,73,5



select DIVISION_ID,NATION_ID, 

isnull(sum(case when weeknum=6 then zdrs end),0) as 'weeknum=6', 

isnull(sum(case when weeknum=5 then zdrs end),0) as 'weeknum=5' 

from @tb group by DIVISION_ID,NATION_ID

order by DIVISION_ID


DIVISION_ID NATION_ID weeknum=6 weeknum=5
昌平 1 54 0
昌平 2 32 4
昌平 3 44 233
海淀 1 32 45
海淀 2 33 55
海淀 3 34 176
西城 1 42 73
西城 2 44 6
liangCK 2008-04-26
  • 打赏
  • 举报
 回复
select DIVISION_ID,NATION_ID,
sum(case when weeknum=6 then zdrs end) as 'weeknum=6',
sum(case when weeknum=5 then zdrs end) as 'weeknum=5'
from 表
group by DIVISION_ID,NATION_ID
wzy_love_sly 2008-04-26
  • 打赏
  • 举报
 回复 
select DIVISION_ID,NATION_ID,

sum(case when weeknum=6 then zdrs end) as 'weeknum=6',

sum(case when weeknum=5 then zdrs end) as 'weeknum=5'

from 表

group by DIVISION_ID,NATION_ID
liangCK 2008-04-26
  • 打赏
  • 举报
 回复
严重关注.
shaoml 2008-04-26
  • 打赏
  • 举报
 回复
哦，真孬~~~~ 表是这样的 ：

DIVISION_ID NATION_ID ZDRS WEEKNUM
海淀 3 34 6
海淀 2 33 6
海淀 1 22 6
昌平 3 44 6
昌平 2 32 6
昌平 1 54 6
西城 1 42 6
西城 2 44 6
海淀 1 45 5
海淀 2 55 5
海淀 3 176 5
昌平 3 233 5
昌平 2 4 5
西城 2 6 5
西城 1 73 5
SQL语句基础教程
SQL语句基础教程
Access_SQL语句
Access_SQL语句详细介绍了access所使用的sql语句~~~
经典SQL语句大全（文档）
经典SQL语句大全（文档）,绝对实用，都是一些常用的语句，可以帮助你很快找到灵感！
SQL语句培训文档
SQL语句培训文档
设备状态监测语句.~sql
设备状态监测语句.~sql
MS-SQL Server

34,838

社区成员

254,632

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章