select * from
(select USER_NAME, count(patient_status) as num_daoyuan from hospital_patients where patient_status='到院'GROUP BY USER_NAME) as aa,
(select USER_NAME, count(patient_yytime) as num_yytime from hospital_patients where patient_yytime!='1990-01-01 00:00:00.000' GROUP BY USER_NAME) as bb,
(select USER_NAME,sum(patient_sr) as sr from hospital_patients GROUP BY USER_NAME) as cc,
(select USER_NAME,avg(patient_numkeren) as kr from hospital_patients GROUP BY USER_NAME) as dd,
(select USER_NAME,avg(patient_numkefu) as kf from hospital_patients GROUP BY USER_NAME) as ee,
(select USER_NAME,avg(patient_numkefu) as kf from hospital_patients GROUP BY USER_NAME) as ff
where aa.User_name=bb.User_name and aa.User_name=cc.User_name and aa.User_name=dd.User_name and aa.User_name=ee.User_name order by num_yytime desc
能不能把上面的SQL语句简略一下呀？？？
还有一个问题是：怎么求时间列【starttime】和【endtime】差的平均值，并且由USER_NAME分组
我就是想查询每个人的平均时长！！！
------解决思路----------------------
select cc.*, aa.num_daoyuan, bb.num_yytime from
(select USER_NAME,
sum(patient_sr) as sr,
avg(patient_numkeren) as kr,
avg(patient_numkefu) as kf
from hospital_patients
GROUP BY USER_NAME) as cc
left join
(select USER_NAME, count(patient_status) as num_daoyuan
from hospital_patients
where patient_status='到院'
GROUP BY USER_NAME) as aa
on aa.USER_NAME = cc.USER_NAME
left join
(select USER_NAME, count(patient_yytime) as num_yytime
from hospital_patients
where patient_yytime!='1990-01-01 00:00:00.000'
GROUP BY USER_NAME) as bb
on aa.USER_NAME = bb.USER_NAME
order by bb.num_yytime desc
------解决思路----------------------
2 楼正解，貌似少了一个子查询，请问如图
貌似重复了？？？？
------解决思路----------------------
求时间列【starttime】和【endtime】差的平均值，并且由USER_NAME分组的问题
有个想法 ，先求出每行的 【starttime】和【endtime】之差 作为一列
再求这一列的平均值
在SQL 中体现上面的想法，
能否提供一些实例数据呢？
------解决思路----------------------
select * from
(select USER_NAME, count(patient_status) as num_daoyuan from hospital_patients where patient_status='到院'GROUP BY USER_NAME) as aa
join
(select USER_NAME, count(patient_yytime) as num_yytime from hospital_patients where patient_yytime!='1990-01-01 00:00:00.000' GROUP BY USER_NAME) as bb
on aa.user_name=bb.user_name
join
(select USER_NAME,sum(patient_sr) as sr,avg(patient_numkeren) as kr
,avg(patient_numkefu) as kf
from hospital_patients GROUP BY USER_NAME) as cc
下面的三个派生表条件一样，分组一样，可以放一起。
------解决思路----------------------
try this,
select USER_NAME,
sum(case when patient_status='到院' then 1 else 0 end) 'num_daoyuan',
sum(case when patient_yytime!='1990-01-01 00:00:00.000' then 1 else 0 end) 'num_yytime',
sum(patient_sr) as sr,
avg(patient_numkeren) as kr,
avg(patient_numkefu) as kf,
avg(patient_numkefu) as kf
from hospital_patients
where patient_status='到院' and patient_yytime!='1990-01-01 00:00:00.000'
group by USER_NAME
order by num_yytime desc
------解决思路----------------------
--方式1--
SELECT * FROM(
SELECT
T1.[USER_NAME]
,SUM(CASE T1.patient_status WHEN'到院'THEN 1 ELSE 0 END)num_daoyuan
,SUM(CASE T1.patient_yytime WHEN'1990-01-01 00:00:00.000'THEN 0 ELSE 1 END)num_yytime
,SUM(T1.patient_sr)sr
,AVG(T1.patient_numkeren)kr
,AVG(T1.patient_numkefu)kf
FROM hospital_patients T1
JOIN(
SELECT [USER_NAME] FROM hospital_patients
WHERE(patient_status='到院'OR patient_yytime<>'1990-01-01 00:00:00.000')
GROUP BY [USER_NAME]
)T2 ON T1.[USER_NAME]=T2.[USER_NAME]
GROUP BY T1.[USER_NAME]
)T
WHERE num_daoyuan>0 AND num_yytime>0
ORDER BY [USER_NAME]
--方式2--数据量不是非常庞大时，标量IN速度可能要快些
SELECT * FROM(
SELECT
T1.[USER_NAME]
,SUM(CASE T1.patient_status WHEN'到院'THEN 1 ELSE 0 END)num_daoyuan
,SUM(CASE T1.patient_yytime WHEN'1990-01-01 00:00:00.000'THEN 0 ELSE 1 END)num_yytime
,SUM(T1.patient_sr)sr
,AVG(T1.patient_numkeren)kr
,AVG(T1.patient_numkefu)kf
FROM hospital_patients T1
WHERE T1.[USER_NAME] IN(
SELECT [USER_NAME] FROM hospital_patients
WHERE(patient_status='到院'OR patient_yytime<>'1990-01-01 00:00:00.000')
)
GROUP BY T1.[USER_NAME]
)T
WHERE num_daoyuan>0 AND num_yytime>0
ORDER BY [USER_NAME]
你看下 哪种方式效率高些
------解决思路----------------------
调整一下
--方式1
SELECT
T1.[USER_NAME]
,SUM(CASE T1.patient_status WHEN'到院'THEN 1 ELSE 0 END)num_daoyuan
,SUM(CASE T1.patient_yytime WHEN'1990-01-01 00:00:00.000'THEN 0 ELSE 1 END)num_yytime
,SUM(T1.patient_sr)sr
,AVG(T1.patient_numkeren)kr
,AVG(T1.patient_numkefu)kf
FROM hospital_patients T1
JOIN(
SELECT [USER_NAME] FROM hospital_patients
WHERE(patient_status='到院'AND patient_yytime<>'1990-01-01 00:00:00.000')
GROUP BY [USER_NAME]
)T2 ON T1.[USER_NAME]=T2.[USER_NAME]
GROUP BY T1.[USER_NAME]
ORDER BY T1.[USER_NAME]
--方式2
SELECT
T1.[USER_NAME]
,SUM(CASE T1.patient_status WHEN'到院'THEN 1 ELSE 0 END)num_daoyuan
,SUM(CASE T1.patient_yytime WHEN'1990-01-01 00:00:00.000'THEN 0 ELSE 1 END)num_yytime
,SUM(T1.patient_sr)sr
,AVG(T1.patient_numkeren)kr
,AVG(T1.patient_numkefu)kf
FROM hospital_patients T1
WHERE T1.[USER_NAME] IN(
SELECT [USER_NAME] FROM hospital_patients
WHERE(patient_status='到院'AND patient_yytime<>'1990-01-01 00:00:00.000')
)
GROUP BY T1.[USER_NAME]
ORDER BY T1.[USER_NAME]
------解决思路----------------------
我想楼主要的应该是类似于
SELECT
aa.[USER_NAME] ,
avg(DATEDIFF(hh,aa.patient_starttime,aa.patient_endtime)) AS N'平均时长(小时)'
FROM (
SELECT '海涛' AS [USER_NAME],'2015-06-13 22:05:06.000' AS patient_starttime,'2015-06-13 23:01:02.000' AS patient_endtime UNION ALL
SELECT '白苏' AS [USER_NAME],'2015-06-13 22:05:06.000' AS patient_starttime,'2015-06-13 23:01:02.000' AS patient_endtime UNION ALL
SELECT '白苏' AS [USER_NAME],'2015-06-13 22:05:06.000' AS patient_starttime,'2015-06-13 23:01:02.000' AS patient_endtime UNION ALL
SELECT '李小娜' AS [USER_NAME],'2015-06-13 22:05:06.000' AS patient_starttime,'2015-06-13 23:01:02.000' AS patient_endtime UNION ALL
SELECT '朱玉珍' AS [USER_NAME],'2015-06-13 22:05:06.000' AS patient_starttime,'2015-06-13 23:01:02.000' AS patient_endtime UNION ALL
SELECT '杨晓峰' AS [USER_NAME],'2015-06-13 22:05:06.000' AS patient_starttime,'2015-06-13 23:01:02.000' AS patient_endtime UNION ALL
SELECT '李小娜' AS [USER_NAME],'2015-06-13 22:05:06.000' AS patient_starttime,'2015-06-13 23:01:02.000' AS patient_endtime UNION ALL
SELECT '海涛' AS [USER_NAME],'2015-06-13 22:05:06.000' AS patient_starttime,'2015-06-13 23:01:02.000' AS patient_endtime UNION ALL
SELECT '朱玉珍' AS [USER_NAME],'2015-06-13 22:05:06.000' AS patient_starttime,'2015-06-13 23:01:02.000' AS patient_endtime UNION ALL
SELECT '白苏' AS [USER_NAME],'2015-06-13 22:05:06.000' AS patient_starttime,'2015-06-13 23:01:02.000' AS patient_endtime UNION ALL
SELECT '张博巍' AS [USER_NAME],'2015-06-13 22:05:06.000' AS patient_starttime,'2015-06-13 23:01:02.000' AS patient_endtime UNION ALL
SELECT '张博巍' AS [USER_NAME],'2015-06-13 22:05:06.000' AS patient_starttime,'2015-06-13 23:01:02.000' AS patient_endtime UNION ALL
SELECT '张博巍' AS [USER_NAME],'2015-06-13 22:05:06.000' AS patient_starttime,'2015-06-13 23:01:02.000' AS patient_endtime UNION ALL
SELECT '张博巍' AS [USER_NAME],'2015-06-13 22:05:06.000' AS patient_starttime,'2015-06-13 23:01:02.000' AS patient_endtime
) aa
GROUP BY aa.[USER_NAME]
结果
USER_NAME 平均时长
白苏 1
海涛 1
李小娜 1
杨晓峰 1
张博巍 1
朱玉珍 1