The Table A
ID QTY UP
1 1 1.5
1 100 1.25
2, 1, 2.5
2 100 2.25

Table B
ID QTY UP
1 56 1.25
1 100 1.25
2, 1, 2.5
2 123 2.5

How do I realize the query result is
ID QTY UP
1 56 1.25
2 123 2.5

This is a business application problem, don't care about why ID repeat, can treat as a SKU, corresponding to different price rules;
Table is to define the rules, A table B is A place where business entry, when entry business data in table B, automatically to contrast table A rules, in order to prevent the artificial modification or does not match the situation of other factors, need A query regarding the table and table B A, the discrepancy BHB line according to the rules of table B business data query, still hope each teach A great god, thank you.

CodePudding user response:

 - 
 create a temporary tableDECLARE @ TABLEA TABLE (ID INT, QTY INT, UP a DECIMAL (19, 9)) 
INSERT INTO @ TABLEA 
SELECT 1,1,1.5 UNION ALL 
SELECT 1100,1.25 UNION ALL 
SELECT 2,1,2.5 UNION ALL 
SELECT 2100,2.25 

DECLARE @ TABLEB TABLE (ID INT, QTY INT, UP a DECIMAL (19, 9)) 
INSERT INTO @ TABLEB 
SELECT 1,56,1.25 UNION ALL 
SELECT 1100,1.25 UNION ALL 
SELECT 2,1,2.5 UNION ALL 
SELECT 2123,2.5 

- data query table B discrepancy BHB A 
SELECT * FROM @ TABLEB AS B 
WHERE NOT the EXISTS (SELECT 1 FROM @ TABLEA AS A WHERE clause B.I D=Anderson D AND B.Q TY=A.Q TY AND B.U P=A.U P) 

CodePudding user response:

ID not repeat words can be so, leave a mark for studying other bosses
Select * from tableb where id not in (
From the select b.i d tablea a inner join tableb b on a.q ty=b.q ty and a.u p=b.u p)