SQL Server 用于排除联接表中两列值的SQL [已关闭]

92dk7w1h 于 2022-12-10 发布在其他

关注(0)|答案(1)|浏览(125)

Closed. This question needs details or clarity . It is not currently accepting answers.
Want to improve this question? Add details and clarify the problem by editing this post .

Closed 6 days ago.
Improve this question
I have following two tables
Table 1
| PKey | number | amount | date |
| ------------ | ------------ | ------------ | ------------ |
| CN-897687 | YHVY | 299.99 | 11/21/22 3:32 PM |
| CN-646465 | JWVF | 271.05 | 10/21/22 4:34 AM |
| CN-475678 | C96H | 61.99 | 7/1/22 11:05 AM |
| CN-858673 | QCVM | 324.08 | 3/1/22 9:51 AM |
| CN-347468 | RW44 | 165.02 | 5/9/22 8:49 AM |
| CN-079836 | 3XTY | 371.34 | 11/27/22 8:48 PM |
Table 2
| PKey | state | open_amt | date |
| ------------ | ------------ | ------------ | ------------ |
| CN-897687 | issued | -299.99 | 11/21/22 3:32 PM |
| CN-897687 | issued | -0.99 | 11/29/22 11:31 AM |
| CN-475678 | issued | -61.99 | 7/1/22 11:05 AM |
| CN-858673 | issued | -324.08 | 3/1/22 9:51 AM |
| CN-858673 | cleared | 0.00 | 11/17/22 12:32 AM |
| CN-858673 | issued | -173.75 | 5/1/22 12:17 AM |
| CN-347468 | issued | -165.02 | 5/9/22 8:49 AM |
| CN-079836 | issued | -371.34 | 11/27/22 8:48 PM |
| CN-079836 | issued | -21.84 | 12/1/22 10:53 AM |
| CN-646465 | issued | -271.05 | 10/21/22 4:34 AM |
| CN-646465 | issued | -22.95 | 11/4/22 9:42 AM |
| CN-646465 | issued | -9.60 | 12/1/22 12:20 AM |
| CN-646465 | cleared | 0.00 | 12/2/22 12:34 AM |
I am using a SQL query to get the output as follows
| PKey | number | amount | date | state | open_amt |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| CN-897687 | YHVY | 299.99 | 11/21/22 3:32 PM | issued | -0.99 |
| CN-646465 | JWVF | 271.05 | 10/21/22 4:34 AM | cleared | 0.00 |
| CN-475678 | C96H | 61.99 | 7/1/22 11:05 AM | issued | -61.99 |
| CN-858673 | QCVM | 324.08 | 3/1/22 9:51 AM | cleared | 0.00 |
| CN-347468 | RW44 | 165.02 | 5/9/22 8:49 AM | issued | -165.02 |
| CN-079836 | 3XTY | 371.34 | 11/27/22 8:48 PM | issued | -21.84 |
I want the output as follows
| PKey | number | amount | date | state | open_amt |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| CN-897687 | YHVY | 299.99 | 11/21/22 3:32 PM | issued | -0.99 |
| CN-475678 | C96H | 61.99 | 7/1/22 11:05 AM | issued | -61.99 |
| CN-347468 | RW44 | 165.02 | 5/9/22 8:49 AM | issued | -165.02 |
| CN-079836 | 3XTY | 371.34 | 11/27/22 8:48 PM | issued | -21.84 |

SELECT DISTINCT
    t1.Pkey
    t1.number,
    t1.date,
    t1.amount,
    t2.open_amt,
    t2.state
FROM
    (SELECT * FROM table 1
     WHERE PKey IN ('CN-897687', 'CN-646465', 'CN-475678',
                    'CN-858673', 'CN-347468', 'CN-079836')) t1
LEFT JOIN
    (SELECT *
     FROM 
         (SELECT 
              *, 
              ROW_NUMBER() OVER (PARTITION BY Pkey ORDER BY date DESC) rn
          FROM
              table 2)
     WHERE
         rn = 1
         -- AND open_amt <> 0
         -- AND state <> 'cleared'
          ) t2 ON t1.Pkey = t2.Pkey

Tried to put the filter for state <> 'cleared' and open_amt <> 0 with different combinations, but not getting the required output

sql-server

来源：https://stackoverflow.com/questions/74667735/sql-to-exclude-two-column-values-in-a-join-table

1条答案

按热度按时间

ocebsuys1#

just add filter condition inside sub query.

select *, row_number () over (partition by Pkey order by date desc) rn
                            from table 2 where open_amt >0
                                           and trim(lower(state)) <> 'cleared'

BTW : i didn't execute your query.

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server 用于排除联接表中两列值的SQL [已关闭]

1条答案

相关问题

热门标签

最新问答