如何使用mysql查询获取所需的resullt [英] how to get required resullt with mysql query
问题描述
输出应该是
< code>
KU&NBSP;&NBSP; &NBSP;电气NBSP;&NBSP;
&NBSP;&NBSP;&NBSP;
是 &NBSP; 6&NBSP;&NBSP; &NBSP; 2&NBSP;&NBSP;
否 &NBSP; 1&NBSP;&NBSP; &NBSP; 2&NBSP;&NBSP;
< / code>在这个KU和Electrical是我的销售渠道名称。是的意味着反对KU的肠痈 没有没有进入的手段。请帮帮忙。我正在苦苦挣b
< code>
`选择SalesChannel.name，Transaction.category_id，count（Transaction.category_id）作为来自网点的数量Outlet内部联接交易Outlet上的交易.id = Transaction.outlet_id内部联盟sale_channels SalesChannel on SalesChannel.id = Outlet.sale_channel_id
group by Transaction.category_id`< / code>
以下是我用过的三张桌子。
1.交易
<代码>
; CREATE TABLE如果不存在`交易`（
`id`int（11）NOT NULL，
;`zone_id` int（11）NOT NULL，
`state_id` int（11）NOT NULL，
; `city_id` int（11）NOT NULL，
`category_id` int（11）NOT NULL，
`sub_category_id` int（11）NOT NULL，
`brand_id` int（11）NOT NULL ，
`model_id` int（11）NOT NULL，
`outlet_id` int（11）NOT NULL，
`no_of_units` int（11）NOT NULL，
; `mop` decimal（10,2）NOT NULL
）ENGINE = InnoDB AUTO_INCREMENT = 5 DEFAULT CHARSET = latin1;
&NBSP;
-
- 转储表"交易"的数据
-
INSERT INTO`traffic`（`id`，`zone_id`，`state_id`，`city_id`，`category_id`，`sub_category_id`，`brand_id`，`model_id`，`outlet_id`，`no_of_units`，`mop`）价值
（1,2,2,2,1,1,1,1,3,3'，6.00'），
（2,2,2,2,1,1,1,1,3,3'，6.00'），
（3,1,1,1,1,1,1,1,1,4,4''2.00'），
（4,2,2,2,1,1,1,1,2,4，'2.00'）;
< / code>
2.outlets
< code>
创建表如果不存在`outlet`（
`id`int（11）NOT NULL，
;`outlet_code` varchar（255）NOT NULL，
`name` varchar（255）NOT NULL，
; `zone_id` int（11）NOT NULL，
`state_id` int（11）NOT NULL，
`city_id` int（11）NOT NULL，
`sale_channel_id` int（11）NOT NULL ，
`is_active` tinyint（1）NOT NULL，
`created` datetime NOT NULL，
`modified` datetime NOT NULL
）ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = latin1;
-
- 转储表`outlet`的数据
-
INSERT INTO`outters`（`id`，`outlet_code`，`name`，`zone_id`，`state_id`，`city_id`，`sale_channel_id`，`is_active`，`created`，`modified`）VALUES
（1，'1508'，'Ashok electricals'，2,2,2,1,1，'2016-10-03 00:00:00'，'2016-10-03 00:00:00'），
（2，'1233'，'vinayak electricals'，1,1,1,2,1，'2016-10-04 00:00:00'，'2016-10-04 00:00:00'）;
< / code>
3. sale_chennals
< code>
CREATE TABLE IF NOT NOT EXISTS`sales_channels`（
`id`int（11）NOT NULL，
;`name` varchar（255）NOT NULL，
`is_active` tinyint（1）NOT NULL，
; `created` datetime NOT NULL，
`modified` datetime NOT NULL
）ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = latin1;
-
- 转储表'sale_channels`的数据为
-
INSERT INTO`sales_channels`（`id`，`name`，`is_active`，`created`，`modified`）VALUES
（1，'KU'，1，'2016-10-03 00:00:00'，'2016-10-03 00:00:00'），
（2，'Electricals'，1，'2016-10-04 00:00:00'，'2016-10-04 00:00:00'）;
我有 mysql 查询，如下所示。
你好，
这是一个Microsoft SQL Server论坛，不适用于MySQL =>
https：// forums。 mysql .com /
Hi experts , I have mysql query given below. I which counter has been used. if i enter category Id 1 for 3 times then counter is coming 3 which is correct but with this i want if i do not enter then different coloumn should come with NO.
output should be
<code>
KU Electrical
Yes 6 2
No 1 2
</code> In this KU and Electrical are my sale channel name. Yes means counter of enteries of KU and No means which have not entered. Please help out in this. i am struggling
<code>
`select SalesChannel.name , Transaction.category_id, count(Transaction.category_id) as count from outlets Outlet inner join transactions Transaction on Outlet.id = Transaction.outlet_id inner join sale_channels SalesChannel on SalesChannel.id = Outlet.sale_channel_id
group by Transaction.category_id`</code>
below are three tables which i used
1. transactions
<code>
CREATE TABLE IF NOT EXISTS `transactions` (
`id` int(11) NOT NULL,
`zone_id` int(11) NOT NULL,
`state_id` int(11) NOT NULL,
`city_id` int(11) NOT NULL,
`category_id` int(11) NOT NULL,
`sub_category_id` int(11) NOT NULL,
`brand_id` int(11) NOT NULL,
`model_id` int(11) NOT NULL,
`outlet_id` int(11) NOT NULL,
`no_of_units` int(11) NOT NULL,
`mop` decimal(10,2) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `transactions`
--
INSERT INTO `transactions` (`id`, `zone_id`, `state_id`, `city_id`, `category_id`, `sub_category_id`, `brand_id`, `model_id`, `outlet_id`, `no_of_units`, `mop`) VALUES
(1, 2, 2, 2, 2, 1, 1, 1, 1, 3, '6.00'),
(2, 2, 2, 2, 2, 1, 1, 1, 1, 3, '6.00'),
(3, 1, 1, 1, 1, 1, 1, 1, 1, 4, '2.00'),
(4, 2, 2, 2, 1, 1, 1, 1, 2, 4, '2.00');
</code>
2.outlets
<code>
CREATE TABLE IF NOT EXISTS `outlets` (
`id` int(11) NOT NULL,
`outlet_code` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`zone_id` int(11) NOT NULL,
`state_id` int(11) NOT NULL,
`city_id` int(11) NOT NULL,
`sale_channel_id` int(11) NOT NULL,
`is_active` tinyint(1) NOT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `outlets`
--
INSERT INTO `outlets` (`id`, `outlet_code`, `name`, `zone_id`, `state_id`, `city_id`, `sale_channel_id`, `is_active`, `created`, `modified`) VALUES
(1, '1508', 'Ashok electricals', 2, 2, 2, 1, 1, '2016-10-03 00:00:00', '2016-10-03 00:00:00'),
(2, '1233', 'vinayak electricals', 1, 1, 1, 2, 1, '2016-10-04 00:00:00', '2016-10-04 00:00:00');
</code>
3. sale_chennals
<code>
CREATE TABLE IF NOT EXISTS `sale_channels` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`is_active` tinyint(1) NOT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `sale_channels`
--
INSERT INTO `sale_channels` (`id`, `name`, `is_active`, `created`, `modified`) VALUES
(1, 'KU', 1, '2016-10-03 00:00:00', '2016-10-03 00:00:00'),
(2, 'Electricals', 1, '2016-10-04 00:00:00', '2016-10-04 00:00:00');
I have mysql query given below.
Hello,
This is a Forum for Microsoft SQL Server, not for MySQL => https://forums.mysql.com/
这篇关于如何使用mysql查询获取所需的resullt的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
