求几个sql语句(急)

2008-06-16 02:59:08 203 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

基于Matlab的MIMO通信系统仿真_Simulink实现瑞利衰落信道中使用空时分组码的BPSK调制的BER 1、2发1收参数设置：双极性信号元+1和-1，每符号含1bit; 采样时间=符号时间=1s; 采用基于数据帧的仿真计算模式，每帧中有2个采样值; 仿真时间=出现100个误码后停止仿真; 随机整数种子=22; Jakes衰落模型中每个信道上的多普勒频移=0.01Hz; 输人信号功率=2W; Alamouti码:两根发送天线，一根接收天线(分集阶= 2); 平均信道信噪比= 18dB; 仿真得出的误码率统计BER =0. 00017. 2、2发阅读详情

已知：表: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语句。即只要在页面上显示如上数据

...全文

AIR780E二次开发点灯(LuatOS) Air780E的LuatOS点灯教程，二次开发教程，可用于所有ec618平台，无需SDK 阅读详情

快跑蜗牛哥

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

kevinllq 2008-06-17

打赏
举报

汇总的不行,估计问题出在日期的字符串上,如2008-1,2008-01;
上边发的只是个示例,比如字符串的判断比较可以换成日期型的.

zhiguo2008 2008-06-17

打赏
举报

首先顶一下,等一下再回复

快跑蜗牛哥 2008-06-16

打赏
举报

TO:kevinllq 谢谢了，不过汇总的不行，
谢谢所有回帖的朋友们，我在看看

lass_name 2008-06-16

打赏
举报

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语句(急)

相关推荐