xdmy/doc/sql/t10.sql

select contractid ,count(*)c from part_repertory  group by contractid 
having count(*)>1



select contractid ,count(*)c from part_repertory  group by partCode,locationId,contractId,providerId,price
having count(*)>1


show create table part_repertory 




drop table part_repertory2


CREATE TABLE `part_repertory3` (
  `id` int(11) NOT NULL  COMMENT '主键',
  `pastureId` int(11) NOT NULL COMMENT '牧场主键',
  `partId` int(11) DEFAULT NULL COMMENT '设备主键',
  `partName` varchar(100) DEFAULT NULL,
  `partCode` varchar(100) DEFAULT NULL,
  `specification` varchar(100) DEFAULT NULL COMMENT '规格',
  `location` varchar(100) DEFAULT NULL COMMENT '位置',
  `locationId` int(11) NOT NULL,
  `reportery` decimal(10,2) DEFAULT NULL COMMENT '库存',
  `minRepertory` decimal(11,0) NOT NULL DEFAULT 0 COMMENT '最低库存',
  `maxRepertory` decimal(11,0) NOT NULL DEFAULT 10 COMMENT '最高库存',
  `contractId` bigint(50) NOT NULL,
  `providerId` int(11) DEFAULT NULL,
  `providerNames` varchar(100) DEFAULT NULL,
  `brand` varchar(100) DEFAULT NULL COMMENT '品牌',
  `brandId` int(11) NOT NULL,
  `unit` varchar(20) DEFAULT NULL COMMENT '单位',
  `price` decimal(15,5) NOT NULL COMMENT '单价',
  `enable` tinyint(4) DEFAULT NULL,
  `precontractId` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  index `pastureId` (`partCode`,`precontractId`,`locationId`),
  index `contractId` (`contractId`),
  index `partcode` (`partCode`,`locationId`,`contractId`,`providerId`,`price`)
) ENGINE=InnoDB AUTO_INCREMENT=587461 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC


describe buydetail 


alter table buydetail  modify column contractId  bigint(50) ;

describe checkpart 

alter table checkpart  modify column contractId  bigint(50) ;


show  index from  part_repertory

insert into part_repertory2 

alter table part_repertory add unique index (partCode,
locationId,
contractId,
providerId,
price)



describe part_repertory


select pr.partCode,
pr.locationId,
pr.contractId,
pr.providerId,
pr.price,  
pr.reportery  from part_repertory  pr
join (
select partCode,locationId,contractId,providerId,price from part_repertory  group by partCode,locationId,contractId,providerId,price
having count(*)>1
)t on 
pr.partCode= t.partCode  and 
pr.locationId= t.locationId  and 
pr.contractId= t.contractId  and 
pr.providerId= t.providerId  and 
pr.price= t.price 
order by  pr.partCode,
pr.locationId,
pr.contractId,
pr.providerId,
pr.price





insert into part_repertory2 
(
select pr.* from part_repertory  pr
join (
select partCode,locationId,contractId,providerId,price from part_repertory  group by partCode,locationId,contractId,providerId,price
having count(*)>1
)t on 
pr.partCode= t.partCode  and 
pr.locationId= t.locationId  and 
pr.contractId= t.contractId  and 
pr.providerId= t.providerId  and 
pr.price= t.price 
order by  pr.partCode,
pr.locationId,
pr.contractId,
pr.providerId,
pr.price
)


describe contract

describe part_repertory


select pr.id, pr.partcode ,pr.contractId  ,c.id ,c.partcode,c2.id,c2.partcode 
from part_repertory pr 
left join contract c on c.id =pr.contractId
left join contract c2 on c2.partcode = pr.partcode
join bigcontract bc on bc.id =c2.bigid
where pr.providerId = bc.providerId and pr.brandid =c2.brandid and pr.price =c2.price
and c.partcode <>c2.partcode


select count(*) 
from part_repertory pr 
left join contract c on c.id =pr.contractId
left join contract c2 on c2.partcode = pr.partcode
join bigcontract bc on bc.id =c2.bigid
where pr.providerId = bc.providerId and pr.brandid =c2.brandid and pr.price =c2.price
and c.partcode <>c2.partcode


delete from part_repertory3



insert into part_repertory3
(
select distinct  pr.*
from part_repertory pr 
left join contract c on c.id =pr.contractId
left join contract c2 on c2.partcode = pr.partcode
join bigcontract bc on bc.id =c2.bigid
where pr.providerId = bc.providerId and pr.brandid =c2.brandid and pr.price =c2.price
and c.partcode <>c2.partcode
)

select  t1.* from 
(
select  pr.id, pr.partcode ,pr.contractId  ,c.id  cid ,c.partcode  ,c2.id  c2id,c2.partcode 
from part_repertory pr 
left join contract c on c.id =pr.contractId
left join contract c2 on c2.partcode = pr.partcode
join bigcontract bc on bc.id =c2.bigid
where pr.providerId = bc.providerId and pr.brandid =c2.brandid and pr.price =c2.price
and c.partcode <>c2.partcode
)t1 join 
(
select  pr.id
from part_repertory pr 
left join contract c on c.id =pr.contractId
left join contract c2 on c2.partcode = pr.partcode
join bigcontract bc on bc.id =c2.bigid
where pr.providerId = bc.providerId and pr.brandid =c2.brandid and pr.price =c2.price
and c.partcode <>c2.partcode
group by  pr.id 
having count(*) >1
)t2 on t1.id =t2.id

update
part_repertory pr 
left join contract c on c.id =pr.contractId
left join contract c2 on c2.partcode = pr.partcode
join bigcontract bc on bc.id =c2.bigid
set pr.contractId = c2.id
where pr.providerId = bc.providerId and pr.brandid =c2.brandid and pr.price =c2.price
and c.partcode <>c2.partcode


select contractid ,count(*)c from part_repertory  group by contractid having count(*)>1

show  index from part_repertory


alter table part_repertory    add index(contractId)
alter table part_repertory    drop  index contractId

show create table part_repertory 


alter table part_repertory  add  UNIQUE  index  (`partCode`,`locationId`,`contractId`,`providerId`,`price`)

select group_concat(id,',')  from part_repertory  group by partCode,locationId,contractId,providerId,price having count(*)>1


select * from part_repertory 
where instr ( (
select group_concat(id,',')  from part_repertory  group by partCode,locationId,contractId,providerId,price having count(*)>1
),id )


select * from part_repertory  where  instr('123',id )



delete pr from part_repertory pr
left join contract c on c.id =pr.contractId
left join contract c2 on c2.partcode = pr.partcode
join bigcontract bc on bc.id =c2.bigid
where pr.providerId = bc.providerId and pr.brandid =c2.brandid and pr.price =c2.price
and c.partcode <>c2.partcode
and pr.reportery <0

alter table part_repertory  add  UNIQUE  index  (`partCode`,`locationId`,`contractId`,`providerId`,`price`)



select pr.* from part_repertory  pr
join 
(select group_concat(id,',') gid ,partCode,locationId,contractId,providerId,price from part_repertory  group by partCode,locationId,contractId,providerId,price having count(*)>1
)t  on
pr.partCode= t.partCode  and 
pr.locationId= t.locationId  and 
pr.contractId= t.contractId  and 
pr.providerId= t.providerId  and 
pr.price= t.price  and 
instr (t.gid,pr,pr.id) >1




CREATE TABLE `part_repertory5` (
  `id` int(11) NOT NULL  COMMENT '主键',
  `pastureId` int(11) NOT NULL COMMENT '牧场主键',
  `partId` int(11) DEFAULT NULL COMMENT '设备主键',
  `partName` varchar(100) DEFAULT NULL,
  `partCode` varchar(100) DEFAULT NULL,
  `specification` varchar(100) DEFAULT NULL COMMENT '规格',
  `location` varchar(100) DEFAULT NULL COMMENT '位置',
  `locationId` int(11) NOT NULL,
  `reportery` decimal(10,2) DEFAULT NULL COMMENT '库存',
  `minRepertory` decimal(11,0) NOT NULL DEFAULT 0 COMMENT '最低库存',
  `maxRepertory` decimal(11,0) NOT NULL DEFAULT 10 COMMENT '最高库存',
  `contractId` bigint(50) NOT NULL,
  `providerId` int(11) DEFAULT NULL,
  `providerNames` varchar(100) DEFAULT NULL,
  `brand` varchar(100) DEFAULT NULL COMMENT '品牌',
  `brandId` int(11) NOT NULL,
  `unit` varchar(20) DEFAULT NULL COMMENT '单位',
  `price` decimal(15,5) NOT NULL COMMENT '单价',
  `enable` tinyint(4) DEFAULT NULL,
  `precontractId` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  index `pastureId` (`partCode`,`precontractId`,`locationId`),
  index `contractId` (`contractId`),
  index `partcode` (`partCode`,`locationId`,`contractId`,`providerId`,`price`)
) ENGINE=InnoDB AUTO_INCREMENT=587461 DEFAULT CHARSET=utf8 ROW_FORMAT=dynamic


insert into  part_repertory4 (
    select * from   part_repertory where reportery <0
)

delete from  part_repertory where reportery <0


select  * from part_repertory4 

insert into  part_repertory4 
select pr.*
from part_repertory  pr
join 
(select partCode,locationId,contractId,providerId,price  from part_repertory  group by partCode,locationId,contractId,providerId,price having count(*)>1
)t  on
pr.partCode= t.partCode  and 
pr.locationId= t.locationId  and 
pr.contractId= t.contractId  and 
pr.providerId= t.providerId  and 
pr.price= t.price 



select t2.ranking,pr.* from part_repertory pr join 
(
select pr.id ,
rank() over (partition by pr.partCode,
pr.locationId,
pr.contractId,
pr.providerId,
pr.price order by id) as Ranking
 from part_repertory  pr
join 
(select partCode,locationId,contractId,providerId,price  from part_repertory  group by partCode,locationId,contractId,providerId,price having count(*)>1
)t  on
pr.partCode= t.partCode  and 
pr.locationId= t.locationId  and 
pr.contractId= t.contractId  and 
pr.providerId= t.providerId  and 
pr.price= t.price 
)t2  on pr.id =t2.id
where  t2.Ranking >1



delete pr from part_repertory pr join 
(
select pr.id ,
rank() over (partition by pr.partCode,
pr.locationId,
pr.contractId,
pr.providerId,
pr.price order by id) as Ranking
 from part_repertory  pr
join 
(select partCode,locationId,contractId,providerId,price  from part_repertory  group by partCode,locationId,contractId,providerId,price having count(*)>1
)t  on
pr.partCode= t.partCode  and 
pr.locationId= t.locationId  and 
pr.contractId= t.contractId  and 
pr.providerId= t.providerId  and 
pr.price= t.price 
)t2  on pr.id =t2.id
where  t2.Ranking >1


alter table part_repertory  add  UNIQUE  index  (`partCode`,`locationId`,`contractId`,`providerId`,`price`)
show index from part_repertory


alter table part_repertory  drop index partcode






update  part_repertory pr join (
select sum(reportery)c,partCode,locationId,contractId,providerId,price from part_repertory4
where reportery >0
group by partCode,locationId,contractId,providerId,price
)t on
pr.partCode= t.partCode  and 
pr.locationId= t.locationId  and 
pr.contractId= t.contractId  and 
pr.providerId= t.providerId  and 
pr.price= t.price 
set pr.reportery   =pr.reportery+t.c


select rank() over (partition by 
partCode,
locationId,
contractId,
providerId,
price order by id) as Ranking,partCode,locationId,contractId,providerId,price ,group_concat(id,',')
from part_repertory4
where reportery >0


select * from part_repertory4  where  reportery >0


select * from  part_repertory pr join (
select t.* from (
select
rank() over (partition by 
partCode,
locationId,
contractId,
providerId,
price order by id) as Ranking,p.*
from part_repertory4 p
where reportery >0
)t where t.ranking =2
)t2 on 
pr.partCode= t2.partCode  and 
pr.locationId= t2.locationId  and 
pr.contractId= t2.contractId  and 
pr.providerId= t2.providerId  and 
pr.price= t2.price  



update part_repertory pr join (
select t.* from (
select
rank() over (partition by 
partCode,
locationId,
contractId,
providerId,
price order by id) as Ranking,p.*
from part_repertory4 p
where reportery >0
)t where t.ranking =2
)t2 on 
pr.partCode= t2.partCode  and 
pr.locationId= t2.locationId  and 
pr.contractId= t2.contractId  and 
pr.providerId= t2.providerId  and 
pr.price= t2.price  
set pr.reportery =pr.reportery +t2.reportery