SQL Server Is is posible to filter group of records?

66bbxpm5 于 2023-06-04 发布在其他

关注(0)|答案(2)|浏览(117)

i have table with operations on vehicle. It looks like this:
| idr | idp | ope |
| ------------ | ------------ | ------------ |
| 2230530165347004000299104100 | 2230530164734003000299104 | 8 |
| 2230530165330003000299104100 | 2230530164734003000299104 | 14 |
| 2230530164818004000299104100 | 2230530164734003000299104 | 1 |
| 2230530164734033000299104100 | 2230411105828003005269783 | 2 |
| 2230530163656012000290160100 | 2230411105828003005269783 | 14 |
| 2230530163614017000290160100 | 2230411105828003005269783 | 1 |

idr - unique identifier

idp - id of the vehicle, every vehicle have own idp, top 3 records are for one vehicle and other 3 records are from other vehicle

ope - operation number, every type of operation have own ope number, for example 1 = First registration 14 - order vehicle document, 8 - release document, 2 - deregistration etc

I want to find every vehicle that has ope 132 but at the same time doesn't have ope 131. For example:

No. 1 vehicle Audi A4 with idp 2230530160651002000290160 and this vehicle has operations 1, 14, 8, 132, 131
No. 2 vehicle BMW M5 with idp 2230530160537003000291185 and this vehicle has operations 1, 14, 8, 132

I need only vehicles like vehicle no. 2.

I tried using union, cte, group by but my knowledge is insufficient to make query like this. Every time query result contains vehicles with ope 131.

sql-server

来源：https://stackoverflow.com/questions/76366602/is-is-posible-to-filter-group-of-records

2条答案

按热度按时间

hwazgwia1#

I want to find every vehicle that has ope 132 but at the same time doesn't have ope 131

You can use group by and having :

select idp
from mytable
where ope in (131, 132)
group by idp
having min(ope) = 132

The query fiters on both operation, and ensures that 131 does not occur (since the minimum value is 132).

A more generic approach uses case expressions (this would fit more complex cases, over more columns):

select idp
from mytable
where ope in (131, 132)                              -- any of the two operations
group by idp
having max(case when ope = 131 then 1 else 0) = 0    -- 131 was not found
   and max(case when ope = 132 then 1 else 0) = 1    -- but 132 was found

赞(0）回复(0）举报 2023-06-04

h4cxqtbf2#

this is a pretty rough example. Your checking for presence of your first criteria in the outer query and the absence of the 2nd in your inner query.

SELECT distinct idp 
FROM Yourtable yt1
WHERE ope IN (132)
AND not exists
    (
        SELECT DISTINCT idp
        FROM yourTable yt2
        WHERE open IN (131)
        AND yt2.idp = yt1.idp
    )

赞(0）回复(0）举报 2023-06-04

我来回答

SQL Server Is is posible to filter group of records?

2条答案

相关问题

热门标签

最新问答