select 月份,id,count(*) as 次数
from (
select convert(char(7),日期,120) as 月份,id1 as id
from table1
union all
select convert(char(7),日期,120) as 月份,id2 as id
from table1
union all
select convert(char(7),日期,120) as 月份,id3 as id
from table1
union all
select convert(char(7),日期,120) as 月份,id4 as id
from table1
) as t
group by 月份,id
from (
select convert(char(7),日期,120) as 月份,id1 as id
from table1
union all
select convert(char(7),日期,120) as 月份,id2 as id
from table1
union all
select convert(char(7),日期,120) as 月份,id3 as id
from table1
union all
select convert(char(7),日期,120) as 月份,id4 as id
from table1
) as t
group by 月份,id
解决方案 »
- SQL 选择出某个字段 比一个集合中最小的值 小的所有记录
- 如何 在英文版的windows server 2003 Enterprise X64 edition Sp1 上安装SQL2005企业版?
- 连接sql server2008慢的问题
- sql server 2008 使用SELECT查询语句时,提示:Msg 208,Invalid object name 'dbo.学生信息'.
- 能在where子句中写这样的case语句吗?
- 一个关联查询的问题
- 简单的SQL语句,在线等。
- sql 死锁
- 高手快来-》又一个有关字符串操作的问题
- 年度按周查詢
- 求一条sql语句。。。。。。
- 如何向表中插入一个空记录
from
(select id1 as id from csdn
union
select id2 from csdn where id2<>id1
union
select id3 from csdn where id3<>id2 and id3<>id1
union
select id4 from csdn where id4<>id3 and id4<>id2 and id4<>id1) b,
(select id,sum(1) as cnt
from
(select id1 as id from csdn
union
select id2 from csdn where id2<>id1
union
select id3 from csdn where id3<>id2 and id3<>id1
union
select id4 from csdn where id4<>id3 and id4<>id2 and id4<>id1) d
where id in (select (case when id=id1 then id1
when id=id2 then id2
when id=id3 then id3
when id=id4 then id4
else '' end) from csdn) group by d.id) c
where b.id=c.id
这样是不对的,明天继续:)
有错误,帮忙帮到底吧!!~
--建立环境
create table table1(
日期 datetime,
id1 int,
id2 int,
id3 int,
id4 int
)
goinsert table1
select
'2004-08-04',67,122,68,97
union all select
'2004-08-04',69,98,70,99
union all select
'2004-08-04',71,112,72,115
union all select
'2004-08-04',77,104,78,105
union all select
'2004-08-04',79,116,80,107
union all select
'2004-08-04',81,108,82,117
union all select
'2004-08-04',91,102,74,103
union all select
'2004-08-04',93,110,84,111
union all select
'2004-08-05',67,100,68,101
union all select
'2004-08-05',69,98,70,0
union all select
'2004-08-05',71,0,91,115
union all select
'2004-08-05',73,102,74,103
union all select
'2004-08-05',77,104,78,106
union all select
'2004-08-05',79,116,80,107
union all select
'2004-08-05',81,109,93,117
union all select
'2004-08-05',83,110,84,111
union all select
'2004-08-06',67,100,68,101
union all select
'2004-08-06',69,98,70,97
union all select
'2004-08-06',73,102,74,103
union all select
'2004-08-06',77,105,78,106
union all select
'2004-08-06',79,108,80,107
union all select
'2004-08-06',83,110,84,111
union all select
'2004-08-06',91,99,72,112
union all select
'2004-08-06',93,109,82,117
union all select
'2004-08-07',67,100,68,101
union all select
'2004-08-07',69,115,91,97
union all select
'2004-08-07',71,99,72,112
union all select
'2004-08-07',73,102,74,103
union all select
'2004-08-07',77,105,78,106
union all select
'2004-08-07',79,108,93,107
union all select
'2004-08-07',81,109,82,104
union all select
'2004-08-07',83,110,84,116
union all select
'2004-08-08',67,100,68,101
union all select
'2004-08-08',71,99,72,112
union all select
'2004-08-08',73,98,74,117
union all select
'2004-08-08',77,105,78,106
union all select
'2004-08-08',81,109,82,104
union all select
'2004-08-08',83,111,84,116
union all select
'2004-08-08',91,115,70,97
union all select
'2004-08-08',93,108,80,107
union all select
'2004-08-09',67,102,91,103
union all select
'2004-08-09',69,115,70,0
union all select
'2004-08-09',71,99,72,112
union all select
'2004-08-09',73,98,74,117
union all select
'2004-08-09',77,105,93,106
union all select
'2004-08-09',79,108,80,110
union all select
'2004-08-09',81,0,82,104
union all select
'2004-08-09',83,111,84,116
go--语句
select 月份,id,count(*) as 次数
from (
select convert(char(7),日期,120) as 月份,id1 as id
from table1
union all
select convert(char(7),日期,120) as 月份,id2 as id
from table1
union all
select convert(char(7),日期,120) as 月份,id3 as id
from table1
union all
select convert(char(7),日期,120) as 月份,id4 as id
from table1
) as t
group by 月份,id--结果
/*
月份 id 次数
------- ----------- -----------
2004-08 0 4
2004-08 67 6
2004-08 68 5
2004-08 69 5
2004-08 70 5
2004-08 71 5
2004-08 72 5
2004-08 73 5
2004-08 74 6
2004-08 77 6
2004-08 78 5
2004-08 79 5
2004-08 80 5
2004-08 81 5
2004-08 82 5
2004-08 83 5
2004-08 84 6
2004-08 91 6
2004-08 93 6
2004-08 97 4
2004-08 98 5
2004-08 99 5
2004-08 100 4
2004-08 101 4
2004-08 102 5
2004-08 103 5
2004-08 104 5
2004-08 105 5
2004-08 106 5
2004-08 107 5
2004-08 108 5
2004-08 109 4
2004-08 110 5
2004-08 111 5
2004-08 112 5
2004-08 115 5
2004-08 116 5
2004-08 117 5
2004-08 122 1(所影响的行数为 39 行)
*/
create table #t(rq datetime,id int,id1 int,id2 int)
insert into #t select getdate(), 1,2,3
insert into #t select getdate(), 6,1,2
insert into #T select dateadd(mm,1,getdate()), 6,1,2
select * from #T2004-08-04 08:47:15.207 1 2 3
2004-08-04 08:47:25.210 6 1 2
2004-09-04 08:56:23.857 6 1 2 select rq,id,count(id)as 数目
from
(select datepart(month,rq) as rq,id from #t
union all
select datepart(month,rq) as rq,id1 from #t
union all
select datepart(month,rq) as rq,id2 from #t) a group by id,rq----
rq id 数目
----------- ----------- -----------
8 1 2
8 2 2
8 3 1
8 6 1
9 1 1
9 2 1
9 6 1