if-else语句

z5btuh9x 于 2021-06-24 发布在 Mysql

关注(0)|答案(2)|浏览(411)

我有下面写的查询，如果我的计数器来了，那么应该有显示是，如果没有值来了，那么将没有。请看看查询。输出应如下所示

KU      Electrical  
Yes   6       2  
No    1       2

6是ku的计数器，yes表示ku的存在，同样no表示ku的不存在

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 SalesChannel.name

下面是我用过的三张table
交易

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');

2.出口

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');

销售额

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');

mysql if-statement

来源：https://stackoverflow.com/questions/39870023/if-else-statement-in-select-query

2条答案

按热度按时间

bvjveswy1#

您可以在查询中使用“case”
选择salesschannel.name，transaction.category\u id，count（transaction.category\u id）作为count，case when count（transaction.category\u id）>0，然后选择“yes”，否则选择“no”作为ku
从outlets outlet inner join transactions transaction on outlet.id=transaction.outlet\u id inner join sale\u channels salesschannel on salesschannel.id=outlet.sale\u channel\u id group by salesschannel.name

赞(0）回复(0）举报 2021-06-24

xriantvc2#

我在outlets表中添加了一个额外的行，因此下面的查询无法找到事务(3，'3333'，'电气'，1，1，1，2，1，'2016-10-04 00:00:00'，'2016-10-04 00:00:00'；

select  t.srce
            ,sum(case when s.name = 'KU' then 1 else 0 end) KU
            ,sum(case when s.name = 'Electricals' then 1 else 0 end) Electricals
from
(
select 'yes' srce, o.id,  o.sale_channel_id, t.category_id  
from outlets o 
join transactions T on O.id = T.outlet_id 
union all
select 'no' srce, o.id, o.sale_channel_id, t.category_id  
from outlets o 
left outer join transactions T on O.id = T.outlet_id
 where t.outlet_id is null
 ) t

 JOIN  sale_channels s on  t.sale_channel_id = s.id

 group by t.srce
 order by case
                when t.srce = 'yes' then 1 
                 else 2
             end;

内部子查询使用join获取有事务的outlet，使用outer join/null方法获取没有事务的outlet。外部查询然后使用条件聚合生成输出
结果

+------+------+-------------+
| srce | KU   | Electricals |
+------+------+-------------+
| yes  |    3 |           1 |
| no   |    0 |           1 |
+------+------+-------------+

如果您有未知数量的销售渠道，则可能需要使用动态sql。

set @sumstr = 
(select str from
(
select @rn:=@rn + 1 rn,@str:=concat(@str,'sum(case when s.name=',char(39),s.name,char(39),' then 1 else 0 end) as ', s.name, ',' ) str
from (select @rn:=0,@str:='') str,sale_channels s
) s
order by rn desc limit 1
);

select concat(
'Select t.srce, ',substr(@sumstr,1,length(@sumstr) -1) ,
' from
(
select ', 
concat(char(39),char(121),char(101),char(115),char(39)) , 
' srce, o.id,  o.sale_channel_id, t.category_id  
from outlets o 
join transactions T on O.id = T.outlet_id 
union all
select ' , 
concat(char(39),char(110),char(111),char(39))  ,
' srce, o.id, o.sale_channel_id, t.category_id  
from outlets o 
left outer join transactions T on O.id = T.outlet_id
 where t.outlet_id is null
 ) t
 JOIN  sale_channels s on  t.sale_channel_id = s.id
 group by t.srce
 order by case
                when t.srce = ' ,
                     concat(char(39),char(121),char(101),char(115),char(39)) ,' then 1 
                 else 2
             end;   
'
)
;

赞(0）回复(0）举报 2021-06-24

我来回答

if-else语句

2条答案

相关问题

热门标签

最新问答