问一个SQL查询

Question

create  table A(
yi  int,
yiname varchar(10),
yisex varchar(10),
)
Create  table B(
bi  int,
biname varchar(10),
bisex  varchar(10))
Create  Table C (
ydate  datetime,
ci  int,
yi int)
insert into A  
select 1,'李三','男'  union all 
select 2,'王五','女'  union all
select 3,'超六','男'
insert into B
select 1,'s001','0'  union all
select 2,'d001','0'  union all
select 3,'f001','0'  
insert into C
select '2003/11/30',1,2  union all
select '2003/11/30',2,2  union all
select '2003/11/30',3,2  union all
select '2003/11/30',1,1  union all
select '2003/11/30',2,1  union all
select '2003/11/30',1,3  
---------------------------
select  * from a
select *  from b
select *  from c
select yi=(select yiname from a where yi=d.yi)  from 
(select ydate,yi,qty=count(yi) from c  group by ydate,yi)   d 
where  qty in (select max(qty)  from (select ydate,yi,qty=count(yi) from c  group by ydate,yi) e)

Answer 1

select 医生姓名=(select 医生姓名 from 医生 where 医生.医生编码=d.医生编码)  from 
(select 日期,医生编码,qty=count(医生编码) from 就诊记录 where 日期='2003/11/30'  group by 日期,医生编码)   d 
where  qty in (select max(qty)  from (select 日期,医生编码,qty=count(医生编码) from 就诊记录 where 日期='2003/11/30'  group by 日期,医生编码) e)

Answer 2

create  table A(
yi  int,
yiname varchar(10),
yisex varchar(10),
)
Create  table B(
bi  int,
biname varchar(10),
bisex  varchar(10))
Create  Table C (
ydate  datetime,
ci  int,
yi int)
insert into A  
select 1,'李三','男'  union all 
select 2,'王五','女'  union all
select 3,'超六','男'
insert into B
select 1,'s001','0'  union all
select 2,'d001','0'  union all
select 3,'f001','0'  
insert into C
select '2003/11/30',1,2  union all
select '2003/11/30',2,2  union all
select '2003/11/30',3,2  union all
select '2003/11/30',1,1  union all
select '2003/11/30',2,1  union all
select '2003/11/30',1,3  
---------------------------
select  * from a
select *  from b
select *  from c
select yi=(select yiname from a where yi=d.yi)  from 
(select ydate,yi,qty=count(yi) from c where ydate='2003/11/30' group by ydate,yi)   d 
where  qty in (select max(qty)  from (select ydate,yi,qty=count(yi) from c where ydate='2003/11/30' group by ydate,yi) e)

Answer 3

分3步看得比较清晰：
--drop table #tmp
select 医生.医生编码,医生.所在科室,count(*) as rs  into #tmp from 医生,就诊记录 where 医生.医生编码=就诊记录.医生编码 and datediff(d,就诊记录.日期,'2004-11-30')=0
 group by 医生.医生编码,医生.所在科室
--select * from #tmp
--drop table #tmp1
select max(rs) as rs,所在科室 into #tmp1 from #tmp group by 所在科室
--select * from #tmp1
select 医生.所在科室,医生姓名 from 医生,#tmp,#tmp1 where #tmp1.所在科室=#tmp.所在科室 and #tmp1.rs=#tmp.rs and #tmp.医生编码=医生.医生编码应该能出来，如果有字段错误你再改改！！

Answer 4

分3步看得比较清晰：
--drop table #tmp
select 医生.医生编码,医生.所在科室,count(*) as rs  into #tmp from 医生,就诊记录 where 医生.医生编码=就诊记录.医生编码 and datediff(d,就诊记录.日期,'2004-11-30')=0
 group by 医生.医生编码,医生.所在科室
--select * from #tmp
--drop table #tmp1
select max(rs) as rs,所在科室 into #tmp1 from #tmp group by 所在科室
--select * from #tmp1
select 医生.所在科室,医生姓名 from 医生,#tmp,#tmp1 where #tmp1.所在科室=#tmp.所在科室 and #tmp1.rs=#tmp.rs and #tmp.医生编码=医生.医生编码应该能出来，如果有字段错误你再改改！！

Answer 5

分3步看得比较清晰：
--drop table #tmp
select 医生.医生编码,医生.所在科室,count(*) as rs  into #tmp from 医生,就诊记录 where 医生.医生编码=就诊记录.医生编码 and datediff(d,就诊记录.日期,'2003-11-30')=0
 group by 医生.医生编码,医生.所在科室
--select * from #tmp
--drop table #tmp1
select max(rs) as rs,所在科室 into #tmp1 from #tmp group by 所在科室
--select * from #tmp1
select 医生.所在科室,医生姓名 from 医生,#tmp,#tmp1 where #tmp1.所在科室=#tmp.所在科室 and #tmp1.rs=#tmp.rs and #tmp.医生编码=医生.医生编码应该能出来，如果有字段错误你再改改！！

Answer 6

if  exists(select 1 from sysobjects where id=object_id('醫生') and objectproperty(id,'isTable')=1)
drop table 醫生
if  exists(select 1 from sysobjects where id=object_id('病人') and objectproperty(id,'isTable')=1)
drop table 病人
if  exists(select 1 from sysobjects where id=object_id('就診記錄') and objectproperty(id,'isTable')=1)
drop table 就診記錄go
/*
醫生（醫生編碼，醫生姓名，性别，年齡，所在科室）
病人（病歷號，姓名，性別，年齡，職業）
就診記錄（日期，病歷號，醫生編碼）
*/
create table 醫生(醫生編碼 varchar(10) not null primary key,醫生姓名 varchar(10) null,性別 varchar(2) null,年齡 int null,所在科室 varchar(20) null)
create table 病人(病歷號 varchar(10) not null primary key,姓名 varchar(10) null,性別 varchar(2) null,年齡 int null,職業 varchar(20) null)
create table 就診記錄(日期 datetime  not null ,病歷號 varchar(10) null,醫生編碼 varchar(10) null)
goinsert into 醫生
select '001','李明軍','男',26,'外科' union
select '002','王  軍','男',42,'內科' union
select '003','李麗花','女',21,'婦科' union
select '004','蘇  清','男',25,'外科' insert into 病人
select '0001','張志華','男',15,'學生' union
select '0002','李  強','男',31,'工人' union
select '0003','五一強','男',32,'工人' union
select '0004','張志華','男',15,'學生' union
select '0005','明華強','男',33,'工人' union
select '0006','工人強','男',34,'工人' union
select '0007','張志華','男',15,'學生' union
select '0008','李  四','男',36,'工人' union
select '0009','王  五','男',39,'工人' union
select '0010','鄧麗花','女',28,'干部' insert into 就診記錄
select '2003/01/01','0001','001' union
select '2003/01/08','0002','002' union
select '2003/10/01','0003','001' union
select '2003/11/30','0004','003' union
select '2003/11/30','0005','001' union
select '2003/11/30','0006','002' union
select '2003/11/30','0007','001' union
select '2003/11/30','0008','002' union
select '2003/11/30','0009','001' union
select '2003/12/30','0010','003' goselect * from 醫生
/*
醫生編碼 醫生姓名  性别     年齡     所在科室
001 李明軍 男 26 外科
002 王  軍 男 42 內科
003 李麗花 女 21 婦科
004 蘇  清 男 25 外科
*/select * from 病人
/*
病歷號    姓名    性別      年齡      職業
0001 張志華 男 15 學生
0002 李  強 男 31 工人
0003 五一強 男 32 工人
0004 張志華 男 15 學生
0005 明華強 男 33 工人
0006 工人強 男 34 工人
0007 張志華 男 15 學生
0008 李  四 男 36 工人
0009 王  五 男 39 工人
0010 鄧麗花 女 28 干部
*/select * from 就診記錄
/*
日期                        病歷號   醫生編碼
2003-01-01 00:00:00.000 0001 001
2003-01-08 00:00:00.000 0002 002
2003-10-01 00:00:00.000 0003 001
2003-11-30 00:00:00.000 0004 003
2003-11-30 00:00:00.000 0005 001
2003-11-30 00:00:00.000 0006 002
2003-11-30 00:00:00.000 0007 001
2003-11-30 00:00:00.000 0008 002
2003-11-30 00:00:00.000 0009 001
2003-12-30 00:00:00.000 0010 003
*/
select 醫生姓名 from
(
select 醫生編碼,count(*) sl from 就診記錄 where 日期='2003/11/30' group by 醫生編碼
)a 
join 
(
 select max(sl) sl from
 (
  select 醫生編碼,count(*) sl from 就診記錄 where 日期='2003/11/30' group by 醫生編碼
 )a
)b on a.sl=b.sl 
join 醫生 c on a.醫生編碼=c.醫生編碼
/*
醫生姓名
李明軍
*/

Answer 7

--上面查詢是不分科室,本查詢分科室
if  exists(select 1 from sysobjects where id=object_id('醫生') and objectproperty(id,'isTable')=1)
drop table 醫生
if  exists(select 1 from sysobjects where id=object_id('病人') and objectproperty(id,'isTable')=1)
drop table 病人
if  exists(select 1 from sysobjects where id=object_id('就診記錄') and objectproperty(id,'isTable')=1)
drop table 就診記錄go
/*
醫生（醫生編碼，醫生姓名，性别，年齡，所在科室）
病人（病歷號，姓名，性別，年齡，職業）
就診記錄（日期，病歷號，醫生編碼）
*/
create table 醫生(醫生編碼 varchar(10) not null primary key,醫生姓名 varchar(10) null,性別 varchar(2) null,年齡 int null,所在科室 varchar(20) null)
create table 病人(病歷號 varchar(10) not null primary key,姓名 varchar(10) null,性別 varchar(2) null,年齡 int null,職業 varchar(20) null)
create table 就診記錄(日期 datetime  not null ,病歷號 varchar(10) null,醫生編碼 varchar(10) null)
goinsert into 醫生
select '001','李明軍','男',26,'外科' union
select '002','王  軍','男',42,'內科' union
select '003','李麗花','女',21,'婦科' union
select '004','蘇  清','男',25,'外科' insert into 病人
select '0001','張志華','男',15,'學生' union
select '0002','李  強','男',31,'工人' union
select '0003','五一強','男',32,'工人' union
select '0004','張志華','男',15,'學生' union
select '0005','明華強','男',33,'工人' union
select '0006','工人強','男',34,'工人' union
select '0007','張志華','男',15,'學生' union
select '0008','李  四','男',36,'工人' union
select '0009','王  五','男',39,'工人' union
select '0010','鄧麗花','女',28,'干部' insert into 就診記錄
select '2003/01/01','0001','001' union
select '2003/01/08','0002','002' union
select '2003/10/01','0003','001' union
select '2003/11/30','0004','003' union
select '2003/11/30','0005','001' union
select '2003/11/30','0006','002' union
select '2003/11/30','0007','001' union
select '2003/11/30','0008','004' union
select '2003/11/30','0009','001' union
select '2003/12/30','0010','003' goselect * from 醫生
/*
醫生編碼 醫生姓名  性别     年齡     所在科室
001 李明軍 男 26 外科
002 王  軍 男 42 內科
003 李麗花 女 21 婦科
004 蘇  清 男 25 外科
*/select * from 病人
/*
病歷號    姓名    性別      年齡      職業
0001 張志華 男 15 學生
0002 李  強 男 31 工人
0003 五一強 男 32 工人
0004 張志華 男 15 學生
0005 明華強 男 33 工人
0006 工人強 男 34 工人
0007 張志華 男 15 學生
0008 李  四 男 36 工人
0009 王  五 男 39 工人
0010 鄧麗花 女 28 干部
*/select * from 就診記錄
/*
日期                        病歷號   醫生編碼
2003-01-01 00:00:00.000 0001 001
2003-01-08 00:00:00.000 0002 002
2003-10-01 00:00:00.000 0003 001
2003-11-30 00:00:00.000 0004 003
2003-11-30 00:00:00.000 0005 001
2003-11-30 00:00:00.000 0006 002
2003-11-30 00:00:00.000 0007 001
2003-11-30 00:00:00.000 0008 004
2003-11-30 00:00:00.000 0009 001
2003-12-30 00:00:00.000 0010 003
*/
select 醫生姓名,c.所在科室 from 
(
select a.醫生編碼,所在科室,count(*) sl from 就診記錄 a join 醫生 b on a.醫生編碼=b.醫生編碼 where 日期='2003/11/30' group by a.醫生編碼,所在科室
)a 
join 
(
 select 所在科室,max(sl) sl from
 (
  select a.醫生編碼,所在科室,count(*) sl from 就診記錄 a join 醫生 b on a.醫生編碼=b.醫生編碼 where 日期='2003/11/30' group by a.醫生編碼,所在科室
 )a group by 所在科室
)b on a.所在科室=b.所在科室 and a.sl=b.sl 
join 醫生 c on a.醫生編碼=c.醫生編碼
/*
醫生姓名  所在科室
李麗花 婦科
李明軍 外科
王  軍 內科
*/

Answer 8

select a.日期,a.醫生編碼,count(*)
from 就診記錄 a
group by 日期,醫生編碼
having count(*)
=
(
 select max(c)
   from 
     (
       select count(*) as c
         from 就診記錄
        where 日期 = a.日期
     group by 醫生編碼
     ) T
)
order by a.日期,a.醫生編碼

Answer 9

select a.日期,a.醫生編碼,count(*)
from 就診記錄 a
group by 日期,醫生編碼
having count(*)
=
(
 select max(c)
   from 
     (
       select count(*) as c
         from 就診記錄
        where 日期 = a.日期
     group by 醫生編碼
     ) T
)
order by a.日期,a.醫生編碼select a.日期,a.醫生編碼,count(*)
from 就診記錄 a
group by 日期,醫生編碼
having count(*)
=
(
(select Top 1 (select count(*) from 就診記錄 where 日期 = b.日期 and 醫生編碼 = b.醫生編碼)
                    from 就診記錄 b
                   where 日期 = a.日期 
                order by (select count(*) from 就診記錄 where 日期 = b.日期 and 醫生編碼 = b.醫生編碼) desc)
)order by a.日期,a.醫生編碼-- having where 可以只留其一select a.日期,a.醫生編碼,count(*)
from 就診記錄 a
where (select count(*) from 就診記錄 where 日期 = a.日期 and 醫生編碼 = a.醫生編碼)
=
(select Top 1 (select count(*) from 就診記錄 where 日期 = b.日期 and 醫生編碼 = b.醫生編碼)
                 from 就診記錄 b
                where 日期 = a.日期 
             order by (select count(*)
                         from 就診記錄
                        where 日期 = b.日期 and 醫生編碼 = b.醫生編碼
                       ) desc)
group by a.日期,a.醫生編碼
having count(*)
=
(
 select max(c)
   from 
     (
       select count(*) as c
         from 就診記錄
        where 日期 = a.日期
     group by 醫生編碼
     ) T
)
order by a.日期,a.醫生編碼select a.日期,a.醫生編碼,count(*)
from 就診記錄 a
where (select count(*) from 就診記錄 where 日期 = a.日期 and 醫生編碼 = a.醫生編碼)
=
(
 select max(c)
   from 
     (
       select count(*) as c
         from 就診記錄
        where 日期 = a.日期
     group by 醫生編碼
     ) T
)
group by a.日期,a.醫生編碼
having count(*)
=(select Top 1 (select count(*) from 就診記錄 where 日期 = b.日期 and 醫生編碼 = b.醫生編碼)
                    from 就診記錄 b
                   where 日期 = a.日期 
                order by (select count(*) from 就診記錄 where 日期 = b.日期 and 醫生編碼 = b.醫生編碼) desc)
order by a.日期,a.醫生編碼

Answer 10

select a.医生编码,a.医生姓名,a.所在科室,count(*)
from 医生 A
(
 select distinct　医生编码,病历号 
 from 就诊记录
 WHERE 日期 = 2003年11月30日) B   //相同病历号的只算一个病人
WHERE a.医生编码 = b.医生编码
group by a.医生编码,a.医生姓名,a.所在科室    //避免同名的医生
order by count(*) desc

Answer 11

select top 1 * from (
select 医生编码,人数=count(b.姓名) from 就诊记录 a inner join 病人 b on a.病历号=b.病历号 where a.日期='2003-11-30'
group by 医生编码) as ww order by 人数 DESC

Answer 12

create table 醫生(醫生編碼 varchar(10) not null primary key,醫生姓名 varchar(10) null,性別 varchar(2) null,年齡 int null,所在科室 varchar(20) null)
create table 病人(病歷號 varchar(10) not null primary key,姓名 varchar(10) null,性別 varchar(2) null,年齡 int null,職業 varchar(20) null)
create table 就診記錄(日期 datetime  not null ,病歷號 varchar(10) null,醫生編碼 varchar(10) null)
goinsert into 醫生
select '001','李明軍','男',26,'外科' union
select '002','王  軍','男',42,'內科' union
select '003','李麗花','女',21,'婦科' union
select '004','蘇  清','男',25,'外科' insert into 病人
select '0001','張志華','男',15,'學生' union
select '0002','李  強','男',31,'工人' union
select '0003','五一強','男',32,'工人' union
select '0004','張志華','男',15,'學生' union
select '0005','明華強','男',33,'工人' union
select '0006','工人強','男',34,'工人' union
select '0007','張志華','男',15,'學生' union
select '0008','李  四','男',36,'工人' union
select '0009','王  五','男',39,'工人' union
select '0010','鄧麗花','女',28,'干部' insert into 就診記錄
select '2003/01/01','0001','001' union
select '2003/01/08','0002','002' union
select '2003/10/01','0003','001' union
select '2003/11/30','0004','003' union
select '2003/11/30','0005','001' union
select '2003/11/30','0006','002' union
select '2003/11/30','0007','001' union
select '2003/11/30','0008','002' union
select '2003/11/30','0009','001' union
select '2003/12/30','0010','003' goselect * from 醫生
/*
醫生編碼 醫生姓名  性别     年齡     所在科室
001 李明軍 男 26 外科
002 王  軍 男 42 內科
003 李麗花 女 21 婦科
004 蘇  清 男 25 外科
*/select * from 病人
/*
病歷號    姓名    性別      年齡      職業
0001 張志華 男 15 學生
0002 李  強 男 31 工人
0003 五一強 男 32 工人
0004 張志華 男 15 學生
0005 明華強 男 33 工人
0006 工人強 男 34 工人
0007 張志華 男 15 學生
0008 李  四 男 36 工人
0009 王  五 男 39 工人
0010 鄧麗花 女 28 干部
*/select * from 就診記錄
/*
日期                        病歷號   醫生編碼
2003-01-01 00:00:00.000 0001 001
2003-01-08 00:00:00.000 0002 002
2003-10-01 00:00:00.000 0003 001
2003-11-30 00:00:00.000 0004 003
2003-11-30 00:00:00.000 0005 001
2003-11-30 00:00:00.000 0006 002
2003-11-30 00:00:00.000 0007 001
2003-11-30 00:00:00.000 0008 002
2003-11-30 00:00:00.000 0009 001
2003-12-30 00:00:00.000 0010 003
select a.醫生編碼,b.醫生姓名 from (select top 1 count(醫生編碼) as 最多条数,醫生編碼 from 就診記錄 where convert(char(10),日期,121)='2003-11-30' group by 醫生編碼 order by count(醫生編碼) desc) a left join 醫生 as b on a.醫生編碼=b.醫生編碼

Answer 13

select top 1 a.醫生編碼,b.醫生姓名 count(distict a.病历号) 
from 就診記錄 a 
left join 醫生 as b on a.醫生編碼=b.醫生編碼
where convert(char(10),日期,121)='2003-11-30' group by a.醫生編碼,b.醫生姓名 order by count(distict a.病历号) desc

Answer 14

--所以用 having 或 where 均可
select a.日期,a.醫生編碼,count(*) as count
,
(
 select max(c)
   from 
     (
       select count(*) as c
         from 就診記錄
        where 日期 = a.日期
     group by 醫生編碼
     ) T
) as max1
,
(
(select Top 1 (select count(*) from 就診記錄 where 日期 = b.日期 and 醫生編碼 = b.醫生編碼)
                    from 就診記錄 b
                   where 日期 = a.日期 
                order by (select count(*) from 就診記錄 where 日期 = b.日期 and 醫生編碼 = b.醫生編碼) desc)
) as max2
from 就診記錄 a
group by 日期,醫生編碼
order by a.日期,a.醫生編碼

Answer 15

select T1.医生编码,T1.医生姓名,T1.所在科室,count(*)
from 医生 （T1）
(
 select distinct 病历号 
 from 就诊记录
 WHERE 日期 = 2003年11月30日and T1.医生编码 = 
{select T2.医生编码 
from T2
where T2.病历号=t3.病历号
group by T1.医生编码,T1.医生姓名,T1.所在科室 
order by count(*) desc}