在sql server中,要求在一个10000条医院就诊记录中,将两个月内出现6次的和六个月出现8次的病人输出到单独的两个表格中。求算法
在sql server中,要求在一个10000条医院就诊记录中,将两个月内出现6次的和六个月出现8次的病人输出到单独的两个表格中。求算法!!!!
程序代码:
create table JZJL ( JZ_TIME DATETIME,---就诊时间 JZ_NAME VARCHAR(20)---病人名称 ) INSERT INTO JZJL VALUES('20110101','A') INSERT INTO JZJL VALUES('20110201','A') INSERT INTO JZJL VALUES('20110101','A') INSERT INTO JZJL VALUES('20110101','A') INSERT INTO JZJL VALUES('20110101','A') INSERT INTO JZJL VALUES('20110101','A') INSERT INTO JZJL VALUES('20110501','B') INSERT INTO JZJL VALUES('20110601','B') INSERT INTO JZJL VALUES('20110601','B') INSERT INTO JZJL VALUES('20110601','B') INSERT INTO JZJL VALUES('20110601','B') INSERT INTO JZJL VALUES('20110601','B') INSERT INTO JZJL VALUES('20110601','A') INSERT INTO JZJL VALUES('20110601','A') /*两个月出现6次*/ SELECT T.JZ_NAME FROM (SELECT MAX(JZ_TIME) MINTIME,MIN(JZ_TIME) MAXTIME,JZ_NAME FROM JZJL WHERE JZ_NAME IN (SELECT JZ_NAME FROM JZJL GROUP BY JZ_NAME HAVING COUNT(1)=6) GROUP BY JZ_NAME)T WHERE DATEDIFF(MONTH,T.MINTIME,T.MAXTIME)<2 /*6个月出现8次*/ SELECT T.JZ_NAME FROM (SELECT MAX(JZ_TIME) MINTIME,MIN(JZ_TIME) MAXTIME,JZ_NAME FROM JZJL WHERE JZ_NAME IN (SELECT JZ_NAME FROM JZJL GROUP BY JZ_NAME HAVING COUNT(1)=8) GROUP BY JZ_NAME)T WHERE DATEDIFF(MONTH,T.MINTIME,T.MAXTIME)<6楼主看看对你有没有帮助