SELECT HOTELID, 是否直营,事业部,本月收入

FROM  (

SELECT

     A.HOTELID,

A.ISDIRECT AS 是否直营,

B.DIVISION AS `事业部`,

SUM(C.`DAY`) AS `本月收入`

FROM

ods_xms_hotelid AS A,

ods_fr_hotelextend AS B,

dw_fd_ynjourrep AS C

WHERE

A.HOTELID = B.HOTELID AND B.HOTELID = C.HOTELID

AND A.ISDIRECT = 'T'

AND A.SECTIONID = 'HOTEL' 

AND C.CLS = 70000000 

  AND YEAR(C.DATE) = '${year(date)}' AND MONTH(C.DATE) = '${month(date)}'

GROUP BY B.DIVISION, A.HOTELID

order by

CASE `事业部`

WHEN '酒店公司事业一部' THEN

1

WHEN '酒店公司事业二部' THEN

2

WHEN '酒店公司事业三部' THEN

3

WHEN '酒店公司济南区域事业部' THEN

4

WHEN '酒店公司青岛区域事业部' THEN

5

WHEN '酒店公司临沂区域事业部' THEN

6

END  ASC

)  

group by 事业部 

SELECT

     A.HOTELID,

A.ISDIRECT AS 是否直营,

B.DIVISION AS `事业部`,

SUM(C.`DAY`) AS `本月收入`

FROM

ods_xms_hotelid AS A,

ods_fr_hotelextend AS B,

dw_fd_ynjourrep AS C

WHERE

A.HOTELID = B.HOTELID AND B.HOTELID = C.HOTELID

AND A.ISDIRECT = 'T'

AND A.SECTIONID = 'HOTEL' 

AND C.CLS = 70000000 

  AND YEAR(C.DATE) = '${year(date)}' AND MONTH(C.DATE) = '${month(date)}'

GROUP BY B.DIVISION, A.HOTELID,A.ISDIRECT

order by

CASE `事业部`

WHEN '酒店公司事业一部' THEN

1

WHEN '酒店公司事业二部' THEN

2

WHEN '酒店公司事业三部' THEN

3

WHEN '酒店公司济南区域事业部' THEN

4

WHEN '酒店公司青岛区域事业部' THEN

5

WHEN '酒店公司临沂区域事业部' THEN

6

END  ASC;