数据如下:
id email date
1 [email protected] 2009-08-01
2 [email protected] 2009-08-01
3 [email protected] 2009-08-01
4 [email protected] 2009-08-01
5 [email protected] 2009-08-01
6 [email protected] 2009-08-01
7 [email protected] 2009-08-02
8 [email protected] 2009-08-02
9 [email protected] 2009-08-02
10 [email protected] 2009-08-02
11 [email protected] 2009-08-02
12 [email protected] 2009-08-02
...
我要求查询每天的
email相同的记录大于3的email,并显示有多少条
结果应该如下email date total
[email protected] 2009-08-01 [email protected] 2009-08-02 5
id email date
1 [email protected] 2009-08-01
2 [email protected] 2009-08-01
3 [email protected] 2009-08-01
4 [email protected] 2009-08-01
5 [email protected] 2009-08-01
6 [email protected] 2009-08-01
7 [email protected] 2009-08-02
8 [email protected] 2009-08-02
9 [email protected] 2009-08-02
10 [email protected] 2009-08-02
11 [email protected] 2009-08-02
12 [email protected] 2009-08-02
...
我要求查询每天的
email相同的记录大于3的email,并显示有多少条
结果应该如下email date total
[email protected] 2009-08-01 [email protected] 2009-08-02 5
FROM TB
GROUP BY EMAIL,DATE HAVING COUNT(ID)>3
from tb
group by email,date
having count(*) > 3
email,
convert(varchar(10),[date],120) as [date],
count(1) as total
from
tb
group by
email,
convert(varchar(10),[date],120)
having
count(1)>3
即可
INSERT @TB
SELECT 1, '[email protected]', '2009-08-01' UNION ALL
SELECT 2, '[email protected]', '2009-08-01' UNION ALL
SELECT 3, '[email protected]', '2009-08-01' UNION ALL
SELECT 4, '[email protected]', '2009-08-01' UNION ALL
SELECT 5, '[email protected]', '2009-08-01' UNION ALL
SELECT 6, '[email protected]', '2009-08-01' UNION ALL
SELECT 7, '[email protected]', '2009-08-02' UNION ALL
SELECT 8, '[email protected]', '2009-08-02' UNION ALL
SELECT 9, '[email protected]', '2009-08-02' UNION ALL
SELECT 10, '[email protected]', '2009-08-02' UNION ALL
SELECT 11, '[email protected]', '2009-08-02' UNION ALL
SELECT 12, '[email protected]', '2009-08-02'
SELECT email,date,COUNT(*) AS TOTAL
FROM @TB
GROUP BY email,date
HAVING COUNT(*)>3
/*email date TOTAL
--------- ------------------------------------------------------ -----------
[email protected] 2009-08-01 00:00:00.000 4
[email protected] 2009-08-02 00:00:00.000 5
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-04 13:33:12
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[email] varchar(9),[date] datetime)
insert [tb]
select 1,'[email protected]','2009-08-01' union all
select 2,'[email protected]','2009-08-01' union all
select 3,'[email protected]','2009-08-01' union all
select 4,'[email protected]','2009-08-01' union all
select 5,'[email protected]','2009-08-01' union all
select 6,'[email protected]','2009-08-01' union all
select 7,'[email protected]','2009-08-02' union all
select 8,'[email protected]','2009-08-02' union all
select 9,'[email protected]','2009-08-02' union all
select 10,'[email protected]','2009-08-02' union all
select 11,'[email protected]','2009-08-02' union all
select 12,'[email protected]','2009-08-02'
--------------开始查询--------------------------
select
email,
convert(varchar(10),[date],120) as [date],
count(1) as 数量
from
tb
group by
email,
convert(varchar(10),[date],120)
having
count(1)>3
----------------结果----------------------------
/* email date 数量
--------- ---------- -----------
[email protected] 2009-08-01 4
[email protected] 2009-08-02 5(2 行受影响)
*/
CREATE TABLE TEST(ID INT ,EMAIL VARCHAR(20),DATE1 DATETIME)
INSERT INTO TEST
SELECT 1,'[email protected]','2009-08-01' UNION ALL
SELECT 2,'[email protected]','2009-08-01' UNION ALL
SELECT 3,'[email protected]','2009-08-01' UNION ALL
SELECT 4,'[email protected]','2009-08-01' UNION ALL
SELECT 5,'[email protected]','2009-08-01' UNION ALL
SELECT 6,'[email protected]','2009-08-01' UNION ALL
SELECT 7,'[email protected]','2009-08-01' UNION ALL
SELECT 8,'[email protected]','2009-08-02' UNION ALL
SELECT 9,'[email protected]','2009-08-02' UNION ALL
SELECT 10,'[email protected]','2009-08-02' UNION ALL
SELECT 11,'[email protected]','2009-08-02' UNION ALL
SELECT 12,'[email protected]','2009-08-02' GOSELECT EMAIL,DATE1,COUNT(ID) 'TOTAL'
FROM TEST
GROUP BY EMAIL,DATE1 HAVING COUNT(ID)>3
EMAIL DATE1 TOTAL
-------------------- ------------------------------------------------------ -----------
[email protected] 2009-08-01 00:00:00.000 4
[email protected] 2009-08-02 00:00:00.000 5(所影响的行数为 2 行)
(
id int identity(1,1) primary key,
email varchar(30),
date datetime
)
insert into @TB select '[email protected]','2009-08-01'
union all select '[email protected]','2009-08-01'
union all select '[email protected]','2009-08-01'
union all select '[email protected]','2009-08-01'
union all select '[email protected]','2009-08-01'
union all select '[email protected]','2009-08-01'
union all select '[email protected]','2009-08-02'
union all select '[email protected]','2009-08-02'
union all select '[email protected]','2009-08-02'
union all select '[email protected]','2009-08-02'
union all select '[email protected]','2009-08-02'
union all select '[email protected]','2009-08-02'
--(1)
select * from
(
select email,count(email) cnt,date from @TB group by email,date
)TT
where cnt>3
--(2)
;with hgo as
(
select email,count(email) cnt,date from @TB group by email,date
)
--select * from hgo where cnt>3
--(3)
select email,count(email) cnt,date from @TB group by email,date having count(email)>3
/*
email cnt date
------------------------------ ----------- -----------------------
[email protected] 4 2009-08-01 00:00:00.000
[email protected] 5 2009-08-02 00:00:00.000(2 行受影响)
*/
;with hgo as
(
select email,count(email) cnt,date from @TB group by email,date
)
select * from hgo where cnt>3
不小心注释啦
email,
convert(varchar(10),[date],120) as [date],
count(1) as total
from
tb
where
convert(varchar(10),[date],120)='2009-08'
group by
email,
convert(varchar(10),[date],120)
having
count(1)>3
email,
convert(varchar(10),[date],120)
加条件
select
email,
convert(varchar(10),[date],120) as [date],
count(1) as 数量
from
tb
where
datepart(mm,[date])=8
group by
email,
convert(varchar(10),[date],120)
having
count(1)>3
select * from
(
select email,count(email) cnt,date from @TB group by email,date
)TT
where cnt>3 and year(date)=2009 and month(date)=8
email,
convert(varchar(10),[date],120)
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c)
1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86>
(Build 2600: Service Pack 3)
愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-04 13:39:09.500●●●●●
★★★★★soft_wsx★★★★★
*/if object_id('tb') is not null drop table tb
create table tb(id int, email nvarchar(20), date varchar(10))
insert into tb
select
1, '[email protected]', '2009-08-01'
union all select 2, '[email protected]', '2009-08-01'
union all select 3, '[email protected]', '2009-08-01'
union all select 4, '[email protected]', '2009-08-01'
union all select 5, '[email protected]', '2009-08-01'
union all select 6, '[email protected]', '2009-08-01'
union all select 7, '[email protected]', '2009-08-02'
union all select 8, '[email protected]', '2009-08-02'
union all select 9, '[email protected]', '2009-08-02'
union all select 10, '[email protected]', '2009-08-02'
union all select 11, '[email protected]', '2009-08-02'
union all select 12, '[email protected]', '2009-08-02' --select * from tb
/*
id email date
1 [email protected] 2009-08-01
2 [email protected] 2009-08-01
3 [email protected] 2009-08-01
4 [email protected] 2009-08-01
5 [email protected] 2009-08-01
6 [email protected] 2009-08-01
7 [email protected] 2009-08-02
8 [email protected] 2009-08-02
9 [email protected] 2009-08-02
10 [email protected] 2009-08-02
11 [email protected] 2009-08-02
12 [email protected] 2009-08-02
*/select email,date,count(1) as 记录数 from tb group by email,date having(count(1)>3)
/*
email date 记录数
[email protected] 2009-08-01 4
[email protected] 2009-08-02 5
*/刚刚断网了,希望还有机会
(
id int identity(1,1) primary key,
email varchar(30),
date datetime
)
insert into @TB select '[email protected]','2009-08-01'
union all select '[email protected]','2009-08-01'
union all select '[email protected]','2009-08-01'
union all select '[email protected]','2009-08-01'
union all select '[email protected]','2009-08-01'
union all select '[email protected]','2009-08-01'
union all select '[email protected]','2009-08-02'
union all select '[email protected]','2009-08-02'
union all select '[email protected]','2009-08-02'
union all select '[email protected]','2009-08-02'
union all select '[email protected]','2009-08-02'
union all select '[email protected]','2009-08-02';with t as
(
select rn=row_number()over(partition by email order by date),*
from @tb
)
select email,count(1) total from t a
where exists(
select 1 from t
where email=a.email and date=a.date
and rn>3)
group by email
/*
email total
[email protected] 4
[email protected] 5
*/这么热闹,这种方法好像没人用,凑一下.
if object_id('tb') is not null drop table tb
create table tb(id int, email nvarchar(20), date varchar(10))
insert into tb
select
1, '[email protected]', '2009-08-01'
union all select 2, '[email protected]', '2009-08-01'
union all select 3, '[email protected]', '2009-08-01'
union all select 4, '[email protected]', '2009-08-01'
union all select 5, '[email protected]', '2009-08-01'
union all select 6, '[email protected]', '2009-08-01'
union all select 7, '[email protected]', '2009-08-02'
union all select 8, '[email protected]', '2009-08-02'
union all select 9, '[email protected]', '2009-08-02'
union all select 10, '[email protected]', '2009-08-02'
union all select 11, '[email protected]', '2009-08-02'
union all select 12, '[email protected]', '2009-08-02' --select * from tb
/*
id email date
1 [email protected] 2009-08-01
2 [email protected] 2009-08-01
3 [email protected] 2009-08-01
4 [email protected] 2009-08-01
5 [email protected] 2009-08-01
6 [email protected] 2009-08-01
7 [email protected] 2009-08-02
8 [email protected] 2009-08-02
9 [email protected] 2009-08-02
10 [email protected] 2009-08-02
11 [email protected] 2009-08-02
12 [email protected] 2009-08-02
*/select email,date,count(1) as 记录数 from tb group by email,date having(count(1)>3)
/*
email date 记录数
[email protected] 2009-08-01 4
[email protected] 2009-08-02 5
*/
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08 这样的谁能解释下?