mysql 是否将报表与页眉和明细分组？

hrirmatl 于 2023-06-21 发布在 Mysql

关注(0)|答案(3)|浏览(97)

我有以下详细信息的表交易
| id| dt_trans|类别|描述|金额|
| - -----|- -----|- -----|- -----|- -----|
| 1| 2022-08-05 2022-08-05|费用|食品|60|
| 2| 2022-08-05 2022-08-05|旅游|汽油|20点|
| 2| 2022-08-08 2022-08-08|旅游|汽油|20点|
然后我想根据下面的报告创建报告
| | dt_trans|类别|描述|金额|
| - -----|- -----|- -----|- -----|- -----|
|费用|||||
| 1| 2022-08-05 2022-08-05|费用|食品|60|
| | | |合计| 60|
| | | | | |
|旅行|||||
| 1| 2022-08-05 2022-08-05|旅游|汽油|20点|
| 2| 2022-08-08 2022-08-08|旅游|汽油|20点|
| | | |合计| 四十|
通常我会根据报表创建一个临时表，并根据2个查询添加行。逻辑是这样的

Loop // query1 retrieve all the category

   query2 retrieve all the details based on category

end of loop query1

是否有更简单的方法来创建此报告？
查询：

DROP TABLE IF EXISTS `transaction`;
CREATE TABLE `transaction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cashaccount_id` int(11) DEFAULT NULL,
  `dt_trans` date DEFAULT NULL,
  `category` varchar(200) DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  `type` tinyint(4) DEFAULT NULL COMMENT '0 income 1 expense',
  `amount` float(10,2) DEFAULT 0.00,
  `dt_created` datetime DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `transaction` (`id`, `cashaccount_id`, `dt_trans`, `category`, `description`, `type`, `amount`, `dt_created`, `uid`) VALUES
(115, 1, '2021-10-10', 'Expense', 'Food', 1, 60.00, '2021-10-10 17:06:36', 1),
(121, 1, '2021-10-15', 'Travel', 'Petrol', 1, 20.00, '2021-10-15 01:01:15', 1),
(123, 1, '2021-10-05', 'Travel', 'Petrol', 0, 20.00, '2021-10-15 09:11:28', 1)

mysql

来源：https://stackoverflow.com/questions/76404160/group-report-with-header-and-detail

3条答案

按热度按时间

roejwanj1#

尝试使用ROLLUP。示例

select category head,rn, max(dt_trans)dt_trans,category,max(description)description,sum(amount) amount
from(
select *
  ,row_number()over(partition by category order by category desc,dt_trans) rn
from transaction
) t
group by category ,rn with rollup

结果。rn -按类别合计中为空，表头和rn -报表合计中均为空。
| 头部|rn| dt_trans|类别|描述|金额|
| - -----|- -----|- -----|- -----|- -----|- -----|
| 费用|1| 2021-10-10- 2021-10-10|费用|食品|60|
| 费用|零|2021-10-10- 2021-10-10|费用|食品|60|
| 旅游|1| 2021-10-05 -05 - 05 - 05 - 05 - 05 - 05 - 05 - 05 - 05 - 05 -05 - 01|旅游|汽油|20点|
| 旅游|2| 2021-10- 15 - 2021-10-15|旅游|汽油|20点|
| 旅游|零|2021-10- 15 - 2021-10-15|旅游|汽油|四十|
| 零|零|2021-10- 15 - 2021-10-15|零|汽油|一百|

赞(0）回复(0）举报 2023-06-21

uxh89sit2#

尝试将三个select语句合并，每个语句都有特定的顺序，如下所示：
要获取所有用户的报告，请执行以下操作：

with t as
(
  select id, dt_trans, category, description, amount, uid,
       dense_rank() over (order by uid, category) as ord1, 2 as ord2
  from transaction
  
  union all
  
  select distinct category, '', '', '', '', uid,
         dense_rank() over (order by uid, category) as ord1,
         1 as ord2
  from transaction 
  
  union all
  
  select  '', '', '', 'Total', 
          sum(amount), uid,
          dense_rank() over (order by uid, category) as ord1, 
          3 as ord2 
  from transaction 
  group by category, uid
)
select uid, id, dt_trans, category, description, amount
from t
order by uid, ord1, ord2

demo
要获取单个用户的报告，请执行以下操作：

with t as
(
  select id, dt_trans, category, description, amount,
       dense_rank() over (order by category) as ord1, 2 as ord2
  from transaction where uid = 1
  
  union all
  
  select distinct category, '', '', '', '',
         dense_rank() over (order by category) as ord1,
         1 as ord2
  from transaction where uid = 1
  
  union all
  
  select  '', '', '', 'Total', 
          sum(amount),
          dense_rank() over (order by category) as ord1, 
          3 as ord2 
  from transaction where uid = 1
  group by category 
)
select id, dt_trans, category, description, amount
from t
order by ord1, ord2

demo
但是最好在SQL服务器之外执行这种格式化报告。

赞(0）回复(0）举报 2023-06-21

bfnvny8b3#

你可能会尝试在编程方面的循环，但这里是一个简单的MySQL查询

SELECT 
    s.*
FROM
    transaction s
        CROSS JOIN
    (SELECT 
        category
    FROM
        transaction) AS t
        GROUP BY s.id

赞(0）回复(0）举报 2023-06-21

我来回答

mysql 是否将报表与页眉和明细分组？

3条答案

相关问题

热门标签

最新问答