MS SQL Server
  1. MS SQL Server
  3. Requête pour filterr les transactions avec le montant exact du décalage
Intereting Posts
SQL Server wrap Select … Insère-t-il des requêtes dans une transaction implicite? Combiner XML à partir de T-SQL Facteur de remplissage SQL Server SQL Server Comment find les clients qui ont acheté un produit de CHAQUE magasin? SQL Comment listr les colonnes dans une table en tant que déclaration de slider Recommandation de database utiliser une instruction select sql pour get les parameters de la seconde instruction select Comment puis-je get un rapport SQL avec un groupe qui substitue le groupe par valeur avec une string? à partir de la valeur existante Mettre à jour la colonne à l'aide de valeurs séparées par des virgules dans SQL Server Problèmes avec la génération automatique uniqueidentifier dans SQL sur insert login au file mdf de la database du server sql sans installer le server sql sur la machine cliente? Impossible de se connecter aux servers SQL distants en dehors du réseau avec PYODBC Version SQL Server 2008 de OVER (… Rows Unbounded Preceding) SQL Server (Entity Framework): created_at, updated_at Colonnes Comment passer une table à une procédure stockée?

Requête pour filterr les transactions avec le montant exact du décalage

La requête ci-dessous est un brouillon contenant les colonnes pertinentes dans la requête que je voudrais écrire, alors ne le regardez pas comme une solution. Utilisez-le comme un guide pour les noms de table et de colonne. J'essaie de supprimer toutes les transactions qui se compensent pour les mêmes ORDER_ID et ACCOUNT_ID. Je ne pense pas que je puisse faire un agrégat en utilisant SUM, car cela appendait toutes les valeurs TX_AMOUNT pour le regroupement. Voir TX_ID 6 et 7. Ces deux doivent apparaître dans le jeu de résultats. Comment puis-je sortir le TX_ID de la table ci-dessous, et filterr tout ce qui ne dit pas "SHOW THIS"? 

SELECT T1.ACCOUNT_ID T1.ORDER_ID, T1.TX_ID FROM TRANSACTION AS T1 WHERE T1.ACCOUNT_ID IN ( SELECT T2.ACCOUNT_ID FROM TRANSACTION AS T2 GROUP BY T2.ACCOUNT_ID, T2.ORDER_ID HAVING SUM(T2.TX_AMOUNT) != 0 AND T2.ORDER_ID IS NOT NULL ) AND T1.ORDER_ID IN ( SELECT T3.ORDER_ID FROM TRANSACTION AS T3 GROUP BY T3.ACCOUNT_ID, T3.ORDER_ID HAVING SUM(T3.TX_AMOUNT) != 0 AND T3.ORDER_ID IS NOT NULL ) TX_ID ORDER_ID ACCOUNT_ID TX_AMOUNT ------------------------------------ 1 A1 200 -3.00 <--------- DON'T SHOW THIS; OFFSET BY #2 2 A1 200 3.00 <--------- DON'T SHOW THIS; OFFSET BY #1 3 A1 200 3.00 <--------- SHOW THIS 4 A2 999 -10.01 <--------- DON'T SHOW THIS; OFFSET BY #5 5 A2 999 10.01 <--------- DON'T SHOW THIS; OFFSET BY #4 6 A2 999 10.01 <--------- SHOW THIS 7 A2 999 5.02 <--------- SHOW THIS

VERSION 2: BEAUCOUP plus propre … DEMO de travail avec des commentaires (vous devrez peut-être cliquer sur Exécuter!) Pour voir les résultats souhaités (ou peut-être que j'ai un problème de caching)

  • Le CTE (Common Table Expression) est juste votre configuration de données que vous montrez
  • Le CTE2 ajoute simplement un numéro de ligne partitionné par le tx_amount, order_Id, account_Id. La key ici est le fait que nous obtenons un row_number pour chaque order_ID, Account_ID et tax_Amount redémarrant lorsque ces 3 valeurs changent mais s'incrémentent quand elles restnt les mêmes. Cela nous permet plus tard d'exclure des matchs identiques sur les tx_amounts opposés sans éliminer ceux qui sont plus importants que les autres (votre exemple à 3,00 $)
  • Le Select extrait simplement les loggings de l'set de base où la valeur opposée avec le même numéro de ligne order_id et le count existent. si tel n'est pas le cas, nous soaps que c'est une valeur w / oa correspondant à tx_Amount et que nous voulons conserver.
  • Demandez si vous avez des questions! Heureux d'aider si quelque chose n'est pas clair
  • Enfin si nous changeons CTE2 donc le rowNumber () est ordonné par tx_ID asc au lieu de tx_Amount desc (ce qui ne sert vraiment à rien d'autre que j'ai besoin d'un ordre sur le row_nubmer), alors nous nous débarrasserons du nombre le plus bas suivant l'approche FIFO)

. 

 With CTE (TX_ID, ORDER_ID, ACCOUNT_ID, TX_AMOUNT) as ( SELECT 1, 'A1', 200, -3.00 UNION ALL SELECT 2, 'A1', 200, 3.00 UNION ALL SELECT 3, 'A1', 200, 3.00 UNION ALL SELECT 4, 'A2', 999, -10.01 UNION ALL SELECT 5, 'A2', 999, 10.01 UNION ALL SELECT 6, 'A2', 999, 10.01 UNION ALL SELECT 7, 'A2', 999, 5.02 ), cte2 as ( SELECT A.*, row_number() over (partition by order_ID, Account_ID, Tx_Amount order by tx_Amount desc) RN FROM cte A) SELECT * FROM cte2 A WHERE NOT exists (SELECT * FROM cte2 B WHERE A.Order_ID = B.Order_ID and A.Account_ID = B.Account_Id and A.tx_Amount*-1 = B.tx_Amount and A.RN = B.RN)

En nous donnant: (notez que nous devrions éliminer le RN en changeant * aux champs désirés mais je suis trop paresseux à ce point) 

 +----+-------+----------+------------+-----------+----+ | | TX_ID | ORDER_ID | ACCOUNT_ID | TX_AMOUNT | RN | +----+-------+----------+------------+-----------+----+ | 1 | 2 | A1 | 200 | 3,00 | 2 | | 2 | 7 | A2 | 999 | 5,02 | 1 | | 3 | 5 | A2 | 999 | 10,01 | 2 | +----+-------+----------+------------+-----------+----+

VERSION 1: (Scratch ce laid, je veux dire sérieusement, qui pense comme ça?) Je fais …

  1. Faire quelque chose. (version 1)
  2. Rire (une étape importante et négligée)
  3. Alors faites-le bien (voir la version 2 ci-dessus)
  4. Maintenant, faites le mieux. (indexes, tweak joint l'orthographe, les commentaires de layout, utilisez le bon ordre par sur la logique row_number dans CTE2)

DEMO 

 With CTE (TX_ID, ORDER_ID, ACCOUNT_ID, TX_AMOUNT) as ( SELECT 1, 'A1', 200, -3.00 UNION ALL SELECT 2, 'A1', 200, 3.00 UNION ALL SELECT 3, 'A1', 200, 3.00 UNION ALL SELECT 4, 'A2', 999, -10.01 UNION ALL SELECT 5, 'A2', 999, 10.01 UNION ALL SELECT 6, 'A2', 999, 10.01 UNION ALL SELECT 7, 'A2', 999, 5.02 ), cte2 as ( SELECT * FROM (Select A.Tx_Id aTx_ID , A.order_ID as AOrderID , A.Account_ID as AAccount_ID , A.tx_Amount as ATx_Amount , Row_number() over (partition by Order_ID, Account_ID, tx_Amount order by tx_Amount asc) ARN from cte a WHERE tx_Amount <=0) A FULL OUTER JOIN (SELECT b.tx_Id , b.order_Id , b.Account_Id , b.tx_Amount , Row_number() over (partition by Order_ID, Account_ID, tx_Amount order by tx_Amount desc) BRN FROM CTE B WHERE tx_Amount>0) B on A.AOrderID = B.Order_ID and A.AAccount_ID = B.Account_ID and A.ATx_Amount*-1 = B.tx_Amount and A.ARN=B.BRN Where a.Atx_ID is null or B.tx_ID is null) Select ATX_ID, AORDERID, AAccount_ID, ATX_AMOUNT from cte2 where ATX_ID is not null UNION ALL Select TX_ID, ORDER_ID, Account_ID, TX_AMOUNT from cte2 where TX_ID is not null