01.
select
a.A_id, concat(A_fname,
' '
, A_Lname)
as
nm
02.
, if(B_Date=d1
and
B_round=1, cn, 0)
as
d1_1, if(B_Date=d1
and
B_round=1, C_name,
''
)
as
cn_1_1
03.
, if(B_Date=d1
and
B_round=2, cn, 0)
as
d1_2, if(B_Date=d1
and
B_round=2, C_name,
''
)
as
cn_1_2
04.
, if(B_Date=d1
and
B_round=3, cn, 0)
as
d1_3, if(B_Date=d1
and
B_round=3, C_name,
''
)
as
cn_1_3
05.
, if(B_Date=d2
and
B_round=1, cn, 0)
as
d2_1, if(B_Date=d2
and
B_round=1, C_name,
''
)
as
cn_2_1
06.
, if(B_Date=d2
and
B_round=2, cn, 0)
as
d2_2, if(B_Date=d2
and
B_round=2, C_name,
''
)
as
cn_2_2
07.
, if(B_Date=d2
and
B_round=3, cn, 0)
as
d2_3, if(B_Date=d2
and
B_round=3, C_name,
''
)
as
cn_2_3
08.
from
(
select
*,
'3/16/2018'
as
d1,
'3/17/2018'
as
d2
from
db_A)
as
a
09.
left
join
(
10.
select
a.A_id, B_date, B_round, C_name,
count
(*)
as
cn
11.
from
db_ADD a
12.
left
join
db_B b using B_id
13.
left
join
db_C c using C_id
14.
group
by
a.A_id, a.B_id, a.C_id
15.
)
as
tc using A_id