SQL表，在行中查找值并进行比较. [英] SQL table, finding values in rows and comparing.

问题描述

我有一个带有列的表:LastName，FirstName，DOB，CurrentWeight，CurrentEmpStat，DateOfVIsit，Patient_ID和Rec_ID.
在此表中，我可以具有多个条目的同一个人.记录将具有唯一的Rec_ID.
我需要检查表并找到具有相同姓，名，DOB和Patient_ID的行.
然后，我需要从该表中删除所有记录(最新记录除外)(姓，名，DOB，Patient_ID).实现此目标的最佳方法是什么?
提前感谢.
JL.

再次问好，
实际上，让我更好地描述问题.我真正想要实现的是逐行浏览表并比较这些行中的数据.在相互比较并在这些行的五列中的四列中找到相同的值时，应删除第五个值较高/较低的那个(假定OP知道要选择哪个)，然后继续进行处理直到表中这四列都是唯一的.



HELLO AGAIN，
实际上，让我更好地描述一下.
我要寻找的是逐行遍历数据库并比较这些行中的数据.当对这些行中的五个列中的四个进行一对一比较并找到相同的值时，应删除第五个列中的一个较高的值.或更小.然后，删除操作完成后，应将其与数据库中的另一行进行比较，直至结束.因此，基本上没有行具有相同的四个值.
希望，这不算什么.. :)

JL

Hi,
I have a table with columns: LastName, FirstName, DOB, CurrentWeight, CurrentEmpStat, DateOfVIsit, Patient_ID and Rec_ID.
In this table I can have same person with multiple entries. Record will have a unique Rec_ID.
I need to check the table and find the rows with same LastName, FirstName, DOB and Patient_ID.
Then I need to delete all multiple records (LastName, FirstName, DOB, Patient_ID)from that table except the newest record. What''s the best way to achieve this?
Thankx in advance.
JL.

Hello again,
Actually, let me describe the problem a little better. What I actually want to achieve is to go through the table row by row and compare data in those rows. While comparing one to another and finding same values in four out of five columns in those rows it should delete the one whose 5th value is higher/lower ( assuming OP knows which to choose) And continue processing until those four columns are unique in the table.



HELLO AGAIN,
ACTUALLY, LET ME DESCRIBE IT LITTLE BETTER.
WHAT I''M LOOKING FOR LITTERALY IS TO GO THROUGH THE DATABASE ROW BY ROW AND COMPARE DATA IN THOSE ROWS. WHEN COMPERING ONE TO ONE AND FINDING SAME VALUES IN FOUR OUT OF FIVE COLUMNS IN THOSE ROWS IT SHOULD DELETE THE ONE WHICH VALUE OF THE FIFTH COLUMN IS HIGHER. OR SMALLER. THEN WHEN DELETE IS DONE IT SHOULD GO AND COMPARE IT TO THE ANOTHER ROW IN THE DATABASE TILL THE END. SO BASICALLY, NO ROWS WITH SAME FOUR VALUES.
HOPE, IT''S NOT THAT COMFUSING.. :)

JL

推荐答案

假设像这样的表

create table test(
    rec_id uniqueidentifier -- your PK
,   rec_timestamp timestamp -- something to let us find the latest record
,   pid varchar(max) -- patient id, first name, last name, and DOB
,   first varchar(max)
,   last varchar(max)
,   dob date
)

该删除语句应该可以解决问题:

this delete statement should do the trick:

delete lhs
from test lhs
where exists (
    select *
    from test rhs
    where lhs.first=rhs.first
      and lhs.last=rhs.last
      and lhs.dob=rhs.dob
      and lhs.pid=rhs.pid
      and rhs.rec_timestamp > lhs.rec_timestamp
)

祝你好运！

这篇关于SQL表，在行中查找值并进行比较.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！