How to get on the same day have conforms to the data of three conditions.-CodePudding

There is a table: TB
Table structure:
The patient ID, date, drug coding (other fields can be ignored)

I want to find, those patients had one day, at the same time open drug code for A, B, C three kinds of medicine,

Results:

The patient ID date
10002 the 2020-10-29
10002 the 2020-10-30
10002 the 2020-11-04
10003 the 2020-11-01
10003 the 2020-11-02
The 2020-11-03 10004

CodePudding user response:

Select the patient ID, date
From the table
Where drug code in (A, B, C)
Group by the patient ID, date
Having a count (drug code)=3

CodePudding user response:

reference 1/f, the breeze gently up response:

select the patient ID, date
From the table
Where drug code in (A, B, C)
Group by the patient ID, date
Having a count (drug code)=3

Thank you, but you will have A problem, if A drug opened three times, didn't open B, C will this condition,

CodePudding user response:

You're right, then set a layer,
Select the patient ID, date
The from (
Select the patient ID, date, drug code
From the table
Where drug code in (A, B, C)
Group by the patient ID, date, drug code
)
Group by the patient ID, date
Having a count (1)=3

CodePudding user response:

Date if it is a timestamp, oneself again under the processing line,

CodePudding user response:

The SELECT Y_NAME, YI_DATE
The FROM TB
WHERE YI_CODE IN (' A ', 'B', 'C')
GROUP BY Y_NAME, YI_DATE
HAVING a COUNT (DISTINCT YI_CODE)=3

CodePudding user response:

You can refer to the

 with t as (
Select id 10002, '2020-01-01' c_date, 'A' c_type from dual union all 
Select 10002, '2020-01-01', 'B' from dual union all 
Select 10002, '2020-01-02', 'A' from dual union all 
Select 10002, '2020-01-02', 'B' from dual union all 
Select 10002, '2020-01-02', 'C' from dual union all 
Select 10003, '2020-01-01', 'A' from dual union all 
Select 10003, '2020-01-01', 'B' from dual union all 
Select 10003, '2020-01-01', 'B' from dual union all 
Select 10004, '2020-01-01', 'A' from dual union all 
Select 10004, '2020-01-01', 'C' from dual) 
Select id, c_date 
The from (
Select ID, c_date listagg (distinct c_type, ', ') WITHIN GROUP (ORDER BY c_type) a 
The from t 
Where c_type in (' A ', 'B', 'C') 
Group by ID, c_date) 
Where a='a, B, C'