http://community.csdn.net/Expert/topic/4866/4866760.xml?temp=.2526209Create Table table1
([user]Varchar(10),
date DateTime,
mflage Bit,
[count]Int)
Insert table1 Select 'a', '2004-06-01', 0, 20
Union All Select 'b', '2004-06-11', 0, 12
Union All Select 'b', '2004-06-11', 1, 12
Union All Select 'a', '2004-07-01', 0, 13
Union All Select 'a', '2004-08-01', 0, 17
Union All Select 'a', '2004-08-01', 1, 13
Union All Select 'b', '2004-08-01', 1, 1
GO
Declare @S Varchar(8000)
Set @S=''
Select @S=@S+',SUM(Case When Left([date],7)='''+[date]+''' And mflage=0 Then [count] Else 0 End) As '''+[date]+'A'' ,SUM(Case When Left([date],7)='''+[date]+''' And mflage=1 Then [count] Else 0 End) As '''+[date]+'B'''
From (Select Distinct Convert(Varchar(7),[date],120) As [date] From table1) A Order By [date]
Select @S='Select [user]'+@S+' From table1 Group By [user]'
EXEC(@S)
GO
Drop Table table1
--Result
/*
user2004-06A2004-06B2004-07A2004-07B2004-08A2004-08B
a2001301713
b12120001
*/下面这个部分看不明白!!!望详解!!!呵呵!!!
Select @S=@S+',SUM(Case When Left([date],7)='''+[date]+''' And mflage=0 Then [count] Else 0 End) As '''+[date]+'A'' ,SUM(Case When Left([date],7)='''+[date]+''' And mflage=1 Then [count] Else 0 End) As '''+[date]+'B'''
([user]Varchar(10),
date DateTime,
mflage Bit,
[count]Int)
Insert table1 Select 'a', '2004-06-01', 0, 20
Union All Select 'b', '2004-06-11', 0, 12
Union All Select 'b', '2004-06-11', 1, 12
Union All Select 'a', '2004-07-01', 0, 13
Union All Select 'a', '2004-08-01', 0, 17
Union All Select 'a', '2004-08-01', 1, 13
Union All Select 'b', '2004-08-01', 1, 1
GO
Declare @S Varchar(8000)
Set @S=''
Select @S=@S+',SUM(Case When Left([date],7)='''+[date]+''' And mflage=0 Then [count] Else 0 End) As '''+[date]+'A'' ,SUM(Case When Left([date],7)='''+[date]+''' And mflage=1 Then [count] Else 0 End) As '''+[date]+'B'''
From (Select Distinct Convert(Varchar(7),[date],120) As [date] From table1) A Order By [date]
Select @S='Select [user]'+@S+' From table1 Group By [user]'
EXEC(@S)
GO
Drop Table table1
--Result
/*
user2004-06A2004-06B2004-07A2004-07B2004-08A2004-08B
a2001301713
b12120001
*/下面这个部分看不明白!!!望详解!!!呵呵!!!
Select @S=@S+',SUM(Case When Left([date],7)='''+[date]+''' And mflage=0 Then [count] Else 0 End) As '''+[date]+'A'' ,SUM(Case When Left([date],7)='''+[date]+''' And mflage=1 Then [count] Else 0 End) As '''+[date]+'B'''
解决方案 »
- 如何查看库里所有有数据的表的表名字,百分送上
- INNER JOIN查询的问题
- 关系模型中的关系,元组怎么理解?
- SQLite数据库连接程序怎么写?~急~~在线等!
- SQL sever访问 Oracle时出错!
- 一个关于delete的问题?在线等~~~~
- 在登录中删除了系统管理员账号怎么办?
- for replication的意思
- SQL server 触发器问题:无法绑定由多个部分组成的标识符
- 如何在MSSQL中根据其他表来更新本表新增字段
- 怎样把datetime类型的数据转换为varchar类型,而且显示为"2006-08-28"样式?
- group by 是不是只能取一个字段呀和他的聚合运算,如果要取多个表里的字段的话是不是全要写在group by后面了,
,SUM(Case When Left([date],7)='2006-02' And mflage=0 Then [count] Else 0 End) As '2006-02A' ,SUM(Case When Left([date],7)='2006-02' And mflage=1 Then [count] Else 0 End) As '2006-02B'
...
呵呵 这句 又是什么意思???