时间戳

首页 > 解决方案 > 问题 MYSQL Query join with output want

mysql - 问题 MYSQL Query join with output want

问题描述

MySql 查询有问题。我已经尝试了很多次查询，但仍然没有得到我想要的。也许任何人都可以帮助我解决我的问题。

这是结构表和我想要的输出： 显示我想要的表格和输出

这是我尝试的，但是当@IDPERIODS=2 时，那不是我想要的显示：

SET @IDPERIODS:=2;
SELECT billing.*
    FROM _t_data_user
    LEFT JOIN (
        SELECT user_id as iduser,
    IF(a.id_bill_type=b.id_bill_type,a.id_setting_bill,ifnull(b.id_setting_bill,a.id_setting_bill)) as idsettingbill,
    id_user_group as group_user,
    IF(a.id_bill_type=b.id_bill_type,a.id_bill_type, ifnull(b.id_bill_type,a.id_bill_type)) as idbilltype,
    IF(a.id_bill_type=b.id_bill_type,a.id_period, ifnull(b.id_period,a.id_period)) as period,
    IF(a.id_bill_type=b.id_bill_type,a.amount_bill, ifnull(b.amount_bill,a.amount_bill)) as amount_billing
        FROM _t_data_user
        LEFT JOIN _t_setting_bill_user b ON b.id_group_user=id_user_group and b.id_period=@IDPERIODS
        LEFT JOIN _t_setting_bill_user a ON a.id_user=user_id and a.id_period=@IDPERIODS
        WHERE IFNULL(a.id_period, b.id_period) = @IDPERIODS
    ) billing ON iduser = user_id
    WHERE period = @IDPERIODS
GROUP BY user_id, idbilltype

这个 MySql 表方案：

表结构和样本数据：

CREATE TABLE `_t_data_user` (
          `user_id` int(4) unsigned NOT NULL AUTO_INCREMENT,
          `id_user_group` int(4) DEFAULT NULL,
          PRIMARY KEY (`user_id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `_t_data_user` (`user_id`, `id_user_group`)
    VALUES
        (1, 1),
        (2, 1),
        (3, 1),
        (4, 2);

CREATE TABLE `_t_setting_bill_user` (
          `id_setting_bill` int(11) unsigned NOT NULL AUTO_INCREMENT,
          `id_group_user` int(4) DEFAULT NULL,
          `id_user` int(4) DEFAULT NULL,
          `id_period` int(4) DEFAULT NULL,
          `id_bill_type` int(4) DEFAULT NULL,
          `amount_bill` bigint(20) DEFAULT NULL,
          PRIMARY KEY (`id_setting_bill`)
        ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
        
INSERT INTO `_t_setting_bill_user` 
 (`id_setting_bill`, `id_group_user`, `id_user`,
  `id_period`, `id_bill_type`, `amount_bill`)
    VALUES
        (1, 1, 0, 1, 1, 1000),
        (2, 1, 0, 1, 2, 500),
        (3, 0, 1, 1, 1, 900),
        (4, 0, 1, 2, 1, 1000),
        (5, 1, 0, 2, 2, 500),
        (6, 2, 0, 1, 1, 1100);

标签： mysqlsqljoinself-join

解决方案

Halo bro desugha，我认为您应该以编程方式进行，您应该重新安排并创建对象以将 data_user 中的数据合并为 setting_bill_user

 SELECT compacted_usr_bill.* FROM (
    SELECT billusr.*, 
        usr.user_id AS usr_id_usr, usr.id_user_group AS usr_id_group
    FROM (
        SELECT bill.id_setting_bill,
            CASE 
                WHEN bill.id_user > 0 AND bill.id_group_user = 0 THEN bill.id_user
                WHEN bill.id_user = 0 AND bill.id_group_user > 0 THEN bill.id_group_user
                ELSE bill.id_user
            END AS grouped_id_usr,
            bill.id_period, bill.id_bill_type, bill.amount_bill
            FROM _t_setting_bill_user AS bill) 
        AS billusr
    LEFT JOIN _t_data_user AS usr
        ON billusr.grouped_id_usr IN(usr.user_id, usr.id_user_group)        
    ) AS compacted_usr_bill

此查询将它们结合起来，您可以使用分组或编程方式再次过滤

推荐阅读