I have a form field is the name and 5 course

The name of physical chemistry of Chinese maths English
Zhang SAN's excellent good optimal in bad
Li si best best best best good
Fifty and good good good good optimal
,,,,,,,,,,,,,,,,,,,,,,,,

Now I have two requirements 1. To choose at least three or more than three courses as records, such as language number outside is excellent or language, physics and chemistry is good such records
2. Or at least three or more than three courses as record, but the language for these two subjects are in it

CodePudding user response:

Oracle may consider using unpivot function, transfer columns, and then judge is much more convenient

In addition, the table design not good, if one more department courses have to change the table structure

CodePudding user response:

A where clause for a list of
Where
(=math and language=English)
OR
(Chinese=maths and Chinese=physics)
OR
(Chinese=maths and Chinese=chemistry)
OR
(the language=English and Chinese=physics)
OR
(chemical) language=English and Chinese=
OR
(Chinese=physics and Chinese=chemistry)
Or
(=physical mathematics=English and mathematics)
Or
(=math English and math=chemical)
Or
Physical and mathematical==chemical (mathematics)
Or
(English==physics and chemistry)

CodePudding user response:

Select name, val, count (1)
From the TAB unpivot (val for item (in Chinese, maths, English, physics, chemistry))
Group by name, val
Having a count (1) & gt;=3;

Select name, val, count (1), sum (decode (item, 'language', 1, 'mathematics', 1, 0))
From the TAB unpivot (val for item (in Chinese, maths, English, physics, chemistry))
Group by nn, val
Having the sum (decode (item, 'language', 1, 'mathematics', 1, 0))=2
And the count (1) & gt;=3
;

CodePudding user response:

The

reference 3 floor nayi_224 response:

select name, val, count (1)
From the TAB unpivot (val for item (in Chinese, maths, English, physics, chemistry))
Group by name, val
Having a count (1) & gt;=3;

Select name, val, count (1), sum (decode (item, 'language', 1, 'mathematics', 1, 0))
From the TAB unpivot (val for item (in Chinese, maths, English, physics, chemistry))
Group by nn, val
Having the sum (decode (item, 'language', 1, 'mathematics', 1, 0))=2
And the count (1) & gt;=3
;

Question idea is: column turned + group, a similar approach and many functions,
The method can solve the problem, upstairs