在sql server中,要求在一个10000条医院就诊记录中,将两个月内出现6次的和六个月出现8次的病人输出到单独的两个表格中。求算法
在sql server中,要求在一个10000条医院就诊记录中,将两个月内出现6次的和六个月出现8次的病人输出到单独的两个表格中。求算法!!!!
create database PatientAdmissions go use PatientAdmissions create table PatientAdmissionsData (TreatmentID int NOT NULL PRIMARY KEY , NI int not null , Gender varchar(1) not null , AdmissionDate date not null , AdmissionType VARCHAR(20) NOT NULL , Treatment varchar(20) not null ) BULK INSERT PatientAdmissionsData FROM 'C:\Users\20021939\Desktop\04062012\PatientAdmissionsData10k.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) GO alter table PatientAdmissionsData add Flag int select * from PatientAdmissionsData /*2个月出现6次以及以上*/ SELECT NI FROM (SELECT MAX(AdmissionDate) MINTIME,MIN(AdmissionDate) MAXTIME,NI FROM PatientAdmissionsData WHERE NI IN (SELECT NI FROM PatientAdmissionsData GROUP BY NI HAVING COUNT(1)>=6) GROUP BY NI)T WHERE DATEDIFF(MONTH,T.MINTIME,T.MAXTIME)<2 /*6个月出现8次以及以上*/ SELECT NI FROM (SELECT MAX(AdmissionDate) MINTIME,MIN(AdmissionDate) MAXTIME,NI FROM PatientAdmissionsData WHERE NI IN (SELECT NI FROM PatientAdmissionsData GROUP BY NI HAVING COUNT(1)>=8) GROUP BY NI)T WHERE DATEDIFF(MONTH,T.MINTIME,T.MAXTIME)<6 SELECT AdmissionDate FROM PatientAdmissionsData WHERE NI= 18