主页 / user-120409

Fanar Web's questions

Martin Hope
Fanar Web
Asked: 2017-09-09 20:29:01 +0800 CST
  • 0

我有这张桌子：

    SET NAMES utf8;
SET time_zone = '+00:00';

CREATE TABLE `app_log` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `serial` int(11) NOT NULL,
  `dataEntryIP` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `dataEntryUA` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `domain` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `timeStamp` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `serial` (`serial`)
) ENGINE=InnoDB AUTO_INCREMENT=4328 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

可能的数据样本如下：

INSERT INTO `app_log` (`ID`, `serial`, `dataEntryIP`, `dataEntryUA`, `domain`, `timeStamp`) VALUES
(6,33605,'2.5.29.18','Google Chrome','www.domain1.com','2017-08-18 18:44:23'),
(7,33606,'8.102.167.213','Google Chrome','domain1.com','2017-08-18 18:45:48'),
(8,33607,'8.24.10.14','Google Chrome','www.domain0.com','2017-08-18 18:58:13'),
(9,33608,'8.24.10.14','Google Chrome','domain0.com','2017-08-18 18:58:13'),
(10,33609,'8.24.10.14','Google Chrome','domain1.com','2017-08-18 19:06:44'),
(11,33610,'8.24.10.14','Google Chrome','www.domain0.com','2017-08-18 19:06:44'),
(12,33611,'7.10.14.11','Google Chrome','domain2.com','2017-08-18 19:12:48'),
(13,33612,'7.10.14.11','Google Chrome','www.domain2.com','2017-08-18 19:12:48'),
(14,33613,'9.45.21.18','Google Chrome','domain3.com','2017-08-18 19:18:10');

现在我需要选择使用了多个域的任何 IP，对于此示例，它是：8.24.10.14 因为它使用了两个域：domain0 和 domain1

我该怎么做？

非常感谢

mysql
Martin Hope
Fanar Web
Asked: 2017-06-01 11:26:31 +0800 CST
  • 0

我正在尝试修改我的报告 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