Question

I would really appreciate your help with the following problem:

我有一个表(库存——头盔),有3列利息。

catalogue_code  purcha  stock_
--------------  ------  ------
1001            Box     Box
1001            Box     Box
1002            EA      EA
1002            Set     Set
1002            Kit     Kit
1004            Set     Set

我谨利用以下规则提取资料:

If a catalogue_code is a duplicate and all its purcha and stock_ attributes have the same value (e.g catalogue_code 1001), select any record.
If a catalogue_code is a duplicate and some of its purcha and stock_ attributes contain different values (e.g catalogue_code 1002), select the record that has purcha = EA , stock_ = EA .

我在T-SQL中难以执行这一逻辑。

<>Update: 只想指出,原表格没有主要关键。复制件的去除是为了使目录——编码能够成为主要的关键。

<>Update2: 不幸的是,尼克和埃利亚的答复未能解决问题。这里是我关于可能实施的假守则:

Find all the duplicated catalogue_codes
For each duplicate do:
Get any pair of stock_ and purcha attributes. Compare the selected pair to all other pairs and store your results in a variable (numberOfIdenticalRecords).
If numberOfIdenticalRecords = 1 (meaning that all the other records have distinct stock_ and purcha values). In this case, select the record that has purcha = EA and stock_ = EA .
Else if numberOfIdenticalRecords > 1 (meaning that all the records contain the same stock_ and purcha values), select any record.

执行工作的一个重大缺陷是,它是程序性的,而是标准。即使有可能执行,但机会却是难以理解和维持。我一想整个事? 这样做是否有更简单的方法?

Answer 1

declare @T table
(
  catalogue_code int,
  purcha varchar(5),
  stock_ varchar(5)
)

insert into @T values
(1001,             Box ,      Box ),
(1001,             Box ,      Box ),
(1002,             AA ,       AA ),
(1002,             MM ,       MM ),
(1002,             EA ,       EA ),
(1002,             Set ,      Set ),
(1002,             Kit ,      Kit ),
(1004,             Set ,      Set )

;with C as
(
  select *,
         row_number() over(partition by catalogue_code 
                           order by case when purcha =  EA 
                                         then 0
                                         else 1
                                    end) as rn
  from @T
)
select *
from C
where rn = 1

结果:

catalogue_code purcha stock_ rn
-------------- ------ ------ --------------------
1001           Box    Box    1
1002           EA     EA     1
1004           Set    Set    1

Try it on SE-Data Exploration: https://data.stackchange.com/stackoverflow/q/114648/

Answer 2

在标题中,你提到删除行文,但你的疑问与你一样,希望有某种疑问。我必须说,你的要求有点奇怪,但我猜想下面的问话会给你想什么:

select 
    catalogue_code, purcha, stock_
from 
    stock_header
where 
    (purcha =  EA  and stock_ =  EA ) 
    or catalogue_code not in(select catalogue_code from stock_header where purcha =  EA  AND stock_ =  EA )
group by 
    catalogue_code, purcha, stock_

如果你真的需要deleterows,那么,你需要找到一个独一无二的词语,如他在评论中提到的Erwin Brandstetter。

友情链接