select t1.dept_name,t1.user_name,t2.PATIENTCOUNT,t3.RECORDCOUNT from
((select b.dept_name, a.user_name, a.user_id from users a,
dept_dict b where a.user_dept=b.dept_code) t1 inner join
(select c.request_doctor_id, count(c.patient_id) as PATIENTCOUNT from mr_on_line
c where c.request_date_time >= CONVERT(varchar(100),'2012-01-04',23)
and c.create_date_time <= CONVERT(varchar(100),'2013-01-08',23)
group by c.request_doctor_id order by c.request_doctor_id) t2
on t1.user_name=t2.doctor_in_charge inner join (select d.creator_id,
count( distinct(d.creator_name)) AS RECORDCOUNT from
mr_file_index d where d.create_date_time >=
CONVERT(varchar(100),'2012-01-04',23) and d.create_date_time <=
CONVERT(varchar(100),'2013-01-08',23) and d.file_flag in
('1','2','3','9') GROUP BY d.creator_id order by d.creator_id)
t3 on t1.user_id=t3.creator_id )sql
((select b.dept_name, a.user_name, a.user_id from users a,
dept_dict b where a.user_dept=b.dept_code) t1 inner join
(select c.request_doctor_id, count(c.patient_id) as PATIENTCOUNT from mr_on_line
c where c.request_date_time >= CONVERT(varchar(100),'2012-01-04',23)
and c.create_date_time <= CONVERT(varchar(100),'2013-01-08',23)
group by c.request_doctor_id order by c.request_doctor_id) t2
on t1.user_name=t2.doctor_in_charge inner join (select d.creator_id,
count( distinct(d.creator_name)) AS RECORDCOUNT from
mr_file_index d where d.create_date_time >=
CONVERT(varchar(100),'2012-01-04',23) and d.create_date_time <=
CONVERT(varchar(100),'2013-01-08',23) and d.file_flag in
('1','2','3','9') GROUP BY d.creator_id order by d.creator_id)
t3 on t1.user_id=t3.creator_id )sql
解决方案 »
- 删除一个字段中包含“三”的所有数据(表USER,USERNAME)?
- sql server 2005 标准版是否可以安装在windows xp操作系统中?sql server 2005开发版不生产服务,这个所谓的生产服务到底什么意思
- 如何写复合SQL语句?
- sum疑问,谢谢帮助
- Log Explorer 看到的日志为什么和我实际的操作对不上?
- 想将中文转化问拼音,出现问题了,帮忙看看
- Report Manager为何不能使用了?
- 请问怎样建立一个Job实现自动发送邮件?(在线等待!)
- SQL问题
- SQL语句中日期型数据取得的问题(急用!)
- 建立索引的执行计划图相关疑问,求解!
- 存储过程对经过索引的字段不起作用吗?
t1.user_name ,
t2.PATIENTCOUNT ,
t3.RECORDCOUNT
FROM ( ( SELECT b.dept_name ,
a.user_name ,
a.user_id
FROM users a ,
dept_dict b
WHERE a.user_dept = b.dept_code
) t1
INNER JOIN ( SELECT c.request_doctor_id ,
COUNT(c.patient_id) AS PATIENTCOUNT
FROM mr_on_line c
WHERE c.request_date_time >= CONVERT(VARCHAR(100), '2012-01-04', 23)
AND c.create_date_time <= CONVERT(VARCHAR(100), '2013-01-08', 23)
GROUP BY c.request_doctor_id
--ORDER BY c.request_doctor_id
) t2 ON t1.user_name = t2.doctor_in_charge
INNER JOIN ( SELECT d.creator_id ,
COUNT(DISTINCT ( d.creator_name )) AS RECORDCOUNT
FROM mr_file_index d
WHERE d.create_date_time >= CONVERT(VARCHAR(100), '2012-01-04', 23)
AND d.create_date_time <= CONVERT(VARCHAR(100), '2013-01-08', 23)
AND d.file_flag IN ( '1', '2', '3', '9' )
GROUP BY d.creator_id
--ORDER BY d.creator_id
) t3 ON t1.user_id = t3.creator_id
)
不过你用派生表进行多表联接查询,联接的结果不会依据派生表内的数据顺序
所以你排序了也没用,把order by子句删掉就行了