表table
Id Dept Fee Date
1006 儿科 6.50 2010-03-12 00:00:00.000
1006 儿科 6.50 2010-03-14 00:00:00.000
1006 儿科 6.50 2010-03-07 00:00:00.000
1006 儿科 6.50 2010-03-06 00:00:00.000
1006 儿科 6.50 2010-03-06 03:00:00.000
1006 儿科 6.50 2010-02-07 00:00:00.000
1006 儿科 6.50 2010-01-03 00:00:00.000
1006 儿科 6.50 2010-04-01 00:00:00.000
1006 儿科 6.50 2009-12-09 00:00:00.000
1007 儿科 6.50 2010-02-08 00:00:00.000
1007 儿科 6.50 2010-02-05 00:00:00.000
1007 儿科 6.50 2010-02-09 00:00:00.000
以下数据省略
问题:查询1个月内看过3次以上病的病人记录?同一天内不管一个病人挂过几次号都算1次,如果要查3月份的该怎么写??
Id Dept Fee Date
1006 儿科 6.50 2010-03-12 00:00:00.000
1006 儿科 6.50 2010-03-14 00:00:00.000
1006 儿科 6.50 2010-03-07 00:00:00.000
1006 儿科 6.50 2010-03-06 00:00:00.000
1006 儿科 6.50 2010-03-06 03:00:00.000
1006 儿科 6.50 2010-02-07 00:00:00.000
1006 儿科 6.50 2010-01-03 00:00:00.000
1006 儿科 6.50 2010-04-01 00:00:00.000
1006 儿科 6.50 2009-12-09 00:00:00.000
1007 儿科 6.50 2010-02-08 00:00:00.000
1007 儿科 6.50 2010-02-05 00:00:00.000
1007 儿科 6.50 2010-02-09 00:00:00.000
以下数据省略
问题:查询1个月内看过3次以上病的病人记录?同一天内不管一个病人挂过几次号都算1次,如果要查3月份的该怎么写??
from tb
where convert(varchar(7),[Date],120)='2010-03'
group by id,convert(varchar(10),[Date],120)
having count(distinct convert(varchar(10),[Date],120))>3
select Id ,convert(varchar(10),date,121) from tb where Date between '2010-3-1' and '2010-3-31'
group by id,convert(varchar(10),date,121)
) group by id having count(id)>3
select Id ,convert(varchar(10),date,121) from tb where Date between '2010-3-1' and '2010-3-31'
group by id,convert(varchar(10),date,121)
) t group by id having count(id)>3
-- Author : htl258(Tony)
-- Date : 2010-04-19 14:08:09
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([Id] INT,[Dept] NVARCHAR(10),[Fee] DECIMAL(18,2),[Date] NVARCHAR(30))
INSERT [tb]
SELECT 1006,N'儿科',6.50,N'2010-03-12' UNION ALL
SELECT 1006,N'儿科',6.50,N'2010-03-14' UNION ALL
SELECT 1006,N'儿科',6.50,N'2010-03-07' UNION ALL
SELECT 1006,N'儿科',6.50,N'2010-03-06' UNION ALL
SELECT 1006,N'儿科',6.50,N'2010-03-06·03:00:00.000' UNION ALL
SELECT 1006,N'儿科',6.50,N'2010-02-07' UNION ALL
SELECT 1006,N'儿科',6.50,N'2010-01-03' UNION ALL
SELECT 1006,N'儿科',6.50,N'2010-04-01' UNION ALL
SELECT 1006,N'儿科',6.50,N'2009-12-09' UNION ALL
SELECT 1007,N'儿科',6.50,N'2010-02-08' UNION ALL
SELECT 1007,N'儿科',6.50,N'2010-02-05' UNION ALL
SELECT 1007,N'儿科',6.50,N'2010-02-09'
GO
--SELECT * FROM [tb]-->SQL查询如下:
select distinct ID
from tb
where CONVERT(char(7),date,23)='2010-03'
group by id
having count(1)>=3 --要大于三次就把等号去掉
/*
ID
-----------
1006(1 行受影响)*/
ID
from
tb
where
convert(char(7),date,23)='2010-03'
group by
id
having
count(distinct id)>=3
(SELECT id FROM (SELECT DISTINCT id,convert(varchar(6),Date,112) d FROM [Table]) a
WHERE d='201004'
GROUP BY id HAVING count(id)>3)
DROP TABLE [tb]
GO
CREATE TABLE [tb]([Id] INT,[Dept] NVARCHAR(10),[Fee] DECIMAL(18,2),[Date] NVARCHAR(30))
INSERT [tb]
SELECT 1006,N'儿科',6.50,N'2010-03-12' UNION ALL
SELECT 1006,N'儿科',6.50,N'2010-03-14' UNION ALL
SELECT 1006,N'儿科',6.50,N'2010-03-07' UNION ALL
SELECT 1006,N'儿科',6.50,N'2010-03-06' UNION ALL
SELECT 1006,N'儿科',6.50,N'2010-03-06·03:00:00.000' UNION ALL
SELECT 1006,N'儿科',6.50,N'2010-02-07' UNION ALL
SELECT 1006,N'儿科',6.50,N'2010-01-03' UNION ALL
SELECT 1006,N'儿科',6.50,N'2010-04-01' UNION ALL
SELECT 1006,N'儿科',6.50,N'2009-12-09' UNION ALL
SELECT 1007,N'儿科',6.50,N'2010-02-08' UNION ALL
SELECT 1007,N'儿科',6.50,N'2010-02-05' UNION ALL
SELECT 1007,N'儿科',6.50,N'2010-02-09'
GO
select ID,count(distinct convert(varchar(10),[Date],120)) n
from tb
where convert(varchar(7),[Date],120)='2010-03'
group by id
having count(distinct convert(varchar(10),[Date],120))>3ID n
----------- -----------
1006 4(所影响的行数为 1 行)
(SELECT id FROM (SELECT DISTINCT id,date FROM [Table]) a
WHERE convert(varchar(6),Date,112)='201003'
GROUP BY id HAVING count(id)>3)