27,579
社区成员
发帖
与我相关
我的任务
分享
select "540100" "销售"+"成本" as "ggg" from ( SELECT "public"."数据"."年月", "public"."数据"."财年", "public"."数据"."财月", "public"."数据"."部门名称", "public"."数据"."性质", SUM(CASE WHEN "public"."数据"."科目编码" = '50000100' THEN "public"."数据"."借方" ELSE NULL END) AS "500001", Sum(CASE WHEN "public"."数据"."科目编码" = '50010000' THEN "public"."数据"."借方" ELSE NULL END) AS "销售", Sum(CASE WHEN "public"."数据"."科目编码" = '54010000' THEN "public"."数据"."借方" ELSE NULL END) AS "成本", Sum(CASE WHEN "public"."数据"."科目编码" = '20000000' THEN "public"."数据"."借方" ELSE NULL END) AS "200000", Sum(CASE WHEN "public"."数据"."科目编码" = '54030000' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56010000' THEN "public"."数据"."借方" ELSE NULL END) AS "560100", Sum(CASE WHEN "public"."数据"."科目编码" = '56010100' THEN "public"."数据"."借方" ELSE NULL END) AS "560101", Sum(CASE WHEN "public"."数据"."科目编码" = '56010200' THEN "public"."数据"."借方" ELSE NULL END) AS "560102", Sum(CASE WHEN "public"."数据"."科目编码" = '56010300' THEN "public"."数据"."借方" ELSE NULL END) AS "560103", Sum(CASE WHEN "public"."数据"."科目编码" = '56010400' THEN "public"."数据"."借方" ELSE NULL END) AS "560104", Sum(CASE WHEN "public"."数据"."科目编码" = '56010500' THEN "public"."数据"."借方" ELSE NULL END) AS "560105", Sum(CASE WHEN "public"."数据"."科目编码" = '56010600' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56010700' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56010800' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56010900' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56011000' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56011100' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56011200' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56011300' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56011400' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56011500' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56011600' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56011700' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56011800' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56011900' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56012000' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56012100' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56012200' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56012300' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56012400' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56012500' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56012600' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56012700' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56012800' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56012900' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56013000' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '30000000' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '50510000' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '54020000' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56020000' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56020100' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56020200' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56020300' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56020400' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56020500' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56020600' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56020700' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56020800' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56020900' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56021000' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56021100' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56021200' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56021300' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56021400' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56021500' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56021600' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56021700' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56021800' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56021900' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56022000' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56022100' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56022200' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56022300' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56022400' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56022500' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56022600' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56022700' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56022800' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56022900' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56023000' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56030000' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56030100' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56030200' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56030300' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '56030400' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '40000000' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '51110000' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '53010000' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '57110000' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '57110100' THEN "public"."数据"."借方" ELSE NULL END) AS "540300", Sum(CASE WHEN "public"."数据"."科目编码" = '57110200' THEN "public"."数据"."借方" ELSE NULL END) AS "540300" FROM "public"."数据" WHERE "public"."数据"."一级编码" > 5000 GROUP BY "public"."数据"."年月", "public"."数据"."财年", "public"."数据"."财月", "public"."数据"."部门名称", "public"."数据"."性质" ) as bv GROUP BY "销售"
楼上老师 换成动态的行转列 怎么写?
SELECT
`年月`,
`财年`,
`科目名称`,
`一级编码`,
`一级科目`,
`部门名称`,
SUM(CASE WHEN `科目编码` = '50010000' THEN `金额` ELSE NULL END ) AS '111',
SUM ( CASE WHEN `科目编码` = '54010000' THEN `金额` ELSE NULL END ) AS '112',
`性质`
from
( SELECT
`public`.`数据`.`年月`,
`public`.`数据`.`科目编码`,
`public`.`数据`.`科目名称`,
`public`.`数据`.`一级编码`,
`public`.`数据`.`一级科目`,
SUM ( `public`.`数据`.`借方` ) AS `金额`,
`public`.`数据`.`部门名称`,
`public`.`数据`.`财年`,
`public`.`数据`.`性质`
FROM
`public`.`数据`
WHERE
`public`.`数据`.`一级编码` > 5000
GROUP BY
`public`.`数据`.`年月`,
`public`.`数据`.`科目编码`,
`public`.`数据`.`部门名称`,
`public`.`数据`.`一级编码`,
`public`.`数据`.`科目名称`,
`public`.`数据`.`一级科目`,
`public`.`数据`.`财年`,
`public`.`数据`.`性质`) AS gg
GROUP BY
`年月`,
`科目名称`,
`一级编码`,
`一级科目`,
`性质`,
`财年`