查询问题

fuchunjun 2010-04-23 10:47:01
Pat_Id Dept Fee state Date
10007 工本费 NULL 1.50 0 2010-04-22 10:44:10.000
10008 工本费 NULL 1.50 0 2010-04-22 16:03:40.000
10009 工本费 NULL 1.50 0 2010-04-22 16:04:35.000
10007 妇科 NULL 5.00 0 2010-04-22 17:00:00.000
10007 儿科 NULL 5.00 0 2010-04-23 09:03:00.000
10007 妇科 NULL 5.00 0 2010-04-24 10:30:00.000
10007 内科 NULL 5.00 0 2010-04-22 17:40:00.000
10008 儿科 NULL 5.00 0 2010-04-22 15:05:00.000
10008 内科 NULL 5.00 0 2010-04-23 16:00:00.000
10008 妇科 NULL 5.00 0 2010-04-22 18:30:00.000
查询4月看过3次（含）以上病的病人的详细信息？同一天同一个病人无论挂几次号都算一次，满足条件的应该只有Pat_Id=10007的这个人。
...全文
115 12 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
feegle_develop 2010-04-23
  • 打赏
  • 举报
 回复 


CREATE TABLE T

(

Pat_Id int,

Dept varchar(20),

Fee varchar(20),

states int,

Dates datetime

)

GO



INSERT INTO T

SELECT 10007,'工本费',1.50,0,'2010-04-22 10:44:10.000'

UNION

SELECT 10008,'工本费',1.50,0,'2010-04-22 16:03:40.000'

UNION

SELECT 10009,'工本费',1.50,0,'2010-04-22 16:04:35.000'

UNION

SELECT 10007,'妇科',1.50,0,'2010-04-22 17:00:00.000'

UNION

SELECT 10007,'儿科',5.00,0,'2010-04-23 09:03:00.000'

UNION

SELECT 10007,'妇科',5.00,0,'2010-04-24 10:30:00.000'

UNION

SELECT 10007,'内科',5.00,0,'2010-04-22 17:40:00.000'

UNION

SELECT 10008,'儿科',5.00,0,'2010-04-22 15:05:00.000'

UNION 

SELECT 10008,'内科',5.00,0,'2010-04-23 16:00:00.000'

UNION

SELECT 10008,'妇科',5.00,0,'2010-04-22 18:30:00.000'

GO



SELECT *

FROM T

WHERE T.Pat_Id

IN 

(

SELECT Pat_Id

FROM

(

SELECT Pat_Id,CONVERT(VARCHAR(10),Dates,23) Dates

FROM T

GROUP BY Pat_Id,CONVERT(VARCHAR(10),Dates,23)

) A

GROUP BY Pat_Id

HAVING COUNT(*)>=3

)

GO
东那个升 2010-04-23
  • 打赏
  • 举报
 回复 
IF NOT OBJECT_ID('[tb]') IS NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb]([Pat_Id] INT,[Dept] NVARCHAR(10),[Fee] DECIMAL(18,2),[state] INT,[Date] DATETIME)

INSERT [tb]

SELECT 10007,N'工本费',1.50,0,N'2010-04-22 10:44:10.000' UNION ALL

SELECT 10008,N'工本费',1.50,0,N'2010-04-22 16:03:40.000' UNION ALL

SELECT 10009,N'工本费',1.50,0,N'2010-04-22 16:04:35.000' UNION ALL

SELECT 10007,N'妇科',5.00,0,N'2010-04-22 17:00:00.000' UNION ALL

SELECT 10007,N'儿科',5.00,0,N'2010-04-23 09:03:00.000' UNION ALL

SELECT 10007,N'妇科',5.00,0,N'2010-04-24 10:30:00.000' UNION ALL

SELECT 10007,N'内科',5.00,0,N'2010-04-22 17:40:00.000' UNION ALL

SELECT 10008,N'儿科',5.00,0,N'2010-04-22 15:05:00.000' UNION ALL

SELECT 10008,N'内科',5.00,0,N'2010-04-23 16:00:00.000' UNION ALL

SELECT 10008,N'妇科',5.00,0,N'2010-04-22 18:30:00.000'

GO

--SELECT * FROM [tb]







select  [Pat_Id],count(distinct convert(char(8),[Date],112)) from tb 

where Date>='20100401' and Date<'20100501'

group by Pat_Id

having count(distinct convert(char(8),[Date],112))>=3



     Pat_Id            

----------- -----------

      10007           3



(1 行受影响)
喜-喜 2010-04-23
  • 打赏
  • 举报
 回复 
--------------------SQL Server数据格式化工具-------------------

---------------------------------------------------------------

-- DESIGNER :happycell188(喜喜)

--       QQ :584738179

-- Development Tool :Microsoft Visual C++ 6.0    C Language 

-- FUNCTION :CONVERT DATA TO T-SQL

---------------------------------------------------------------

-- Microsoft SQL Server  2005

-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]

---------------------------------------------------------------

---------------------------------------------------------------



use test

go

if object_id('test.dbo.tb') is not null drop table tb

-- 创建数据表

create table tb

(

Pat_Id int,

Dept char(7),

Fee char(5),

state int,

Date datetime

)

go

--插入测试数据

insert into tb select 10007,'工本费','1.50',0,'2010-04-22 10:44:10.000'

union all select 10008,'工本费','1.50',0,'2010-04-22 16:03:40.000'

union all select 10009,'工本费','1.50',0,'2010-04-22 16:04:35.000'

union all select 10007,'妇科','5.00',0,'2010-04-22 17:00:00.000'

union all select 10007,'儿科','5.00',0,'2010-04-23 09:03:00.000'

union all select 10007,'妇科','5.00',0,'2010-04-24 10:30:00.000'

union all select 10007,'内科','5.00',0,'2010-04-22 17:40:00.000'

union all select 10008,'儿科','5.00',0,'2010-04-22 15:05:00.000'

union all select 10008,'内科','5.00',0,'2010-04-23 16:00:00.000'

union all select 10008,'妇科','5.00',0,'2010-04-22 18:30:00.000'

go

--代码实现



select pat_id from (select distinct pat_id,date=convert(varchar(10),date,23) from tb)t

where month(date)='04'

group by pat_id

having count(date)>=3



/*测试结果



Pat_Id

---------------------

10007



(1 行受影响)

*/
dawugui 2010-04-23
  • 打赏
  • 举报
 回复 
CREATE TABLE [tb]([Pat_Id] INT,[Dept] NVARCHAR(10),[Fee] DECIMAL(18,2),[state] INT,[Date] DATETIME)

INSERT [tb]

SELECT 10007,N'工本费',1.50,0,N'2010-04-22 10:44:10.000' UNION ALL

SELECT 10008,N'工本费',1.50,0,N'2010-04-22 16:03:40.000' UNION ALL

SELECT 10009,N'工本费',1.50,0,N'2010-04-22 16:04:35.000' UNION ALL

SELECT 10007,N'妇科',5.00,0,N'2010-04-22 17:00:00.000' UNION ALL

SELECT 10007,N'儿科',5.00,0,N'2010-04-23 09:03:00.000' UNION ALL

SELECT 10007,N'妇科',5.00,0,N'2010-04-24 10:30:00.000' UNION ALL

SELECT 10007,N'内科',5.00,0,N'2010-04-22 17:40:00.000' UNION ALL

SELECT 10008,N'儿科',5.00,0,N'2010-04-22 15:05:00.000' UNION ALL

SELECT 10008,N'内科',5.00,0,N'2010-04-23 16:00:00.000' UNION ALL

SELECT 10008,N'妇科',5.00,0,N'2010-04-22 18:30:00.000'

GO



select m.* from tb m where convert(varchar(7),Date,120) = '2010-04' and exists(select 1 from (select pat_id , convert(varchar(7),Date,120) date from tb where convert(varchar(7),Date,120) = '2010-04' group by pat_id , convert(varchar(7),Date,120) having count(distinct convert(varchar(10),Date,120)) >= 3) n where Pat_Id = m.Pat_Id)



drop table tb



/*

Pat_Id      Dept       Fee                  state       Date                                                   

----------- ---------- -------------------- ----------- ------------------------------------------------------ 

10007       工本费        1.50                 0           2010-04-22 10:44:10.000

10007       妇科         5.00                 0           2010-04-22 17:00:00.000

10007       儿科         5.00                 0           2010-04-23 09:03:00.000

10007       妇科         5.00                 0           2010-04-24 10:30:00.000

10007       内科         5.00                 0           2010-04-22 17:40:00.000



（所影响的行数为 5 行）

*/
--小F-- 2010-04-23
  • 打赏
  • 举报
 回复 
----------------------------------------------------------------

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2010-04-23 10:48:50

-- Verstion:

--      Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) 

--	May 26 2009 14:24:20 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([Pat_Id] int,[Dept] varchar(6),[Fee] sql_variant,[state] numeric(3,2),[Date] int,[C6] datetime)

insert [tb]

select 10007,'工本费',null,1.50,0,'2010-04-22 10:44:10.000' union all

select 10008,'工本费',null,1.50,0,'2010-04-22 16:03:40.000' union all

select 10009,'工本费',null,1.50,0,'2010-04-22 16:04:35.000' union all

select 10007,'妇科',null,5.00,0,'2010-04-22 17:00:00.000' union all

select 10007,'儿科',null,5.00,0,'2010-04-23 09:03:00.000' union all

select 10007,'妇科',null,5.00,0,'2010-04-24 10:30:00.000' union all

select 10007,'内科',null,5.00,0,'2010-04-22 17:40:00.000' union all

select 10008,'儿科',null,5.00,0,'2010-04-22 15:05:00.000' union all

select 10008,'内科',null,5.00,0,'2010-04-23 16:00:00.000' union all

select 10008,'妇科',null,5.00,0,'2010-04-22 18:30:00.000'

--------------开始查询--------------------------

select

 Pat_Id 

from

 (select

    Pat_Id,count(distinct convert(varchar(10),c6,120)) as num 

  from

    tb 

  where

   convert(varchar(7),[c6],120)='2010-04'

  group by

    Pat_Id having count(distinct convert(varchar(10),c6,120))>=3)t



----------------结果----------------------------

/* Pat_Id

-----------

10007



(1 行受影响)



*/
sgtzzc 2010-04-23
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 sgtzzc 的回复:]

SQL code
select *
from tb t
where (
select count(distinct convert(varchar(10),[date],120))
from tb where pat_id=t.pat_id
and convert(varchar(7),[date],120)='2010-04'
)>=3
where convert(varcha……
[/Quote]
修正
IF NOT OBJECT_ID('[tb]') IS NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb]([Pat_Id] INT,[Dept] NVARCHAR(10),[Fee] DECIMAL(18,2),[state] INT,[Date] DATETIME)

INSERT [tb]

SELECT 10007,N'工本费',1.50,0,N'2010-04-22 10:44:10.000' UNION ALL

SELECT 10008,N'工本费',1.50,0,N'2010-04-22 16:03:40.000' UNION ALL

SELECT 10009,N'工本费',1.50,0,N'2010-04-22 16:04:35.000' UNION ALL

SELECT 10007,N'妇科',5.00,0,N'2010-04-22 17:00:00.000' UNION ALL

SELECT 10007,N'儿科',5.00,0,N'2010-04-23 09:03:00.000' UNION ALL

SELECT 10007,N'妇科',5.00,0,N'2010-04-24 10:30:00.000' UNION ALL

SELECT 10007,N'内科',5.00,0,N'2010-04-22 17:40:00.000' UNION ALL

SELECT 10008,N'儿科',5.00,0,N'2010-04-22 15:05:00.000' UNION ALL

SELECT 10008,N'内科',5.00,0,N'2010-04-23 16:00:00.000' UNION ALL

SELECT 10008,N'妇科',5.00,0,N'2010-04-22 18:30:00.000'

GO



select *

from tb t

where (

  select count(distinct convert(varchar(10),[date],120)) 

  from tb where pat_id=t.pat_id

  and convert(varchar(7),[date],120)='2010-04'

)>=3

and convert(varchar(7),[date],120)='2010-04'



/**

Pat_Id      Dept       Fee                                     state       Date

----------- ---------- --------------------------------------- ----------- -----------------------

10007       工本费        1.50                                    0           2010-04-22 10:44:10.000

10007       妇科         5.00                                    0           2010-04-22 17:00:00.000

10007       儿科         5.00                                    0           2010-04-23 09:03:00.000

10007       妇科         5.00                                    0           2010-04-24 10:30:00.000

10007       内科         5.00                                    0           2010-04-22 17:40:00.000



(5 行受影响)

**/
东那个升 2010-04-23
  • 打赏
  • 举报
 回复 


select Pat_Id from tb 

where Date>'20100401' and Date<'20100501'

group by Pat_Id

having count(distinct convert(char(8),getdate(),112))>=3
dawugui 2010-04-23
  • 打赏
  • 举报
 回复
更改一下:
select m.* from tb m where convert(varchar(7),Date,120) = '2010-04' and exists(select 1 from (select pat_id , convert(varchar(7),Date,120) date from tb where convert(varchar(7),Date,120) = '2010-04' group by pat_id , convert(varchar(7),Date,120) having count(1) >= 3) n where Pat_Id = m.Pat_Id)
htl258_Tony 2010-04-23
  • 打赏
  • 举报
 回复 
--------------------------------------------------------------------------

--  Author : htl258(Tony)

--  Date   : 2010-04-23 10:50:43

--  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)

--  Blog   : http://blog.csdn.net/htl258

--------------------------------------------------------------------------

--> 生成测试数据表:tb



IF NOT OBJECT_ID('[tb]') IS NULL

	DROP TABLE [tb]

GO

CREATE TABLE [tb]([Pat_Id] INT,[Dept] NVARCHAR(10),[Fee] DECIMAL(18,2),[state] INT,[Date] DATETIME)

INSERT [tb]

SELECT 10007,N'工本费',1.50,0,N'2010-04-22 10:44:10.000' UNION ALL

SELECT 10008,N'工本费',1.50,0,N'2010-04-22 16:03:40.000' UNION ALL

SELECT 10009,N'工本费',1.50,0,N'2010-04-22 16:04:35.000' UNION ALL

SELECT 10007,N'妇科',5.00,0,N'2010-04-22 17:00:00.000' UNION ALL

SELECT 10007,N'儿科',5.00,0,N'2010-04-23 09:03:00.000' UNION ALL

SELECT 10007,N'妇科',5.00,0,N'2010-04-24 10:30:00.000' UNION ALL

SELECT 10007,N'内科',5.00,0,N'2010-04-22 17:40:00.000' UNION ALL

SELECT 10008,N'儿科',5.00,0,N'2010-04-22 15:05:00.000' UNION ALL

SELECT 10008,N'内科',5.00,0,N'2010-04-23 16:00:00.000' UNION ALL

SELECT 10008,N'妇科',5.00,0,N'2010-04-22 18:30:00.000'

GO

--SELECT * FROM [tb]



-->SQL查询如下:

select distinct Pat_Id from tb t where exists(select 1 from tb where convert(char(7),date,23)='2010-04' and Pat_Id=t.Pat_Id having count(distinct convert(char,date,23))>=3) and convert(char(7),date,23)='2010-04'

/*

Pat_Id

-----------

10007



(1 行受影响)

*/
dawugui 2010-04-23
  • 打赏
  • 举报
 回复 
select m.* from tb m where convert(varchar(7),Date,120) = '2010-04' and exists(select 1 from (select pat_id , convert(varchar(10),Date,120) date from tb where convert(varchar(7),Date,120) = '2010-04' group by pat_id , convert(varchar(10),Date,120) having count(1) >= 3) n where Pat_Id = m.Pat_Id)
sgtzzc 2010-04-23
  • 打赏
  • 举报
 回复 
select *

from tb t

where (

  select count(distinct convert(varchar(10),[date],120)) 

  from tb where pat_id=t.pat_id

  and convert(varchar(7),[date],120)='2010-04'

)>=3

where convert(varchar(7),[date],120)='2010-04'
htl258_Tony 2010-04-23
  • 打赏
  • 举报
 回复 
select * from tb t where exists(select 1 from tb where convert(char(7),date,23)='2020-04' and Pat_Id=t.Pat_Id having count(1)>=3) and convert(char(7),date,23)='2020-04'
iphone IMEI查询完整ID
在下载或使用任何工具时，确保先进行病毒扫描是很重要的，因为不安全的工具可能会导致个人信息泄露或其他安全问题。 在实际操作中，要谨慎处理这样的查询方法，因为直接获取他人的Apple ID信息可能违反隐私法规，...
数据库开发 维护 性能调优 备份还原 各种复杂问题的解决方案
数据库开发、维护、性能调优、备份还原以及解决复杂问题，这些都是数据库管理中不可或缺的关键环节。在IT领域，数据库作为存储和管理数据的核心系统，其高效稳定运行对于业务连续性和数据安全性至关重要。以下是对...
Android通讯录管理（获取联系人、通话记录、短信消息）
在实现这些功能时，需要注意兼容性问题，因为Android系统版本间API可能存在差异。此外，为了用户体验，应适当处理异步加载和权限请求，避免阻塞主线程。在`Contact_Demo`这个示例项目中，你应该能看到具体实现这些...
【C#】12306抢票的基础实现
在此，将【源码】交出，提供给感兴趣的朋友，继续去完善，此源码还存在很多问题，还有很多需要优化与功能需要完善的地方；包括UI部分很多地方还没有优化和完善，更别说功能，此源码只实现了，基本的登录，查询，订票...
应用实例

27,582

社区成员

68,544

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章