I want to determine if there is a record that matches the condition in SQL.

tableA
ID YMD DAY SECSE CODE
22 1997090100 KG00100
22 199709010 10 KG00610
22 199709010 11 KG01042
22 199709010 12 KG00361
22 199709010 13 KG00363
22 199709010 20 KG01150
22 199709010 30 KG01631
22 199709010 31 KG01632

22 19970912000 KG00200
22 19970912 0 10 KG01631
22 19970912 0 11 KG01632
22 19970912 0 20 KG01670
22 199709120 30 KG01320
22 199709120 31KG01180
22 19970912 032 KG02281
22 19970912 03 KG00930

22 1997120500 KG00200
22 199712050 10 KG04830
22 1997 12050 11 KG04230
22 1997 12050 12 KG04410
22 1997 12050 20 KG01633
22 1997 12050 21 KG01670
22 19971205022 KG00362

22 19971216000 KG00200
22 199712160 10 KG05540
22 199712160 11 KG06140
22 199712160 12KG00510
22 199712160 20 KG01670

22 199801270 10 KG01110
22 199801270 11 KG05160
22 199801270 12KG05310
22 199801270 13KG05320
22 199801270 14 Z10180
22 199801270 20 KG01670
22 19980127 100 KG00200
22 19980127 110 KG01633
22 19980127 11 KG00362

22 1998020600 KG00200
22 199802060 10 KG05040
22 199802060 11 KG01140
22 199802060 20 KG01110
22 199802060 21KG05160
22 199802060 22KG05310
22 199802060 23KG05320
22 199802060 24 Z10180
22 199802060 30 KG01633
22 199802060 31 KG01670
22 19980206032 KG00362

Thank you for your help.
In the table above, I would like to extract an ID and a date that does not have a value of DAY with 0 SE and 0 CSE and code KG00100 or KG00200.
As for the expected value, the DAY0 group with ID2219980127 does not include CODE KG00200 or KG00100, so I would appreciate it if you could extract 2219980127.
ID YMD DAY SEQ CSEQ CODE
22 1998012700 KG00200
I would like to insert this record.

DB is management studio.

I look forward to your kind cooperation.

Additional
I would like to know the date and ID of each date where DAY is 0SE, 0CSE, 0CODE is not KG00200 or KG00100.

c# sql

2022-09-30 18:10

1 Answers

You should first apply the DAY, SE, CSE conditions to the table, and then create a query that excludes the group records that fall under the CODE exclusion conditions.
So use NOT EXISTS to

 WITH cte
AS
(
    SELECT*
      FROM tableA
     WHERE DAY = '0'
       AND SE = '0'
       ANDCSE = '0'
)
SELECT*
  FROM ctet1
  WHERE NOT EXISTS
(
    SELECT*
      FROM ctet2
     WHERE t1.ID = t2.ID
       AND t1.YMD = t2.YMD
       AND t1.DAY = t2.DAY
       AND t2.CODE IN ('KG00100', 'KG00200')
)

I think this kind of query can be realized.

The intention of the question seems to be to multiply by GROUP BY ID, YMD, so for the query above,

Change the second SELECT* to SELECT DISCTINCT ID, YMD
Delete ANDt1.DAY=t2.DAY

I think it will be as intended if
However, if you want to group the output, you can start from the beginning

SELECT ID, YMD
  FROM tableA
 WHERE DAY = '0'
   AND SE = '0'
   ANDCSE = '0'
 GROUP BY ID, YMD
HAVING SUM (CASE WHEN CODE LIKE'KG00[12]00'THEN1ELSE0END) = 0

You can also extract using the HAVING clause as shown in .

2022-09-30 18:10

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656