求几个sql语句(急)

快跑蜗牛哥 2008-06-16 02:59:08

已知：表:papernum
nid factno deptno padate mou
1 1 2 2007-07 56
2 1 3 2007-07 26
3 1 2 2008-01 10
4 2 1 2007-06 25
5 3 1 2007-06 12
6 2 2 2008-06 25
7 3 3 2008-05 26
8 1 3 2007-06 30
...
表：fact
fno factname
1 cs1
2 cs2
3 cs3
...
表： dept
did deptname
1 AS1
2 AS2
3 AS3
...
现在在页面上要显示：
fa/pa 一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月 tota
cs1/2008 10 10
cs2/2008 25 25
cs3/2008 26 26
cs1/2007 30 82 112
cs2/2007 25 25
cs3/2007 12 12
total2 10 26 92 82 210
可用多个sql语句。即只要在页面上显示如上数据

...全文
158 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
kevinllq 2008-06-17
  • 打赏
  • 举报
 回复
汇总的不行,估计问题出在日期的字符串上,如2008-1,2008-01;
上边发的只是个示例,比如字符串的判断比较可以换成日期型的.
zhiguo2008 2008-06-17
  • 打赏
  • 举报
 回复
首先顶一下,等一下再回复
快跑蜗牛哥 2008-06-16
  • 打赏
  • 举报
 回复
TO:kevinllq 谢谢了，不过汇总的不行，
谢谢所有回帖的朋友们，我在看看
lass_name 2008-06-16
  • 打赏
  • 举报
 回复
up
dadi5566 2008-06-16
  • 打赏
  • 举报
 回复
你可以使用内置函数 year(),month(),day()
事例如下：
select year(getdate()),month(getdate()),day(getdate())
LIHY70 2008-06-16
  • 打赏
  • 举报
 回复 


select b.factoryname/left(a.padate,4) as fa/pa,

'一月'=sum(case when right(a.padate,2)='01' then isnull(a.mou,0) else 0 end),

'二月'=sum(case when right(a.padate,2)='02' then isnull(a.mou,0) else 0 end),

.....

'total'=(select sum(mou) from papernum c where c.factno=a.factno and left(c.padate,4)=left(a.padate,4))

 from papernum a, factname b where  a.factno=b.fno group by b.factoryname,left(padate,4)  

union all

select 'total' as fa/pa,

'一月'=sum(case when right(padate,2)='01' then isnull(mou,0) else 0 end),

'二月'=sum(case when right(padate,2)='02' then isnull(mou,0) else 0 end),

.....

'total'=sum(mou) 

 from papernum
linguojin11 2008-06-16
  • 打赏
  • 举报
 回复
完全看不懂。。。什么时候才能象大家那样。。哎。。郁闷
kevinllq 2008-06-16
  • 打赏
  • 举报
 回复
不好意思，上边那个少了个列汇总，这个补上了
select b.factname+'/'+a.pa "fa/pa", a.*
from
(select factno,substring(padate,1,4) pa,
sum(case when substring(padate,6,2) = '01' then mou else 0 end) '一月',
sum(case when substring(padate,6,2) = '02' then mou else 0 end) '二月',
sum(case when substring(padate,6,2) = '03' then mou else 0 end) '三月',
sum(case when substring(padate,6,2) = '04' then mou else 0 end) '四月',
sum(case when substring(padate,6,2) = '05' then mou else 0 end) '五月',
sum(case when substring(padate,6,2) = '06' then mou else 0 end) '六月',
sum(case when substring(padate,6,2) = '07' then mou else 0 end) '七月',
sum(case when substring(padate,6,2) = '08' then mou else 0 end) '八月',
sum(case when substring(padate,6,2) = '09' then mou else 0 end) '九月',
sum(case when substring(padate,6,2) = '10' then mou else 0 end) '十月',
sum(case when substring(padate,6,2) = '11' then mou else 0 end) '十一月',
sum(case when substring(padate,6,2) = '12' then mou else 0 end) '十二月',
sum(mou) total
from papernum
group by factno,substring(padate,1,4)) a, fact b
where a.factno=b.fno
union all
select 'total', null,null,
sum(case when substring(padate,6,2) = '01' then mou else 0 end) '一月',
sum(case when substring(padate,6,2) = '02' then mou else 0 end) '二月',
sum(case when substring(padate,6,2) = '03' then mou else 0 end) '三月',
sum(case when substring(padate,6,2) = '04' then mou else 0 end) '四月',
sum(case when substring(padate,6,2) = '05' then mou else 0 end) '五月',
sum(case when substring(padate,6,2) = '06' then mou else 0 end) '六月',
sum(case when substring(padate,6,2) = '07' then mou else 0 end) '七月',
sum(case when substring(padate,6,2) = '08' then mou else 0 end) '八月',
sum(case when substring(padate,6,2) = '09' then mou else 0 end) '九月',
sum(case when substring(padate,6,2) = '10' then mou else 0 end) '十月',
sum(case when substring(padate,6,2) = '11' then mou else 0 end) '十一月',
sum(case when substring(padate,6,2) = '12' then mou else 0 end) '十二月',
sum(mou) total
from papernum
kevinllq 2008-06-16
  • 打赏
  • 举报
 回复
你看一下吧，结果比你所要的多两列
select b.factname+'/'+a.pa "fa/pa", a.*
from
(select factno,substring(padate,1,4) pa,
sum(case when substring(padate,6,2) = '01' then mou else 0 end) '一月',
sum(case when substring(padate,6,2) = '02' then mou else 0 end) '二月',
sum(case when substring(padate,6,2) = '03' then mou else 0 end) '三月',
sum(case when substring(padate,6,2) = '04' then mou else 0 end) '四月',
sum(case when substring(padate,6,2) = '05' then mou else 0 end) '五月',
sum(case when substring(padate,6,2) = '06' then mou else 0 end) '六月',
sum(case when substring(padate,6,2) = '07' then mou else 0 end) '七月',
sum(case when substring(padate,6,2) = '08' then mou else 0 end) '八月',
sum(case when substring(padate,6,2) = '09' then mou else 0 end) '九月',
sum(case when substring(padate,6,2) = '10' then mou else 0 end) '十月',
sum(case when substring(padate,6,2) = '11' then mou else 0 end) '十一月',
sum(case when substring(padate,6,2) = '12' then mou else 0 end) '十二月',
sum(mou) total
from papernum
group by factno,substring(padate,1,4)) a, fact b
where a.factno=b.fno
wzy_love_sly 2008-06-16
  • 打赏
  • 举报
 回复 
create table tb(id int,date datetime,sale decimal(10,2))

insert into tb select 1,'2008-01-01',10

insert into tb select 2,'2008-01-01',10

insert into tb select 3,'2008-02-01',20

insert into tb select 4,'2008-02-01',20

insert into tb select 5,'2008-03-01',30

insert into tb select 6,'2008-03-01',30

insert into tb select 7,'2008-04-01',40

insert into tb select 8,'2008-04-01',40

insert into tb select 9,'2008-05-01',50

insert into tb select 10,'2008-05-01',50

insert into tb select 11,'2008-06-01',60

insert into tb select 12,'2008-06-01',60

insert into tb select 13,'2008-07-01',70

insert into tb select 14,'2008-07-01',70

insert into tb select 15,'2008-08-01',80

insert into tb select 16,'2008-08-01',80

insert into tb select 17,'2008-09-01',90

insert into tb select 18,'2008-09-01',90

insert into tb select 19,'2008-10-01',100

insert into tb select 20,'2008-10-01',100

insert into tb select 21,'2008-11-01',110

insert into tb select 22,'2008-11-01',110

insert into tb select 23,'2008-12-01',120

insert into tb select 24,'2008-12-01',120



--静态查询

select 

sum(case when datepart(yy,date)='2008' and datepart(mm,date)='01' then sale else 0 end) as '1月',

sum(case when datepart(yy,date)='2008' and datepart(mm,date)='02' then sale else 0 end) as '2月',

sum(case when datepart(yy,date)='2008' and datepart(mm,date)='03' then sale else 0 end) as '3月',

sum(case when datepart(yy,date)='2008' and datepart(mm,date)='04' then sale else 0 end) as '4月',



sum(case when datepart(yy,date)='2008' and datepart(mm,date)='05' then sale else 0 end) as '5月',

sum(case when datepart(yy,date)='2008' and datepart(mm,date)='06' then sale else 0 end) as '6月',

sum(case when datepart(yy,date)='2008' and datepart(mm,date)='07' then sale else 0 end) as '7月',

sum(case when datepart(yy,date)='2008' and datepart(mm,date)='08' then sale else 0 end) as '8月',



sum(case when datepart(yy,date)='2008' and datepart(mm,date)='09' then sale else 0 end) as '9月',

sum(case when datepart(yy,date)='2008' and datepart(mm,date)='10' then sale else 0 end) as '10月',

sum(case when datepart(yy,date)='2008' and datepart(mm,date)='11' then sale else 0 end) as '11月',

sum(case when datepart(yy,date)='2008' and datepart(mm,date)='12' then sale else 0 end) as '12月'

from tb

--插入2009年数据

insert into tb select 25,'2009-01-01',10

--动态查询

declare @sql varchar(max)

select @sql=isnull(@sql+',','')+'sum(case when datepart(yy,date)='''+ltrim([year])+''' and datepart(mm,date)='''+ltrim([month])+''' then sale else 0 end) as ['+ltrim([year])+'年'+ltrim([month])+'月]'

from (select distinct datepart(yy,date) as [year],datepart(mm,date) as [month] from tb)a

exec('select '+@sql+' from tb')













中国风 2008-06-16
  • 打赏
  • 举报
 回复 
 

if not object_id('papernum') is null

	drop table papernum

Go

Create table papernum([nid] int,[factno] int,[deptno] int,[padate] nvarchar(7),[mou] int)

Insert papernum

select 1,1,2,N'2007-07',56 union all

select 2,1,3,N'2007-07',26 union all

select 3,1,2,N'2008-01',10 union all

select 4,2,1,N'2007-06',25 union all

select 5,3,1,N'2007-06',12 union all

select 6,2,2,N'2008-06',25 union all

select 7,3,3,N'2008-05',26 union all

select 8,1,3,N'2007-06',30

Go

if not object_id('fact') is null

	drop table fact

Go

Create table fact([fno] int,[factname] nvarchar(3))

Insert fact

select 1,N'cs1' union all

select 2,N'cs2' union all

select 3,N'cs3'

Go

--dept沒有用到

if not object_id('dept') is null

	drop table dept

Go

Create table dept([did] int,[deptname] nvarchar(3))

Insert dept

select 1,N'AS1' union all

select 2,N'AS2' union all

select 3,N'AS3'

Go

declare @s nvarchar(4000),@s2 nvarchar(4000),@i int

select @s='',@s2='',@i=12

while @i>0

	select @s=','+quotename(case @i	when 1 then '一月' when 2 then '二月' when 3 then '三月' when 4 then '四月' when 5 then '五月' when 6 then '六月'

				when 7 then '七月' when 8 then '八月' when 9 then '九月' when 10 then '十月' when 11 then '十一月' when 12 then '十二月' end)

				+'=sum(case when right([padate],2)='+rtrim(@i)+' then [mou] else 0 end)'+@s,

				@s2=','+quotename(case @i	when 1 then '一月' when 2 then '二月' when 3 then '三月' when 4 then '四月' when 5 then '五月' when 6 then '六月'

						when 7 then '七月' when 8 then '八月' when 9 then '九月' when 10 then '十月' when 11 then '十一月' when 12 then '十二月' end)

						+'=sum(case when right([padate],2)='+rtrim(@i)+' then [mou] else 0 end)'+@s2,

				@i=@i-1	

	

exec('select b.[factname]+''/''+left(a.[padate],4) as [fa/pa]'+@s+',[total]=(select sum([mou]) from papernum where left([padate],4)=left(a.[padate],4) and [factno]=a.[factno])

 from papernum a join fact b on a.[factno]=b.[fno] group by b.[factname],left(a.[padate],4),a.[factno]

union all 

select [fa/pa]=''Total'''+@s2+',sum([mou]) from papernum order by [fa/pa]')





fa/pa    一月          二月          三月          四月          五月          六月          七月          八月          九月          十月          十一月         十二月         total

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

cs1/2007 0           0           0           0           0           30          82          0           0           0           0           0           112

cs1/2008 10          0           0           0           0           0           0           0           0           0           0           0           10

cs2/2007 0           0           0           0           0           25          0           0           0           0           0           0           25

cs2/2008 0           0           0           0           0           25          0           0           0           0           0           0           25

cs3/2007 0           0           0           0           0           12          0           0           0           0           0           0           12

cs3/2008 0           0           0           0           26          0           0           0           0           0           0           0           26

Total    10          0           0           0           26          92          82          0           0           0           0           0           210



(7 個資料列受到影響)
中国风 2008-06-16
  • 打赏
  • 举报
 回复 
 

if not object_id('papernum') is null

	drop table papernum

Go

Create table papernum([nid] int,[factno] int,[deptno] int,[padate] nvarchar(7),[mou] int)

Insert papernum

select 1,1,2,N'2007-07',56 union all

select 2,1,3,N'2007-07',26 union all

select 3,1,2,N'2008-01',10 union all

select 4,2,1,N'2007-06',25 union all

select 5,3,1,N'2007-06',12 union all

select 6,2,2,N'2008-06',25 union all

select 7,3,3,N'2008-05',26 union all

select 8,1,3,N'2007-06',30

Go

if not object_id('fact') is null

	drop table fact

Go

Create table fact([fno] int,[factname] nvarchar(3))

Insert fact

select 1,N'cs1' union all

select 2,N'cs2' union all

select 3,N'cs3'

Go

--dept沒有用到

if not object_id('dept') is null

	drop table dept

Go

Create table dept([did] int,[deptname] nvarchar(3))

Insert dept

select 1,N'AS1' union all

select 2,N'AS2' union all

select 3,N'AS3'

Go

declare @s nvarchar(4000),@i int

select @s='',@i=12

while @i>0

	select @s=','+quotename(case @i	when 1 then '一月' when 2 then '二月' when 3 then '三月' when 4 then '四月' when 5 then '五月' when 6 then '六月'

						when 7 then '七月' when 8 then '八月' when 9 then '九月' when 10 then '十月' when 11 then '十一月' when 12 then '十二月' end)

				+'=sum(case when right([padate],2)='+rtrim(@i)+' then [mou] else 0 end)'+@s,@i=@i-1				

exec('select b.[factname]+''/''+left(a.[padate],4) as [fa/pa]'+@s+',[total]=(select sum([mou]) from papernum where left([padate],4)=left(a.[padate],4) and [factno]=a.[factno])

 from papernum a join fact b on a.[factno]=b.[fno] group by b.[factname],left(a.[padate],4),a.[factno] order by left(a.[padate],4)desc')





fa/pa    一月          二月          三月          四月          五月          六月          七月          八月          九月          十月          十一月         十二月         total

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

cs1/2008 10          0           0           0           0           0           0           0           0           0           0           0           10

cs2/2008 0           0           0           0           0           25          0           0           0           0           0           0           25

cs3/2008 0           0           0           0           26          0           0           0           0           0           0           0           26

cs1/2007 0           0           0           0           0           30          82          0           0           0           0           0           112

cs2/2007 0           0           0           0           0           25          0           0           0           0           0           0           25

cs3/2007 0           0           0           0           0           12          0           0           0           0           0           0           12



(6 個資料列受到影響)
中国风 2008-06-16
  • 打赏
  • 举报
 回复
2000/05?
中国风 2008-06-16
  • 打赏
  • 举报
 回复
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html
行列互转
快跑蜗牛哥 2008-06-16
  • 打赏
  • 举报
 回复
数据怎么老是错位，
快跑蜗牛哥 2008-06-16
  • 打赏
  • 举报
 回复

现在在页面上要显示：
fa/pa 一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月 tota
cs1/2008 10 10
cs2/2008 25 25
cs3/2008 26 26
cs1/2007 30 82 112
cs2/2007 25 25
cs3/2007 12 12
total 10 26 92 82 210

史上超强最常用SQL语句大全
史上超强最常用SQL语句大全，) 1)DDL--数据定义语言 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等 2) DML--数据操作语言 用来对数据库中表的数据进行增删改。关键字：insert, delete, ...
经典SQL语句大全
SQL语句参考，包含Access、MySQL 以及 SQL Server基础创建数据库CREATE DATABASE database-name 删除数据库drop database dbname 备份sql server 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', ...
SQL语句五种分类
SQL的英文全称为Structured Query Language，SQL用来和数据库打交道，完成和数据库的通信，SQL是一套标准，但是每一个数据库都有自己的特性，当使用这个数据库特性相关的功能，这时SQL语句可能就不是标准了，但是...
数据库常用的sql语句大全--sql
每个SQL语句都是亲身实验验证的，并且经过自己的思考的。能够保证sql语句的可运行性。 sql语句的命令不区分大小写，但储存的数据是区分大小写的。在这里我们统一使用英文小写进行命令编辑。如果喜欢大写的可以使用...
Oracle SQL语句之常见优化方法总结
 当在SQL语句中连接多个表时, 尽量使用表的别名并把别名前缀于每个列上。这样一来, 就可以减少解析的时间并减少那些由列歧义引起的语法错误。 3、选择最有效率的表名顺序(只在基于规则的优化器(RB
MS-SQL Server

34,576

社区成员

254,586

社区内容

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

加载中

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

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

+ 用AI写文章