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

三个表组合查询显示 求查询SQ语句L
表一：患者信息库【huanzhe】有字段 患者ID，姓名， 住院日期 数据如下

SQL code

HZID   name      ZYDate 1      张三    2012-10-12      张四    2012-10-53      王三    2012-10-94      李五    2012-10-12....

表二：治疗信息表【zhiliao】有字段 医生姓名 科室 患者ID 费用1 费用2 数据如下

SQL code

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]

SQL code

HZID      ZFMoney         CYDate1         100.0000      1900-1-12         100.0000      1900-1-13         100.0000      1900-1-14         100.0000      1900-1-14         100.0000      1900-1-13         100.0000      1900-1-12         100.0000      1900-1-11         100.0000      1900-1-11         100.0000      1900-1-12         100.0000      1900-1-12         100.0000      2012-10-111         100.0000      2012-11-11......

希望通过SQL查询得到如下结果

以患者分组显示数据字段如下

HZID name ZYDate feiyong1【所有治疗费用1相加】 feiyong2【所有治疗费用2相加】 ZFMoney【所有已结算费用1相加】 Qiankuan【欠款=费用1+费用2=已结算总额】 CYDate【取出院日期字段最大值】 ZLCS【治疗次数为治疗信息表里出现的记录行数量】

SQL code

1      张三    2012-10-1     400.0000           800.0000         400.00      800.00       2012-11-11      42      张四    2012-10-5     300.0000           600.0000         400.00      500.00       2012-10-11      33      王三    2012-10-9     300.0000           600.0000         200.00      700.00       1900-1-1        34      李五    2012-10-12    200.0000           400.0000         200.00      400.00        1900-1-1       2

[解决办法]

SQL code

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 行受影响)  */ 
 
[解决办法]
SQL code
if exists(select object_id('a')) drop table acreate table a(HZID int,name varchar(50),ZYDate datetime)insert into a select 1,'张三','2012-10-1' union allselect 2,'张四','2012-10-5' union allselect 3,'王三','2012-10-9' union allselect 4,'李五','2012-10-12'if exists (select object_id('b'))drop table bcreate table b(yisheng varchar(50),keshi varchar(50),HZID int,feiyong1 decimal(20,4),feiyong2 decimal(20,4))insert into bselect '王医生','脑科',1,100.0000,200.0000 union allselect '李医生','骨科',1,100.0000,200.0000 union allselect '张医生','眼科',2,100.0000,200.0000 union allselect '朱医生','外科',3,100.0000,200.0000 union allselect '王医生','内科',1,100.0000,200.0000 union allselect '王医生','皮肤',2,100.0000,200.0000 union allselect '张医生','口腔',4,100.0000,200.0000 union allselect '王医生','脑科',4,100.0000,200.0000 union allselect '陈医生','眼科',2,100.0000,200.0000 union allselect '王医生','脑科',1,100.0000,200.0000 union allselect '张医生','眼科',3,100.0000,200.0000 union allselect '王医生','脑科',3,100.0000,200.0000if exists(select object_id('c'))drop table ccreate table c(HZID int,ZFMoney decimal(20,4),CYDate datetime)insert into cselect 1,100.0000,'1900-1-1' union allselect 2,100.0000,'1900-1-1' union allselect 3,100.0000,'1900-1-1' union allselect 4,100.0000,'1900-1-1' union allselect 4,100.0000,'1900-1-1' union allselect 3,100.0000,'1900-1-1' union allselect 2,100.0000,'1900-1-1' union allselect 1,100.0000,'1900-1-1' union allselect 1,100.0000,'1900-1-1' union allselect 2,100.0000,'1900-1-1' union allselect 2,100.0000,'2012-10-11' union allselect 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.countNumFROM (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.HZIDGROUP 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    42    张四    300.0000    600.0000    400.0000    2012-10-11 00:00:00.000    33    王三    300.0000    600.0000    200.0000    1900-01-01 00:00:00.000    34    李五    200.0000    400.0000    200.0000    1900-01-01 00:00:00.000    2*/--少了治疗次数，特此更正 

 

查看更多 下一篇