数据库

数据库

Day1

数据库系统
1. server <——> cilent
2. 实例
  1. 数据库
    1. schema
      1. 表
        
        字段

一个数据库可以有多个实例 MySQL、DB2 Instance ——> 数据库缺省数据库
写库的前缀

  1. 字符型    char / varchar
  1. 数值    int / bigint / smnint    精度不同   占用空间不同
  1. 逻辑
  1. 日期   date / datesteamp time   精度不同

聚合函数
窗口函数https://zhuanlan.zhihu.com/p/92654574
https://www.cnblogs.com/airen123/p/11015200.html

https://www.cnblogs.com/miracle-luna/p/11832595.html

https://blog.csdn.net/qq_34412985/article/details/113326535?ops_request_misc=%7B%22request%5Fid%22%3A%22163521922916780271596312%22%2C%22scm%22%3A%2220140713.130102334.pc%5Fall.%22%7D&request_id=163521922916780271596312&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2allfirst_rank_ecpm_v1~rank_v31_ecpm-1-113326535.pc_search_result_cache&utm_term=MySQL%E4%B8%AD%E5%B0%86%E5%AD%97%E7%AC%A6%E4%B8%B2%E8%BD%AC%E4%B8%BA%E6%95%B4%E6%95%B0&spm=1018.2226.3001.4187

mysql:

# mysql 字符串转日期
select  date_format(now(),'%Y-%m-%d'); 
# 时间转时间戳
select unix_timestamp(now()); 
# 字符串转时间
select str_to_date('2020-02-20','%Y-%m-%d %H');
# 字符串转时间戳
select unix_timestamp('2020-02-20');
# 时间戳转时间
select from_unixtime('1582128000');
# 时间戳转字符串
select from_unixtime('1582128000','%Y-%m-%d');
# 字符串和数字的互相转换
# 1.直接在字符串后面+0
select '123'+0+'111';
# 2.CAST()，使用方式为CAST(value AS type);
select cast('5' as signed);
select cast('5.24' as decimal(9,2));  # 9和2分别代表精度和小数点后位数
# 3.CONVERT()，使用CONVERT(value, type);    SELECT CONVERT('67',SIGNED); 
select convert('67',signed);
select convert('67.35',decimal(9,2));
# 将数字转化为字符串
# 1.通用“+''”
select 111+'';
# 2.convert()
select convert(25,char);

数值类型：

字符串类型

类型	用途
char	定长字符串
vachar	变长字符串
tinyblob	不超过 255 个字符的二进制字符串
tinytext	短文本字符串
blob	二进制形式的长文本数据
text	长文本
mediumblob	二进制形式的中等长度文本数据
mediumtext	中等长度文本数据
longblob	二进制形式的极大文本数据
longtext	极大文本数据

数值

类型	大小(Bytes)	用途
tinyint	1	小整数型
smallint	2	大整数型
mediumint	3	大整数型
int/integer	4	大整数型
bigint	8	极大整数型
float	4	单精度浮点数值
double	8	双精度浮点数值
decimal	对于decimal(m,d),如果M>D,为M+2否则为D+2	小数值

日期和时间类型

类型	格式	用途
date	YYYY-MM-DD	日期值
time	HH:MM:SS	时间值或持续时间
year	YYYY	年份值
datetime	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

https://www.runoob.com/mysql/mysql-data-types.html

多表连接

MySQL主要分三种

inner join

内连接或者等值连接

内连接：SELECT * from a INNER JOIN b on a.name=b.id;

数据库 - 图1

left join

左连接：SELECT * from a left JOIN b on a.name=b.id;

数据库 - 图2

rigth join

SELECT * from a right JOIN b on a.name=b.id;

数据库 - 图3

全连接：mysql不支持全连接（full join），可以使用 left join+ union+right join

(SELECT from a left JOIN b on a.name=b.id) UNION (SELECT from a RIGHT JOIN b on a.name=b.id );

多表连接、MySQL多表关联查询

[数据库和实例的区别]

数据库是一个逻辑上的概念，简单的说就是相互关联的一块数据。而对应到实际的物理概念上，就是磁盘上的一个或者一堆文件，里边包含着数据。

实例就是指的操作系统中一系列的进程以及为这些进程所分配的内存块，而它服务的对象就是数据库。简单说就是实例是我们访问数据库的通道。读取数据时都是先读取到内存中，然后再提交给用户。

不同的数据库实例的服务模式会有略微不同。在Oracle中，非RAC的数据库：一个数据库只能有一个实例为它提供服务；而对于RAC数据库：一个数据库可以有多个实例访问。而所有实例每次只能连接一个数据库。

聚合函数

在一个行的集合（一组行）上进行操作，对每个组给一个结果。

名称	作用
avg	求平均值
count	统计行的数量
max	求最大值
min	求最小值
sum	求累加和

MySQL最常用分组聚合函数

①每个组函数接收一个参数
②默认情况下，组函数忽略列值为null的行，不参与计算
③有时，会使用关键字distinct剔除字段值重复的条数

Day2

查询

子查询

CREATE table if not EXISTS subquery.customers (
    `cust_id` int unsigned auto_increment,
    `cust_name` varchar(50) not null,
    `cust_address` varchar(100) not null,
    `cust_city` varchar(150) not null,
    `cust_state` varchar(20) not null,
    `cust_zip` int not null,
    `cust_country` varchar(25) not null,
    `cust_contact` varchar(40) not null,
    `cust_email` varchar(50) not null,
    primary key (`cust_id`)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci
COMMENT='存储顾客信息';
CREATE table if not EXISTS subquery.orderitems (
    `order_num` int unsigned auto_increment,
    `order_item` int not null,
    `prod_id` varchar(20) not null,
    `quantity` int not null,
    `item_price` float not null,
    primary key (`order_num`)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci
COMMENT='只存储订单信息，无客户信息';
# 增加一个主键
CREATE table if not EXISTS subquery.orders (
    `order_num` int unsigned auto_increment,
    `order_date` datetime,
    `cust_id` int not null,
    primary key (`order_num`)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci
COMMENT='存储订单号和顾客id';
# 添加数据
insert into customers
    (`cust_id`,`cust_name`,`cust_address`,`cust_city`,`cust_state`,`cust_zip`,`cust_country`,`cust_contact`,`cust_email`)
    values
    (10001,'Coyote Inc','200 Maple Lans','Detroit','MI',44444,'USA','Y Lee','ylee@coyote.com'),
    (10002,'Mouse House','333 Fromage Lans','Columbus','OH',43333,'USA','Jerry Mouse',NULL),
    (10003,'Wascals','1 Sunny Place','Muncie','IN',42222,'USA','Jim Jones','rabbit@qq.com'),
    (10004,'Yosemite Place','829 Riverside Drive','Phoenix','AZ',88888,'USA','Y Sam','sam@yo.com'),
    (10005,'E Fudd','4545 53rd Street','Chicago','IL',54545,'USA','E Fudd',NULL);
insert into orderitems
    (`order_num`,`order_item`,`prod_id`,`quantity`,`item_price`)
    values
    (20005,1,'ANV01',10,5.99),
    (20005,2,'ANV02',3,9.99),
    (20005,3,'TNT2',5,10.00),
    (20005,4,'FB',1,10.00),
    (20006,1,'JP2000',1,55.00),
    (20007,1,'TNT2',100,10.00),
    (20008,1,'FC',50,2.50),
    (20009,1,'FB',1,10.00),
    (20009,2,'OL1',1,8.99),
    (20009,3,'SLING',1,4.49),
    (20009,4,'ANV03',1,14.99);
insert into orders
    (`order_num`,`order_date`,`cust_id`)
    values
    (20005,'2005-09-01 00:00:00',10001),
    (20006,'2005-09-12 00:00:00',10003),
    (20007,'2005-09-30 00:00:00',10004),
    (20008,'2005-10-03 00:00:00',10005),
    (20009,'2005-10-08 00:00:00',10001);

注意：一般在子查询中，程序先运行在嵌套在最内层的语句，再运行外层。因此在写子查询语句时，可以先测试下内层的子查询语句是否输出了想要的内容，再一层层往外测试，增加子查询正确率。否则多层的嵌套使语句可读性很低。

查询买了商品为’TNT2’的顾客信息

1. 在orderitems里找出TNT2的订单号

select `order_id` from subquery.orderitems where prod_id = 'TNT2';

2. 在orders里找出第一步找出的订单号对应的客户id

select `cust_id` from subquery.orders where `order_id` in (select `order_id` from subquery.orderitems where prod_id = 'TNT2');

3. 在customers中找出第二步对应客户的所有信息

select * from subquery.customers where `cust_id` in (select `cust_id` from subquery.orders where `order_id` in (select `order_id` from subquery.orderitems where prod_id = 'TNT2'));

子查询一般与IN操作符结合使用，也可用=><等。

想要查询每个客户的订单数

查询某个客户的订单数

select count(*) as order1 from subquery.orders where `cust_id` = 10001;

某个客户改为所有客户。

select `cust_name`,`cust_state`,
        (select count(*) from orders where orders.cust_id = customers.cust_id)
        as order1
    from customers
    order by cust_name;

子查询

explain

profile

聚簇索引

variables

case when

https://www.jb51.net/article/126107.htm

explain

作用
1、表的读取顺序
2、数据读取操作的操作类型
3、哪些索引可以使用
4、哪些索引被实际使用
5、表之间的引用
6、每张表有多少行被优化器查询

-- 实际SQL，查找用户名为Jefabc的员工
select *  from tt_repair_order;
-- 查看SQL是否使用索引，前面加上explain即可
explain select *  from tt_repair_order;

数据库 - 图4

查询出来有十二列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、filtered、Extra

id：选择表示符
select_type：查询的类型
table：输出结果集的表
partition：匹配的分区
type：表示表的连接类型
possible_keys：表示查询时，可能使用的索引
key：表示实际使用的索引
key_len：索引字段的长度
ref：列与索引的比较
rows：扫描出的行数(估算的行数)
filtered：按表条件过滤的行百分比
Extra：执行情况的描述和说明

Explain

MySQL优化——看懂explain

id

SELECT识别符，是SELECT的查询序列号，SQL执行的顺序的标识，SQL从大到小的执行

id相同时，执行顺序由上至下。
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

select_type

查询中每个select子句的类型

SIMPLE（简单select，不使用UNION或者子查询等）
PRIMARY（子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY）
UNION（UNION中第二个或后面的SELECT语句）
DEPENDENT UNION（UNION中第二个或后面的SELECT语句，取决于外面的查询）
UNION RESULT（UNION的结果，union语句中第二个select开始后面所有select）
SUBQUERY（子查询中的第一个select，结果不依赖于外部查询）
DEPENDENT SUBERY（子查询中的第一个SELECT，依赖于外部查询）
DERIVED（派生表的SELECT，FROM子句的子查询）
UNCACHEABLE SUBQUERY（一个子查询的结果不能被缓存，必须重新评估外链接的第一行）

table

显示这一步所访问数据库中表名称，输出的行所引用的表。

type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有：ALL、index、range、ref、eq_ref、const、system、NULL（从左往右，性能从差到好）

ALL：Full Table Scan，MySQL将遍历全表以找到匹配的行
index：Full Index Scan，index与ALL区别为index类型只遍历索引树
range：只检索给定范围的行，使用一个索引来选择行
ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到字段上若存在索引，则该索引将被引出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示为null）

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中存在某些键实际上不能按生成的表次序使用。

如果该列为null，则没有相关索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

key

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

不损失精确性的情况下，长度越短越好

ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

-- 测试Extra的filesort
explain select * from emp order by name;

Using join buffer：该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
No tables used：Query语句中使用from dual 或不含任何from子句

-- explain select now() from dual;

总结：
• EXPLAIN不会告诉关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

通过收集统计信息不可能存在结果

Show

https://www.cnblogs.com/saneri/p/6963583.html

profile

https://www.cnblogs.com/flzs/p/9974822.html

https://www.cnblogs.com/developer_chan/p/9231761.html

case when

行转列 case when 列转行 union

https://www.jianshu.com/p/1bfddf9fd91e

https://blog.csdn.net/u013514928/article/details/80969949

https://blog.csdn.net/rocling/article/details/82083332

https://www.cnblogs.com/chenduzizhong/p/9590741.html

https://www.cnblogs.com/xiaoxi/p/7151433.html

Day3

行转列 case when 列转行 union

cdk ——> spark

数据结构、数据类型、数据组合

行转列，列转行

select 
  sum( if(xb='男','1','0')  ) as '男',
  sum( if(xb='女','1','0') ) as '女',
  count(1) as total
from testuser t
select 
    sum(case xb when '男' then 1 else 0 end ) as '男',
    sum(case xb when '女' then 1 else 0 end ) as '女',
    count(1) as total
    from testuser t 
# 也可以通过where
SELECT 
    COUNT(if(xb LIKE "男",xb,NULL)) as '男',
    COUNT(if(xb LIKE "女",xb,NULL)) as '女',
    COUNT(xb) as '总计'
FROM person;
SELECT
    COUNT(CASE xb when '男' THEN '男' ELSE NULL END) as '男',
    COUNT(CASE xb when '女' THEN '女' ELSE NULL END) as '女',
    COUNT(xb) as '总计'
FROM person;
SELECT id,area as '地区',sales1 as '销量' FROM sales
UNION ALL
SELECT id,area as '地区',sales2 as '销量' FROM sales；
select id,area as '地区' ,
        group_concat(sales1,"+",sales2,"=",sales1+sales2) as '销量(销量1,销量2,总销量)'
from sales group by id;

https://blog.csdn.net/LOOCOO225/article/details/110389424

Day4

数据库 - 图5

#6
select
    if (left(PPARTNO,1) in ('B','M','E','C','N'),substring(PPARTNO,2,2),(
        if (PPARTNO like 'Z9908%LGL',99,left(PPARTNO,2))))
    as MAIN_GROUP
from m_42439_tm_part_stock_card_5;

数据库 - 图6

#7
SELECT CASE
    WHEN GROUP1 LIKE 'B%' or 'M%' or 'E%' or 'C%' THEN SUBSTRING(PPARTNO,4,2)
    WHEN PPARTNO LIKE 'Z9908%LGL' THEN SUBSTRING(PPARTNO,4,2)
    ELSE SUBSTRING(PPARTNO,3,2)
END  AS SUB_GROUP
FROM m_42439_tm_part_stock_card_5;

数据库 - 图7

#8
select case
    when GROUP1 like 'B%' or '%M' or 'E%' or 'C%' then substring(PPARTNO,6,7)
    when PPARTNO like 'Z9908%LGL' then substring(PPARTNO,6,7)
    else substring(PPARTNO,5,7)
end as PART_NUM 
from m_42439_tm_part_stock_card_5;

数据库 - 图8

#9
SELECT CASE
    WHEN GROUP1 LIKE 'B%' or 'M%' or 'E%' or 'C%' THEN  '50001000' + SUBSTRING(GROUP1,2,2)
    WHEN PPARTNO LIKE 'Z9908%LGL' THEN '50001012'
    ELSE '50001016'
END AS PART_TYPE
FROM m_42439_tm_part_stock_card_5;

数据库 - 图9

#11
SELECT 
    LEFT(PPARTNO,1) as BRAND_CODE
FROM m_42439_tm_part_stock_card_5;

数据库 - 图10

#12
SELECT PART_NO,sum((AVGCOST*TOTALQTY))/(case when sum(TOTALQTY)=0 then 1 else sum(TOTALQTY) end)  AS AVERAGE_COST
FROM m_42439_tm_part_stock_card_5 GROUP BY PART_NO;

数据库 - 图11

#13
SELECT LASTCOST  AS LATEST_COST
FROM m_42439_tm_part_stock_card_5
WHERE LASTBUY IN
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图12

#14
SELECT PRICE1 AS RECOMMEND_PRICE
FROM m_42439_tm_part_stock_card_5
WHERE LASTBUY IN
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图13

#15
SELECT CASE
    WHEN GROUP1 LIKE 'B%' or 'M%' or 'E%' or 'C%' THEN price2
    WHEN PPARTNO LIKE 'Z9908%LGL' THEN price2
    ELSE price2/(case when trim(pfactor)=0 then 1 else pfactor end)
END AS BBA_COST
FROM m_42439_tm_part_stock_card_5;

数据库 - 图14

#16
SELECT ROUND(PRICE1*1.13,4) AS TAX_PRICE
FROM  m_42439_tm_part_stock_card_5;

数据库 - 图15

#17
SELECT NLPRICE AS LAST_RECOMMEND_PRICE
FROM  m_42439_tm_part_stock_card_5
WHERE LASTBUY IN 
(SELECT max(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图16

#18
SELECT NLPRICE AS LAST_RCMD_DATE
FROM m_42439_tm_part_stock_card_5
WHERE LASTBUY IN 
(SELECT max(LASTBUY) FROM m_42439_tm_part_stock_card_5 group by `PART_NO`);

数据库 - 图17

#21
SELECT NLDATE AS LAST_RCMD_DATE
FROM m_42439_tm_part_stock_card_5
WHERE LASTBUY IN 
(SELECT max(LASTBUY) FROM m_42439_tm_part_stock_card_5 group by `PART_NO`);

数据库 - 图18

#22
SELECT TOTALQTY AS TOTAL_STOCK_QTY
FROM  m_42439_tm_part_stock_card_5
WHERE LASTBUY IN 
(SELECT max(LASTBUY) FROM m_42439_tm_part_stock_card_5 group by `PART_NO`);

数据库 - 图19

#23
SELECT MAXIMUM AS MAX_QTY
FROM  m_42439_tm_part_stock_card_5
WHERE LASTBUY IN 
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 group by `PART_NO`);
#24
SELECT MINIMUM AS MIN_QTY
FROM  m_42439_tm_part_stock_card_5
WHERE LASTBUY IN 
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 group by `PART_NO`);

数据库 - 图20

#26
SELECT SUM(TOTALQTY) AS TOTAL_AVAILABLE_QTY
FROM m_42439_tm_part_stock_card_5 GROUP BY PART_NO;

数据库 - 图21

#29
SELECT SUM(QUANTITY7) AS SCRAP_STORAGE_QTY
FROM m_42439_tm_part_stock_card_5 GROUP BY PART_NO;

数据库 - 图22

#30
SELECT SUM(QUANTITY8) AS CLAIM_STORAGE_QTY
FROM m_42439_tm_part_stock_card_5 GROUP BY PART_NO;

数据库 - 图23

#31
SELECT CASE NOUPDATE
    WHEN 'Y' THEN '10041001'
    WHEN 'N' THEN '10041002'
END AS PROHIBIT_PRICE_UPDATE
FROM  m_42439_tm_part_stock_card_5
WHERE LASTBUY IN 
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图24

#32
SELECT CASE
            WHEN KIT ='Y' AND BALANCE = 'Y' THEN '50011001'
            WHEN KIT ='Y' THEN '50011002'
            ELSE '0'
END AS ASSEMBLY_KIT
FROM  m_42439_tm_part_stock_card_5
    WHERE LASTBUY IN 
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图25

#39
SELECT CASE DISCOUNT
 WHEN 'A' THEN '18'
 WHEN 'B' THEN '20'
 WHEN 'C' THEN '24'
 WHEN 'D' THEN '27'
 WHEN 'E' THEN '31'
 WHEN 'F' THEN '36'
 WHEN 'G' THEN '40'
 WHEN 'H' THEN '45'
 WHEN 'I' THEN '49'
 WHEN 'K' THEN '52'
 WHEN 'P' THEN '5'
 WHEN 'N' THEN '63'
 WHEN 'L' THEN '56'
 WHEN 'M' THEN '58'
 END AS DISCOUNT_CODE
FROM  m_42439_tm_part_stock_card_5
    WHERE LASTBUY IN 
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图26

#40
SELECT SALECODE AS FAMILY_CODE
FROM  m_42439_tm_part_stock_card_5
    WHERE LASTBUY IN 
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图27

#41
SELECT REORDER AS ALOIS_CATEGORY
FROM  m_42439_tm_part_stock_card_5
    WHERE LASTBUY IN 
(SELECTMAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图28

#42
SELECT SUBCAT AS ALOIS_SUBCATEGORY
FROM  m_42439_tm_part_stock_card_5
WHERE LASTBUY IN 
(SELECT max(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图29

#43
SELECT LEFT(MLI1,1) AS REPLENISHMENT_CODE
FROM  m_42439_tm_part_stock_card_5
    WHERE LASTBUY IN 
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图30

#44
SELECT CASE
    WHEN LENGTH(MLI1) = 3 THEN SUBSTRING(MLI1,3,1)
    WHEN LENGTH(MLI1) < 3 THEN NULL
END AS REPLENISHMENT_CODE_OLD
FROM  m_42439_tm_part_stock_card_5
    WHERE LASTBUY IN 
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

https://blog.csdn.net/wangxi06/article/details/115090047

数据库 - 图31

#45
SELECT CASE
    WHEN BALANCE = 'Y' THEN '50021001'
    WHEN BALANCE <> 'Y' THEN '50021002'
END AS IS_INVENTORY
FROM  m_42439_tm_part_stock_card_5
    WHERE LASTBUY IN 
(SELECT max(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图32

#50
SELECT FIRSTMV AS FIRST_PURCHASE_DATE
FROM  m_42439_tm_part_stock_card_5
    WHERE LASTBUY IN 
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

#51
SELECT FIRSTMV AS FIRST_SALES_DATE
FROM  m_42439_tm_part_stock_card_5
    WHERE LASTBUY IN 
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图33

#52
SELECT LASTSOLD AS LAST_SALES_DATE
FROM  m_42439_tm_part_stock_card_5
    WHERE LASTBUY IN 
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图34

#53
SELECT  LASTBUY AS LAST_PURCHASE_DATE
FROM  m_42439_tm_part_stock_card_5
    WHERE LASTBUY IN 
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图35

#54
SELECT MOVED AS LAST_FLOW_DATE
FROM  m_42439_tm_part_stock_card_5
    WHERE LASTBUY IN 
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图36

#55
SELECT  
    IF(SUPER = PPARTNO,null,SUPER) AS NEW_REPLACE_CODE
FROM m_42439_tm_part_stock_card_5;
#56
SELECT
    IF(SUPFROM = PPARTNO,null,SUPFROM) AS OLD_REPLACE_CODE
FROM m_42439_tm_part_stock_card_5;

数据库 - 图37

#60
SELECT LOSTQTY AS SALES_LOSS_QTY
FROM  m_42439_tm_part_stock_card_5
WHERE LASTBUY 
IN (SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图38

#66
SELECT IF(DECIMAL1 = 'Y' ,10041001,10041002) AS IS_DECIMAL_OUT
FROM  m_42439_tm_part_stock_card_5
WHERE LASTBUY IN 
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图39

#67
SELECT DISTINCT  ECONBUY AS MIN_PURCHASE_QTY
FROM  m_42439_tm_part_stock_card_5
WHERE LASTBUY IN 
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图40

#78
SELECT DISTINCT  RAISED AS CREATED_AT
FROM  m_42439_tm_part_stock_card_5
WHERE LASTBUY IN 
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

数据库 - 图41

#81
SELECT DISTINCT CONCAT(`EDITDATE` , `EDITTIME`) AS UPDATED_AT
FROM  m_42439_tm_part_stock_card_5
WHERE LASTBUY in
(SELECT MAX(LASTBUY) FROM m_42439_tm_part_stock_card_5 GROUP BY `PART_NO`);

Day5

SELECT *
FROM (SELECT PART_NO,MAX(LASTBUY) AS LASTBUY FROM m_42439_tm_part_stock_card_5 GROUP BY PART_NO) AS M1,
        m_42439_tm_part_stock_card_5 AS M2
WHERE M1.PART_NO = M2.PART_NO AND M1.LASTBUY = M2.LASTBUY GROUP BY M2.PART_NO;

SELECT M1.PART_NO,
    if (left(PPARTNO,1) in ('B','M','E','C','N'),substring(PPARTNO,2,2),(
        if (PPARTNO like 'Z9908%LGL',99,left(PPARTNO,2))))
    as MAIN_GROUP            -- 6
    ,CASE
        WHEN GROUP1 LIKE 'B%' or 'M%' or 'E%' or 'C%' THEN SUBSTRING(PPARTNO,4,2)
        WHEN PPARTNO LIKE 'Z9908%LGL' THEN SUBSTRING(PPARTNO,4,2)
        ELSE SUBSTRING(PPARTNO,3,2)
    END  AS SUB_GROUP            -- 7
    ,case
        when GROUP1 like 'B%' or '%M' or 'E%' or 'C%' then substring(PPARTNO,6,7)
        when PPARTNO like 'Z9908%LGL' then substring(PPARTNO,6,7)
        else substring(PPARTNO,5,7)
    end as PART_NUM                -- 8
    ,CASE
        WHEN GROUP1 LIKE 'B%' or 'M%' or 'E%' or 'C%' THEN  '50001000' + SUBSTRING(GROUP1,2,2)
        WHEN PPARTNO LIKE 'Z9908%LGL' THEN '50001012'
        ELSE '50001016'
    END AS PART_TYPE        --  9
    ,LEFT(PPARTNO,1) as BRAND_CODE        -- 11
    ,SUM((AVGCOST*TOTALQTY))/(case when sum(TOTALQTY)=0 then 1 else sum(TOTALQTY) end)  AS AVERAGE_COST                -- 12
    ,LASTCOST AS LATEST_COST        -- 13
    ,PRICE1 AS RECOMMEND_PRICE -- 14
    ,CASE
        WHEN GROUP1 LIKE 'B%' or 'M%' or 'E%' or 'C%' THEN price2
        WHEN PPARTNO LIKE 'Z9908%LGL' THEN price2
        ELSE price2/(case when trim(pfactor)=0 then 1 else pfactor end)
    END AS BBA_COST            -- 15
    ,ROUND(PRICE1*1.13,4) AS TAX_PRICE        -- 16
    ,NLPRICE AS LAST_RECOMMEND_PRICE        -- 17
    ,NLPRICE AS LAST_RCMD_DATE            -- 18
    ,NLDATE AS LAST_RCMD_DATE                -- 21
    ,TOTALQTY AS TOTAL_STOCK_QTY        -- 22
    ,MAXIMUM AS MAX_QTY                            -- 23
    ,MINIMUM AS MIN_QTY                            -- 24
    ,SUM(TOTALQTY) AS TOTAL_AVAILABLE_QTY            -- 26
    ,SUM(QUANTITY7) AS SCRAP_STORAGE_QTY        -- 29
    ,SUM(QUANTITY7) AS CLAIM_STORAGE_QTY            -- 30
    ,CASE NOUPDATE
        WHEN 'Y' THEN '10041001'
        WHEN 'N' THEN '10041002'
    END AS PROHIBIT_PRICE_UPDATE            -- 31
    ,CASE
        WHEN KIT ='Y' AND BALANCE = 'Y' THEN '50011001'
        WHEN KIT ='Y' THEN '50011002'
        ELSE '0'
    END AS ASSEMBLY_KIT                            -- 32
    ,CASE DISCOUNT
        WHEN 'A' THEN '18' WHEN 'B' THEN '20' WHEN 'C' THEN '24' WHEN 'D' THEN '27'
        WHEN 'E' THEN '31' WHEN 'F' THEN '36' WHEN 'G' THEN '40' WHEN 'H' THEN '45'
        WHEN 'I' THEN '49' WHEN 'K' THEN '52' WHEN 'P' THEN '5' WHEN 'N' THEN '63'
        WHEN 'L' THEN '56' WHEN 'M' THEN '58'
    END AS DISCOUNT_CODE                         -- 39
    ,SALECODE AS FAMILY_CODE                -- 40
    ,REORDER AS ALOIS_CATEGORY            -- 41
    ,SUBCAT AS ALOIS_SUBCATEGORY        -- 42
    ,LEFT(MLI1,1) AS REPLENISHMENT_CODE        -- 43
    ,CASE
        WHEN LENGTH(MLI1) = 3 THEN SUBSTRING(MLI1,3,1)
        WHEN LENGTH(MLI1) < 3 THEN NULL
    END AS REPLENISHMENT_CODE_OLD            -- 44
    ,CASE
        WHEN BALANCE = 'Y' THEN '50021001'
        WHEN BALANCE <> 'Y' THEN '50021002'
    END AS IS_INVENTORY                            -- 45
    ,FIRSTMV AS FIRST_PURCHASE_DATE    -- 50
    ,FIRSTMV AS FIRST_SALES_DATE        -- 51
    ,LASTSOLD AS LAST_SALES_DATE        -- 52
    ,M2.LASTBUY AS LAST_PURCHASE_DATE    -- 53
    ,MOVED AS LAST_FLOW_DATE                -- 54
    ,IF(SUPER = PPARTNO,null,SUPER) AS NEW_REPLACE_CODE        -- 55
    ,IF(SUPFROM = PPARTNO,null,SUPFROM) AS OLD_REPLACE_CODE        -- 56
    ,LOSTQTY AS SALES_LOSS_QTY            -- 60
    ,IF(DECIMAL1 = 'Y' ,10041001,10041002) AS IS_DECIMAL_OUT    -- 66
    ,ECONBUY AS MIN_PURCHASE_QTY        -- 67
    ,RAISED AS CREATED_AT                        -- 78
    ,CONCAT(`EDITDATE` , `EDITTIME`) AS UPDATED_AT                -- 81
FROM (SELECT PART_NO,MAX(LASTBUY) AS LASTBUY FROM m_42439_tm_part_stock_card_5 GROUP BY PART_NO) AS M1,
        m_42439_tm_part_stock_card_5 AS M2
WHERE M1.PART_NO = M2.PART_NO AND M1.LASTBUY = M2.LASTBUY GROUP BY M2.PART_NO;

SELECT M1.PART_NO,
    if (left(PPARTNO,1) in ('B','M','E','C','N'),substring(PPARTNO,2,2),(
        if (PPARTNO like 'Z9908%LGL',99,left(PPARTNO,2))))
    as MAIN_GROUP            -- 6
    ,CASE
        WHEN GROUP1 LIKE 'B%' or 'M%' or 'E%' or 'C%' THEN SUBSTRING(PPARTNO,4,2)
        WHEN PPARTNO LIKE 'Z9908%LGL' THEN SUBSTRING(PPARTNO,4,2)
        ELSE SUBSTRING(PPARTNO,3,2)
    END  AS SUB_GROUP            -- 7
    ,case
        when GROUP1 like    'B%' or '%M' or 'E%' or 'C%' then substring(PPARTNO,6,7)
        when PPARTNO like 'Z9908%LGL' then substring(PPARTNO,6,7)
        else substring(PPARTNO,5,7)
    end as PART_NUM                -- 8
    ,CASE
        WHEN GROUP1 LIKE 'B%' or 'M%' or 'E%' or 'C%' THEN  '50001000' + SUBSTRING(GROUP1,2,2)
        WHEN PPARTNO LIKE 'Z9908%LGL' THEN '50001012'
        ELSE '50001016'
    END AS PART_TYPE        --  9
    ,LEFT(PPARTNO,1) as BRAND_CODE        -- 11
    ,LASTCOST AS LATEST_COST        -- 13
    ,PRICE1 AS RECOMMEND_PRICE -- 14
    ,CASE
        WHEN GROUP1 LIKE 'B%' or 'M%' or 'E%' or 'C%' THEN price2
        WHEN PPARTNO LIKE 'Z9908%LGL' THEN price2
        ELSE price2/(case when trim(pfactor)=0 then 1 else pfactor end)
    END AS BBA_COST            -- 15
    ,ROUND(PRICE1*1.13,4) AS TAX_PRICE        -- 16
    ,NLPRICE AS LAST_RECOMMEND_PRICE        -- 17
    ,NLPRICE AS LAST_RCMD_DATE            -- 18
    ,NLDATE AS LAST_RCMD_DATE                -- 21
    ,TOTALQTY AS TOTAL_STOCK_QTY        -- 22
    ,MAXIMUM AS MAX_QTY                            -- 23
    ,MINIMUM AS MIN_QTY                            -- 24
    ,CASE NOUPDATE
        WHEN 'Y' THEN '10041001'
        WHEN 'N' THEN '10041002'
    END AS PROHIBIT_PRICE_UPDATE            -- 31
    ,CASE
        WHEN KIT ='Y' AND BALANCE = 'Y' THEN '50011001'
        WHEN KIT ='Y' THEN '50011002'
        ELSE '0'
    END AS ASSEMBLY_KIT                            -- 32
    ,CASE DISCOUNT
        WHEN 'A' THEN '18' WHEN 'B' THEN '20' WHEN 'C' THEN '24' WHEN 'D' THEN '27'
        WHEN 'E' THEN '31' WHEN 'F' THEN '36' WHEN 'G' THEN '40' WHEN 'H' THEN '45'
        WHEN 'I' THEN '49' WHEN 'K' THEN '52' WHEN 'P' THEN '5' WHEN 'N' THEN '63'
        WHEN 'L' THEN '56' WHEN 'M' THEN '58'
    END AS DISCOUNT_CODE                         -- 39
    ,SALECODE AS FAMILY_CODE                -- 40
    ,REORDER AS ALOIS_CATEGORY            -- 41
    ,SUBCAT AS ALOIS_SUBCATEGORY        -- 42
    ,LEFT(MLI1,1) AS REPLENISHMENT_CODE        -- 43
    ,CASE
        WHEN LENGTH(MLI1) = 3 THEN SUBSTRING(MLI1,3,1)
        WHEN LENGTH(MLI1) < 3 THEN NULL
    END AS REPLENISHMENT_CODE_OLD            -- 44
    ,CASE
        WHEN BALANCE = 'Y' THEN '50021001'
        WHEN BALANCE <> 'Y' THEN '50021002'
    END AS IS_INVENTORY                            -- 45
    ,FIRSTMV AS FIRST_PURCHASE_DATE    -- 50
    ,FIRSTMV AS FIRST_SALES_DATE        -- 51
    ,LASTSOLD AS LAST_SALES_DATE        -- 52
    ,M2.LASTBUY AS LAST_PURCHASE_DATE    -- 53
    ,MOVED AS LAST_FLOW_DATE                -- 54
    ,IF(SUPER = PPARTNO,null,SUPER) AS NEW_REPLACE_CODE        -- 55
    ,IF(SUPFROM = PPARTNO,null,SUPFROM) AS OLD_REPLACE_CODE        -- 56
    ,LOSTQTY AS SALES_LOSS_QTY            -- 60
    ,IF(DECIMAL1 = 'Y' ,10041001,10041002) AS IS_DECIMAL_OUT    -- 66
    ,ECONBUY AS MIN_PURCHASE_QTY        -- 67
    ,RAISED AS CREATED_AT                        -- 78
    ,CONCAT(`EDITDATE` , `EDITTIME`) AS UPDATED_AT                -- 81
    ,M1.AVERAGE_COST
    ,M1.TOTAL_AVAILABLE_QTY
    ,M1.SCRAP_STORAGE_QTY
    ,M1.CLAIM_STORAGE_QTY
FROM (SELECT PART_NO
            ,MAX(LASTBUY) AS LASTBUY
            ,SUM((AVGCOST*TOTALQTY))/(case when sum(TOTALQTY)=0 
                        then 1 else sum(TOTALQTY) end)  AS AVERAGE_COST        -- 12
            ,SUM(TOTALQTY) AS TOTAL_AVAILABLE_QTY            -- 26
            ,SUM(QUANTITY7) AS SCRAP_STORAGE_QTY        -- 29
            ,SUM(QUANTITY7) AS CLAIM_STORAGE_QTY            -- 30
    FROM m_42439_tm_part_stock_card_5 GROUP BY PART_NO) AS M1,
         m_42439_tm_part_stock_card_5 AS M2
WHERE M1.PART_NO = M2.PART_NO
    AND M1.LASTBUY = M2.LASTBUY 
GROUP BY M2.PART_NO;

Day6

select
    sum(CKINQTY) as IN_QTY,    -- 10
    sum(CKINVAL) as IN_AMOUNT, -- 11
    sum(CKOUTQTY) as OUT_QTY, -- 12
    sum(CKOUTVAL) as OUT_AMOUNT, -- 13
    sum(TOTALQTY) as END_QTY, -- 14
    (case when sum(TOTALQTY)!=0.0
         then  sum(TOTALQTY*AVGCOST)/sum(TOTALQTY) else 0 end) as END_PRICE, -- 15
    sum(TOTALQTY*AVGCOST) as END_AMOUNT,    -- 16
    sum(HISTORY1) as DEMAND_QTY,    -- 17
    sum(REQUESTS1) as DEMAND_FREQUENCY,    -- 18
    sum(QUANTITY1) as ONE_HOUSE,    -- 20
    sum(QUANTITY2) as TOW_HOUSE,    -- 21
    sum(QUANTITY3) as THREE_HOUSE,    -- 22
    sum(QUANTITY4) as FOUR_HOUSE,    -- 23
    sum(QUANTITY5) as FIVE_HOUSE,    -- 24
    sum(QUANTITY6) as SIX_HOUSE,    -- 25
    sum(QUANTITY7) as SCRAP_HOUSE,    -- 26
    sum(QUANTITY8) as CLAIM_HOUSE,    -- 27
    sum(QUANTITY9) as NINE_HOUSE,    -- 28
    sum(INQTY) as PURCHASE_IN_QTY,    -- 29
    sum(INVAL) as PURCHASE_IN_AMOUNT,    -- 30
    sum(OUTQTY) as SALES_OUT_QTY,    -- 31
    sum(OUTVAL) as SALES_OUT_AMOUNT,    -- 32
    sum(CKINQTY) as INVENTORY_IN_QTY,    -- 33
    sum(CKINVAL) as INVENTORY_IN_AMOUNT,    -- 34
    sum(CKOUTQTY) as INVENTORY_OUT_QTY,    -- 35
    sum(CKOUTVAL) as INVENTORY_OUT_AMOUNT    -- 36
from bmw_mgr.m_42439_tt_part_month_report_5
where part_no<>'Total'
group by YEAR1,MONTH,PART_NO