1 Simple internal connection problem
Q:
Tab1: (Subject Table) Nodeid NodeName1 Language 2 Mathematics 3 English ....
Tab2: (difficulty table) TypeID Typename 1 Easy 2 easily 3 4 more difficult 5 difficult
Tab3: (Test Table) Quesid Nodeid TypeID1 1 22 1 33 2 34 2 15 3 5 ...
Require statistics: (count the number of each subject under each subject)
Easy to be more difficult, language 0 1 1 0 0 mathematics 1 0 1 0 0 English 0 0 0 0 1 ... A:
Create Table Tab1 (Nodeid nvarchar (2) Not null, NodeName Nvarchar (20) Not null) Insert Into Tab1 (NodeId, NodeName) Select '1', 'Language' Union AllSelect '2', 'Mathematics' Union AllSelect '3' ,'English language'
Create Table Tab2 (Typeid Nvarchar (2) Not Null, TypeName Nvarchar (20) Not Null Insert Into Tab2 (TypeId, TypeName) SELECT '1', 'Eye' Union AllSelect '2', 'More Easy' Union AllSelect '3 ',' Union allselect '4', 'is more difficult' union allselect '5', 'difficult'
Create Table Tab3 (Quesid Int Idness (1), Nodeid Nvarchar (2) Not Null, Typeid Nvarchar (2) Not Null Insert Into Tab3 (NodeId, TypeId) Select '1', '2' Union AllSelect '1' , '3' union allselect '2', '3' union allselect '2', '1' union allselect '3', '5'
SELECT subject = C.NodeName, Easy = SUM (Case A.Typeid When 1 Then 1 ELSE 0 End), easier = SUM (Case A.TYPEID WHEN 2 THEN 1 ELSE 0 End), = SUM (Case a.TypeID When 3 Ten 1 Else 0 End), difficult = SUM (Case a.Typeid When 4 Then 1 Else 0 End), difficult = SUM (Case A.Typeid When 5 Then 1 Else 0 End)
From Tab3 a inner join tab2 b on a.typeid = b.typeid inner join tab1 c on a.nodeid = C.NodeID
Group by c.nodeenamedrop table tab1, tab2, tab3
