sql面试题

-- 建表
DROP TABLE IF EXISTS `gcode`;
CREATE TABLE `gcode`  (
  `gcode` int(255) NULL DEFAULT NULL COMMENT '商品id',
  `gname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '商品名称',
  `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '分类'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;


-- ----------------------------
-- Table structure for purorder
-- ----------------------------
DROP TABLE IF EXISTS `purorder`;
CREATE TABLE `purorder`  (
  `id` int(11) NOT NULL COMMENT '主键id',
  `gcode` int(255) NULL DEFAULT NULL COMMENT '商品id',
  `qty` int(11) NULL DEFAULT NULL COMMENT '采购数量',
  `rmb` decimal(65, 0) NULL DEFAULT NULL COMMENT '采购金额rmb',
  `odate` date NULL DEFAULT NULL COMMENT '采购日期',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;


-- ----------------------------
-- Table structure for salorder
-- ----------------------------
DROP TABLE IF EXISTS `salorder`;
CREATE TABLE `salorder`  (
  `id` int(11) NOT NULL COMMENT '主键id',
  `gcode` int(255) NULL DEFAULT NULL COMMENT '商品id',
  `qty` int(11) NOT NULL COMMENT '销售数量',
  `rmb` decimal(20, 0) NULL DEFAULT NULL COMMENT '销售金额',
  `odate` date NOT NULL COMMENT '销售日期',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;

-- 1
SELECT * FROM SALORDER  SA   JOIN  GCODE GC  ON  GC.GCODE=SA.GCODE  WHERE gc.gname='商品1' ;


-- 2  id, gc.gcode,qty,rmb,odate,gname,type
select id, gc.gcode,qty,rmb,odate,gname,type from 
		PURORDER  PU  , GCODE GC  ,
		(SELECT  count(*) as sum  FROM PURORDER  PU  JOIN GCODE GC ON GC.GCODE=PU.GCODE 
					WHERE GC.TYPE='A001' AND DATE_FORMAT(ODATE,'%Y-%m-%d')>='2022-01-01' ) a
	WHERE GC.GCODE=PU.GCODE  and GC.TYPE='A001' AND DATE_FORMAT(ODATE,'%Y-%m-%d')>='2022-01-01'
	AND a.sum >=2


-- 3. 

select g.*, inventory
from gcode g,
     (select gcode, sum(pqty) - sum(sqty) inventory
      from (
               select p.gcode gcode, p.qty pqty, s.qty sqty
               from purorder p,
                    salorder s
               where p.gcode = s.gcode
                 and date_format(p.odate, '%Y-%m-%d') <= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d')
                 and date_format(s.odate, '%Y-%m-%d') <= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d')) a
      group by gcode) b
where b.inventory >= 1000
  and g.gcode = b.gcode;
	
	
select g.*, inventory
from gcode g,
     (select gcode, sum(pqty) - sum(sqty) inventory
      from (
               select p.gcode gcode, p.qty pqty, s.qty sqty
               from purorder p,
                    salorder s
               where p.gcode = s.gcode
                 and date_format(p.odate, '%Y-%m-%d') <= DATE_SUB(SYSDATE(), INTERVAL 1 DAY)
                 and date_format(s.odate, '%Y-%m-%d') <= DATE_SUB(SYSDATE(), INTERVAL 1 DAY)) a
      group by gcode having  sum(pqty) - sum(sqty)>=1000) b
where  g.gcode = b.gcode;


--  4.
select '采购' as 'name', a.aa as '数量', a.rmb as '价格', g.*
from gcode g,
(select p.gcode, sum(p.rmb) rmb, sum(p.qty) aa from purorder p group by p.gcode   order by aa desc limit 10 ) a 
where  a.gcode =  g.gcode 
union all
select  '销售' as 'name',  b.bb as '数量' ,  b.rmb as '价格', g.*
from gcode g,
(select s.gcode, sum(s.rmb) rmb, sum(s.qty) bb from salorder s group by s.gcode order by bb desc limit 10  ) b 
where  b.gcode = g.gcode;



select * from (
select '采购' name, p.gcode, sum(p.qty) pqty, sum(p.rmb) prmb, @rownum := @rownum + 1 rink
from purorder p,
     (select @rownum := 0) b
group by p.gcode
order by pqty desc
limit 10  ) a 
union 
select * from(
select '销售' name, p.gcode, sum(p.qty) pqty, sum(p.rmb) prmb, @rownum02 := @rownum02 + 1 rink
from salorder p,
     (select @rownum02 := 0) b
group by p.gcode
order by pqty desc
limit 10 ) b;