三个表组合查询显示 求查询SQ语句L

Question

表一：患者信息库【huanzhe】有字段   患者ID，姓名， 住院日期  数据如下HZID   name      ZYDate 
1      张三    2012-10-1
2      张四    2012-10-5
3      王三    2012-10-9
4      李五    2012-10-12
....表二：治疗信息表【zhiliao】有字段   医生姓名 科室  患者ID  费用1   费用2 数据如下yisheng  keshi   HZID  feiyong1      feiyong2
王医生    脑科    1     100.0000     200.0000
李医生    骨科    1     100.0000     200.0000
张医生    眼科    2     100.0000     200.0000
朱医生    外科    3     100.0000     200.0000
王医生    内科    1     100.0000     200.0000
王医生    皮肤    2     100.0000     200.0000
张医生    口腔    4     100.0000     200.0000
王医生    脑科    4     100.0000     200.0000
陈医生    眼科    2     100.0000     200.0000
王医生    脑科    1     100.0000     200.0000
张医生    眼科    3     100.0000     200.0000
王医生    脑科    3     100.0000     200.0000
......
表三：费用结算表【jiesuan】有字段   患者ID 支付费用  出院日期[默认值是1900-1-1]HZID      ZFMoney         CYDate
1         100.0000      1900-1-1
2         100.0000      1900-1-1
3         100.0000      1900-1-1
4         100.0000      1900-1-1
4         100.0000      1900-1-1
3         100.0000      1900-1-1
2         100.0000      1900-1-1
1         100.0000      1900-1-1
1         100.0000      1900-1-1
2         100.0000      1900-1-1
2         100.0000      2012-10-11
1         100.0000      2012-11-11
......
希望通过SQL查询得到如下结果以患者分组显示数据字段如下HZID   name      ZYDate      feiyong1【所有治疗费用1相加】      feiyong2【所有治疗费用2相加】     ZFMoney【所有已结算费用1相加】  Qiankuan【欠款=费用1+费用2=已结算总额】      CYDate【取出院日期字段最大值】  ZLCS【治疗次数为治疗信息表里出现的记录行数量】
1      张三    2012-10-1     400.0000           800.0000         400.00      800.00       2012-11-11      4
2      张四    2012-10-5     300.0000           600.0000         400.00      500.00       2012-10-11      3
3      王三    2012-10-9     300.0000           600.0000         200.00      700.00       1900-1-1        3
4      李五    2012-10-12    200.0000           400.0000         200.00      400.00        1900-1-1       2

Answer 1

CREATE TABLE huanzhe (HZID INT,  NAME VARCHAR(10),      ZYDate DATETIME)
 INSERT INTO huanzhe
 SELECT 1,'张三','2012-10-1'
 UNION ALL 
 SELECT 2,'张四','2012-10-5'
 UNION ALL 
 SELECT 3,'王三','2012-10-9'
 UNION ALL 
 SELECT 4,'李五','2012-10-12'
 GO
 
 CREATE TABLE zhiliao(yisheng VARCHAR(10),  keshi VARCHAR(10),   HZID INT ,  feiyong1  decimal(18,2),    feiyong2 decimal(18,2))
 INSERT INTO zhiliao
 select '王医生',  '脑科','1', '100.0000', '200.0000'
 union all select '李医生','骨科','1', '100.0000', '200.0000'
 union all select '张医生','眼科','2', '100.0000', '200.0000'
 union all select '朱医生','外科','3', '100.0000', '200.0000'
 union all select '王医生','内科','1', '100.0000', '200.0000'
 union all select '王医生','皮肤','2', '100.0000', '200.0000'
 union all select '张医生','口腔','4', '100.0000', '200.0000'
 union all select '王医生','脑科','4', '100.0000', '200.0000'
 union all select '陈医生','眼科','2', '100.0000', '200.0000'
 union all select '王医生','脑科','1', '100.0000', '200.0000'
 union all select '张医生','眼科','3', '100.0000', '200.0000'
 union all select '王医生','脑科','3', '100.0000', '200.0000'
 GO
 CREATE TABLE jiesuan(HZID  INT,    ZFMoney  DECIMAL(18,2),       CYDate DATETIME)
 INSERT INTO jiesuan
 select '1',     '100.0000',   '1900-1-1'
 union all select '2',     '100.0000',   '1900-1-1'
 union all select '3',     '100.0000',   '1900-1-1'
 union all select '4',     '100.0000',   '1900-1-1'
 union all select '4',     '100.0000',   '1900-1-1'
 union all select '3',     '100.0000',   '1900-1-1'
 union all select '2',     '100.0000',   '1900-1-1'
 union all select '1',     '100.0000',   '1900-1-1'
 union all select '1',     '100.0000',   '1900-1-1'
 union all select '2',     '100.0000',   '1900-1-1'
 union all select '2',     '100.0000',   '2012-10-11'
 union all select '1',     '100.0000',   '2012-11-11'
 
 SELECT a.hzid,a.name,a.zydate,a.feiyong1,a.feiyong2,b.zfmoney zfmoney,a.feiyong1+a.feiyong2-b.zfmoney qiankuan,CONVERT(DATE,cydate)cydate,a.zlcs
 FROM 
 (SELECT a.hzid,a.name,CONVERT(DATE,a.zydate)zydate,SUM(feiyong1) feiyong1,SUM(feiyong2)feiyong2,COUNT(1) ZLCS
 FROM huanzhe a INNER  JOIN zhiliao b ON a.HZID=b.HZID
 GROUP BY a.hzid,a.name,a.zydate)a 
 INNER JOIN (SELECT hzid,SUM(zfmoney)zfmoney,MAX(cydate)cydate FROM  jiesuan GROUP BY hzid) b ON a.hzid=b.hzid
 
 /*
 hzid        name       zydate     feiyong1                                feiyong2                                zfmoney                                 qiankuan                                cydate     zlcs
 ----------- ---------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------- -----------
 1           张三         2012-10-01 400.00                                  800.00                                  400.00                                  800.00                                  2012-11-11 4
 2           张四         2012-10-05 300.00                                  600.00                                  400.00                                  500.00                                  2012-10-11 3
 3           王三         2012-10-09 300.00                                  600.00                                  200.00                                  700.00                                  1900-01-01 3
 4           李五         2012-10-12 200.00                                  400.00                                  200.00                                  400.00                                  1900-01-01 2
 
 (4 行受影响)
 
 */
 
 

Answer 2

if exists(select object_id('a')) drop table a
create table a
(
HZID int,
name varchar(50),
ZYDate datetime
)insert into a 
select 1,'张三','2012-10-1' union all
select 2,'张四','2012-10-5' union all
select 3,'王三','2012-10-9' union all
select 4,'李五','2012-10-12'if exists (select object_id('b'))drop table b
create table b
(
yisheng varchar(50),
keshi varchar(50),
HZID int,
feiyong1 decimal(20,4),
feiyong2 decimal(20,4)
)
insert into b
select '王医生','脑科',1,100.0000,200.0000 union all
select '李医生','骨科',1,100.0000,200.0000 union all
select '张医生','眼科',2,100.0000,200.0000 union all
select '朱医生','外科',3,100.0000,200.0000 union all
select '王医生','内科',1,100.0000,200.0000 union all
select '王医生','皮肤',2,100.0000,200.0000 union all
select '张医生','口腔',4,100.0000,200.0000 union all
select '王医生','脑科',4,100.0000,200.0000 union all
select '陈医生','眼科',2,100.0000,200.0000 union all
select '王医生','脑科',1,100.0000,200.0000 union all
select '张医生','眼科',3,100.0000,200.0000 union all
select '王医生','脑科',3,100.0000,200.0000
if exists(select object_id('c'))drop table c
create table c
(
HZID int
,ZFMoney decimal(20,4)
,CYDate datetime
)
insert into c
select 1,100.0000,'1900-1-1' union all
select 2,100.0000,'1900-1-1' union all
select 3,100.0000,'1900-1-1' union all
select 4,100.0000,'1900-1-1' union all
select 4,100.0000,'1900-1-1' union all
select 3,100.0000,'1900-1-1' union all
select 2,100.0000,'1900-1-1' union all
select 1,100.0000,'1900-1-1' union all
select 1,100.0000,'1900-1-1' union all
select 2,100.0000,'1900-1-1' union all
select 2,100.0000,'2012-10-11' union all
select 1,100.0000,'2012-11-11'select * from a inner join b on b.HZID=a.HZID
       inner join c on c.HZID=a.HZID
select * from b
select * from cselect a.HZID,a.name,a.feiyong1,a.feiyong2,sum(c.ZFmoney) as ZFmoney,max(CYdate)as CYDate from (
select a.HZID,a.name,sum(b.feiyong1) as feiyong1,sum(b.feiyong2)as feiyong2
from a inner join b on b.HZID=a.HZID
group by a.HZID,a.name) as a inner join c on a.HZID = c.HZID
group by a.HZID,a.name,a.feiyong1,a.feiyong2
---------------------------------------------------------
/*
1 张三 400.0000 800.0000 400.0000 2012-11-11 00:00:00.000
2 张四 300.0000 600.0000 400.0000 2012-10-11 00:00:00.000
3 王三 300.0000 600.0000 200.0000 1900-01-01 00:00:00.000
4 李五 200.0000 400.0000 200.0000 1900-01-01 00:00:00.000*/

Answer 3

[code=SQL][/SELECT t1.hzid ,hz.NAME,hz.ZYDate,t1.f1,t1.f2,t2.zfm, (t1.f1+t1.f2),(t1.f1+t1.f2-t2.zfm),t2.cydate,t1.counts FROM 
(SELECT hzid hzid, SUM(feiyong1) f1,SUM(feiyong2) f2,COUNT(*) counts FROM zhiliao  GROUP BY hzid) t1 LEFT JOIN 
(SELECT js.hzid hzid, SUM(js.ZFMoney) zfm ,MAX(js.CYDate) cydate FROM jiesuan js GROUP BY hzid) t2 ON t1.hzid = t2.hzid 
LEFT JOIN huanzhe hz ON hz.HZID = t1.hzid ;]
结果：
1 张三 2012-10-01 00:00:00 400.00 800.00 400.00 1200.00 800.00 2012-11-11 00:00:00 4
2 张四 2012-10-05 00:00:00 300.00 600.00 400.00 900.00  500.00 2012-10-11 00:00:00 3
3 王三 2012-10-09 00:00:00 300.00 600.00 200.00 900.00  700.00 1900-01-01 00:00:00 3
4 李五 2012-10-10 00:00:00 200.00 400.00 200.00 600.00  400.00 1900-01-01 00:00:00 2

Answer 4

if exists(select object_id('a')) drop table a
create table a
(
HZID int,
name varchar(50),
ZYDate datetime
)insert into a 
select 1,'张三','2012-10-1' union all
select 2,'张四','2012-10-5' union all
select 3,'王三','2012-10-9' union all
select 4,'李五','2012-10-12'if exists (select object_id('b'))drop table b
create table b
(
yisheng varchar(50),
keshi varchar(50),
HZID int,
feiyong1 decimal(20,4),
feiyong2 decimal(20,4)
)
insert into b
select '王医生','脑科',1,100.0000,200.0000 union all
select '李医生','骨科',1,100.0000,200.0000 union all
select '张医生','眼科',2,100.0000,200.0000 union all
select '朱医生','外科',3,100.0000,200.0000 union all
select '王医生','内科',1,100.0000,200.0000 union all
select '王医生','皮肤',2,100.0000,200.0000 union all
select '张医生','口腔',4,100.0000,200.0000 union all
select '王医生','脑科',4,100.0000,200.0000 union all
select '陈医生','眼科',2,100.0000,200.0000 union all
select '王医生','脑科',1,100.0000,200.0000 union all
select '张医生','眼科',3,100.0000,200.0000 union all
select '王医生','脑科',3,100.0000,200.0000
if exists(select object_id('c'))drop table c
create table c
(
HZID int
,ZFMoney decimal(20,4)
,CYDate datetime
)
insert into c
select 1,100.0000,'1900-1-1' union all
select 2,100.0000,'1900-1-1' union all
select 3,100.0000,'1900-1-1' union all
select 4,100.0000,'1900-1-1' union all
select 4,100.0000,'1900-1-1' union all
select 3,100.0000,'1900-1-1' union all
select 2,100.0000,'1900-1-1' union all
select 1,100.0000,'1900-1-1' union all
select 1,100.0000,'1900-1-1' union all
select 2,100.0000,'1900-1-1' union all
select 2,100.0000,'2012-10-11' union all
select 1,100.0000,'2012-11-11'
SELECT a.HZID, a.name, a.feiyong1, a.feiyong2, SUM(c.ZFmoney) AS ZFmoney, 
      MAX(CYdate) AS CYDate, a.countNum
FROM (SELECT a.HZID, a.name, SUM(b.feiyong1) AS feiyong1, SUM(b.feiyong2) 
              AS feiyong2, COUNT(*) AS countNum
        FROM a INNER JOIN
              b ON b.HZID = a.HZID
        GROUP BY a.HZID, a.name) a INNER JOIN
      c ON a.HZID = c.HZID
GROUP BY a.HZID, a.name, a.feiyong1, a.feiyong2, a.countNum
---------------------------------------------------------
/*
1 张三 400.0000 800.0000 400.0000 2012-11-11 00:00:00.000 4
2 张四 300.0000 600.0000 400.0000 2012-10-11 00:00:00.000 3
3 王三 300.0000 600.0000 200.0000 1900-01-01 00:00:00.000 3
4 李五 200.0000 400.0000 200.0000 1900-01-01 00:00:00.000 2*/
--少了治疗次数，特此更正

Answer 5

select HZ.*, ZL.*,JS.*
from huanzhe HZ inner join
               (select HZID,sum(feiyong1), sum(feiyong2) 
                from zhiliao
                group by HZID) ZL on HZ.HZID = ZL.HZID
                INNER JOIN
                (select HZID,sum(ZFMoney),MAX(CY_DATE),COUNT(*)
                 from JIESUAN
                group by HZID)JS ON ZL.HZID = JS.ID