主页 / dba / 问题 / 175080
Accepted
Fanar Web
Asked: 2017-06-01 11:26:31 +0800 CST

双组?? 可能吗？

  • 772

我正在尝试修改我的报告 mysql 查询，当前查询是：

select `companies`.`companynameturkish` AS `Company_Name`, (round(count(`applications`.`company_id`) / (select count(*) from applications where ((`applications`.`appstatus` = '3') ))*100)) AS `Work Load`,
sum(`applications`.`netprice`) AS `Total Cost`,`companies`.`percentage` AS `Firm %`
, round(((sum(`applications`.`netprice`) * 49) / 100),2) AS `Comission`
, round(((sum(`applications`.`netprice`) * `companies`.`percentage`) / 100),2) AS `Benefits`
, round(((sum(`applications`.`netprice`) * 49) / 100),2) - round(((sum(`applications`.`netprice`) * `companies`.`percentage`) / 100),2) AS `Diff`
, count(`applications`.`company_id`) AS `Quantity`
from (`applications` left join `companies` on((`applications`.`company_id` = `companies`.`id`)))
where ((`applications`.`appstatus` = '3') and (`applications`.`recordcreationdate`
like concat(curdate(),'%'))) group by `applications`.`company_id` order by `Work Load` desc

这是解释：

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY applications    NULL    ALL company_id  NULL    NULL    NULL    6   16.67   Using where; Using temporary; Using filesort
1   PRIMARY companies   NULL    eq_ref  PRIMARY PRIMARY 4   db.applications.company_id  1   100.00  Using where
2   SUBQUERY    applications    NULL    ALL NULL    NULL    NULL    NULL    6   16.67   Using where

我想做的是添加新列并按它分组，该列是：提供者类型（applications.type），因此按 applications.type 分组的应用程序左加入 applications.type = tbl_provider.id 上的 tbl_provider，选择 tbl_provider。本专栏的提供者名称，这些是我的表结构，可帮助您了解我在说什么：

CREATE TABLE `applications` (
  `serial` int(11) NOT NULL AUTO_INCREMENT,
  `policy_no` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
  `company_id` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dataentry` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `creator_id` int(11) DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `type` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `provider_percentage` int(11) DEFAULT NULL,
  `firstname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fathername` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `country` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `passportno` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address_main` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address_sub` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `length` tinyint(1) DEFAULT NULL,
  `price` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `paid_price` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `netprice` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `saved_price` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `confirmed` tinyint(1) DEFAULT NULL,
  `appstatus` tinyint(1) DEFAULT NULL,
  `startdate` date DEFAULT NULL,
  `enddate` datetime DEFAULT NULL,
  `recordcreationdate` datetime DEFAULT NULL,
  `recordlastediteddate` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `cancel_reason` text COLLATE utf8_unicode_ci,
  `image_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `net_price_deduced` tinyint(1) NOT NULL DEFAULT '0',
  `status_change_1_to_3` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`serial`),
  KEY `company_id` (`company_id`),
  KEY `policy_no` (`policy_no`),
  KEY `type` (`type`),
  FULLTEXT KEY `policy_no_ft` (`policy_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `companies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `manager_id` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `username` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `password` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `companynamearabic` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `companynameturkish` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `managername` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `companyspeciality` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `percentage` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `companytelephone` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `companyemail` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `contactname` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `contactphone` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `website` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `status` tinyint(11) DEFAULT NULL,
  `balancetype` tinyint(11) DEFAULT NULL,
  `companylimit` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `balance` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `recordcreationdate` date DEFAULT NULL,
  `recordlastediteddate` date DEFAULT NULL,
  `auto_approve` tinyint(4) DEFAULT NULL,
  `avl2mrkz` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `tbl_provider` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `provider_name` varchar(225) COLLATE utf8_bin DEFAULT NULL,
  `no_of_slabs` int(11) DEFAULT NULL,
  `age_from` int(11) DEFAULT NULL,
  `age_to` int(11) DEFAULT NULL,
  `provider_percentage` double DEFAULT NULL,
  `provider_income` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

非常感谢 ！

mysql join

1 个回答

  1. Best Answer

    听起来您想要做的是tbl_provider通过LEFT JOINon绑定到现有查询，applications.type = tbl_provider.id然后将provider_namefrom添加tbl_provider到SELECT列表和GROUP BY子句中。

    注意：对于LEFT JOIN，如果没有匹配的提供者，provider_name将是NULL。它应该仍然出现在SELECT列表中，并且仍然可以分组，但似乎值得注意。

    基本上，要对多列进行分组，您只需要在 之后列出所有列GROUP BY。

    我相信这会做你正在寻找的，如果以上是正确的（为了便于阅读，查询稍微重新格式化）：

    select `companies`.`companynameturkish` AS `Company_Name`
      ,`tbl_provider`.`provider_name`
      ,(round(count(`applications`.`company_id`) / (select count(*) from applications where ((`applications`.`appstatus` = '3') ))*100)) AS `Work Load`
      ,sum(`applications`.`netprice`) AS `Total Cost`
      ,`companies`.`percentage` AS `Firm %`
      ,round(((sum(`applications`.`netprice`) * 49) / 100),2) AS `Comission`
      ,round(((sum(`applications`.`netprice`) * `companies`.`percentage`) / 100),2) AS `Benefits`
      ,round(((sum(`applications`.`netprice`) * 49) / 100),2) - round(((sum(`applications`.`netprice`) * `companies`.`percentage`) / 100),2) AS `Diff`
      ,count(`applications`.`company_id`) AS `Quantity`
  from ((`applications` left join `companies` on (`applications`.`company_id` = `companies`.`id`))
                        left join `tbl_provider` on (`applications`.`type` = `tbl_provider`.`id`))
 where (    (`applications`.`appstatus` = '3')
        and (`applications`.`recordcreationdate` like concat(curdate(),'%'))
       )
 group by `applications`.`company_id`, `tbl_provider`.`provider_name`
 order by `Work Load` desc

    我已经添加tbl_provider.provider_name为SELECT列表中的第二项，添加LEFT JOIN到tbl_provider，并添加tbl_provider.provider_name为列表中的第二项GROUP BY。

    注意：代码未经测试，可能有拼写错误。

    • 0

相关问题