菜鸟sql记录

-- 记录不同用户的胜负情况

SELECT A.name, 

 SUM(CASE WHEN B.result = '胜' THEN 1 ELSE 0 END) AS 胜,
 SUM(CASE WHEN B.result = '负' THEN 1 ELSE 0 END) AS 负 
 FROM test_a A LEFT JOIN test_b B ON A.user_id = B.user_id GROUP BY A.name
  
  -- 查询单表记录条数大于等于2条的数据
 SELECT * FROM g_cardapply gc WHERE EXISTS(SELECT * FROM g_cardapply g WHERE gc.`g_applyno` = g.`g_applyno` GROUP BY g.`g_applyno` HAVING COUNT(g.`g_applyno`) >= 2 )
 
 -- 同时修改2表数据 
UPDATE g_cardapply gc INNER JOIN g_cardapplydetail gcd ON gc.`g_applyno` = gcd.`g_applyno`
SET gc.`g_state` = '08', gcd.`g_name`='张三' WHERE gcd.`g_idcard` = '440401430103083'


-- 查每年每个季度的销售额(按季度划分)
 SELECT sales_year 年份,
 SUM(CASE WHEN s.sales_quarter = '1' THEN sales_room ELSE 0 END) AS 一季度,
 SUM(CASE WHEN s.sales_quarter = '2' THEN s.sales_room ELSE 0 END) AS 二季度,
 SUM(CASE WHEN s.sales_quarter = '3' THEN s.sales_room ELSE 0 END) 三季度,
 SUM(CASE WHEN s.sales_quarter = '4' THEN s.sales_room ELSE 0 END) 四季度
 FROM test_sales s GROUP BY s.sales_year
 
 -- 多行合并成一列(按季度合并成一列)
 SELECT sales_year,sales_quarter_one 销售额,CASE WHEN sales_quarter_one THEN 1 ELSE 0 END 季度  FROM test_sales2
 UNION SELECT sales_year, sales_quarter_two 销售额,CASE WHEN sales_quarter_two THEN 2 ELSE 0 END 季度 FROM test_sales2
 UNION SELECT sales_year, sales_quarter_three 销售额,CASE WHEN sales_quarter_three THEN 3 ELSE 0 END 季度 FROM test_sales2
 UNION SELECT sales_year, sales_quarter_four 销售额,CASE WHEN sales_quarter_four THEN 4 ELSE 0 END 季度 FROM test_sales2
 
--------------------------------------------------------------------------------------
CREATE TABLE `test_a` (
  `user_id` VARCHAR(20) DEFAULT NULL COMMENT 'user_id',
  `name` VARCHAR(20) DEFAULT NULL COMMENT 'name'
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='test_a'


CREATE TABLE `test_b` (
  `key_id` VARCHAR(20) DEFAULT NULL COMMENT 'key_id',
  `user_id` VARCHAR(20) DEFAULT NULL COMMENT 'user_id',
  `result` VARCHAR(20) DEFAULT NULL COMMENT 'result'
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='test_b'


CREATE TABLE g_cardapply
  (
    g_applyno VARCHAR(8) DEFAULT NULL COMMENT '申请单号',
    g_applydate  BIGINT(8) DEFAULT NULL COMMENT '申请日期',
    g_state VARCHAR(2) DEFAULT NULL COMMENT '申请状态',
    PRIMARY KEY (g_applyno)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='g_cardapply'


CREATE TABLE g_cardapplydetail
  (
    g_applyno VARCHAR(8) DEFAULT NULL COMMENT '申请单号',
    g_name VARCHAR(30) DEFAULT NULL COMMENT '申请人姓名',
    g_idcard VARCHAR(18) DEFAULT NULL COMMENT '申请人身份证号',
    g_state VARCHAR(2) DEFAULT NULL COMMENT '申请状态', 
    PRIMARY KEY (g_applyno)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='g_cardapplydetail'


CREATE TABLE test_sales
  (
    id INT DEFAULT NULL COMMENT '主键id',
    sales_year VARCHAR(30) DEFAULT NULL COMMENT '年份',
    sales_quarter VARCHAR(18) DEFAULT NULL COMMENT '季度',
    sales_room INT DEFAULT NULL COMMENT '销售额', 
    PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='销售信息表'


CREATE TABLE test_sales2
  (
    id INT DEFAULT NULL COMMENT '主键id',
    sales_year VARCHAR(30) DEFAULT NULL COMMENT '年份',
    sales_quarter_one INT DEFAULT NULL COMMENT '一季度',
    sales_quarter_two INT DEFAULT NULL COMMENT '二季度',
    sales_quarter_three INT DEFAULT NULL COMMENT '三季度',
    sales_quarter_four INT DEFAULT NULL COMMENT '四季度',
    PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='销售信息表2'

--------------------------------------------------------------------------------------

-- 表数据插入

insert into `` (`sales_year`, `销售额`, `季度`) values('1991','11','1');
insert into `` (`sales_year`, `销售额`, `季度`) values('1992','21','1');
insert into `` (`sales_year`, `销售额`, `季度`) values('1991','12','2');
insert into `` (`sales_year`, `销售额`, `季度`) values('1992','22','2');
insert into `` (`sales_year`, `销售额`, `季度`) values('1991','13','3');
insert into `` (`sales_year`, `销售额`, `季度`) values('1992','23','3');
insert into `` (`sales_year`, `销售额`, `季度`) values('1991','14','4');
insert into `` (`sales_year`, `销售额`, `季度`) values('1992','24','4');

-----------

insert into `test_b` (`key_id`, `user_id`, `result`) values('0001','1000','胜');

insert into `test_b` (`key_id`, `user_id`, `result`) values('0002','1001','胜');
insert into `test_b` (`key_id`, `user_id`, `result`) values('0003','1001','负');
insert into `test_b` (`key_id`, `user_id`, `result`) values('0004','1000','负');
insert into `test_b` (`key_id`, `user_id`, `result`) values('0005','1000','胜');

----------

insert into `g_cardapply` (`g_applyno`, `g_applydate`, `g_state`) values('1','20170901','07');
insert into `g_cardapply` (`g_applyno`, `g_applydate`, `g_state`) values('2','20170808','08');
insert into `g_cardapply` (`g_applyno`, `g_applydate`, `g_state`) values('3','20170707','07');
insert into `g_cardapply` (`g_applyno`, `g_applydate`, `g_state`) values('1','20170606','07');
insert into `g_cardapply` (`g_applyno`, `g_applydate`, `g_state`) values('2','20170505','08');

------------

insert into `g_cardapplydetail` (`g_applyno`, `g_name`, `g_idcard`, `g_state`) values('1','aa','440401430103082','1');
insert into `g_cardapplydetail` (`g_applyno`, `g_name`, `g_idcard`, `g_state`) values('2','张三','440401430103083','1');
insert into `g_cardapplydetail` (`g_applyno`, `g_name`, `g_idcard`, `g_state`) values('3','cc','440401430103084','1');

-------------

insert into `test_sales` (`id`, `sales_year`, `sales_quarter`, `sales_room`) values('1','1991','1','11');
insert into `test_sales` (`id`, `sales_year`, `sales_quarter`, `sales_room`) values('2','1991','2','12');
insert into `test_sales` (`id`, `sales_year`, `sales_quarter`, `sales_room`) values('3','1991','3','13');
insert into `test_sales` (`id`, `sales_year`, `sales_quarter`, `sales_room`) values('4','1991','4','14');
insert into `test_sales` (`id`, `sales_year`, `sales_quarter`, `sales_room`) values('5','1992','1','21');
insert into `test_sales` (`id`, `sales_year`, `sales_quarter`, `sales_room`) values('6','1992','2','22');
insert into `test_sales` (`id`, `sales_year`, `sales_quarter`, `sales_room`) values('7','1992','3','23');
insert into `test_sales` (`id`, `sales_year`, `sales_quarter`, `sales_room`) values('8','1992','4','24');

------------

insert into `test_sales2` (`id`, `sales_year`, `sales_quarter_one`, `sales_quarter_two`, `sales_quarter_three`, `sales_quarter_four`) values('1','1991','11','12','13','14');
insert into `test_sales2` (`id`, `sales_year`, `sales_quarter_one`, `sales_quarter_two`, `sales_quarter_three`, `sales_quarter_four`) values('2','1992','21','22','23','24');