SQL查询其在同一个表上的一列中找到不同的值

Question

我有以下表，其中Description场有scope = 10我插入的第一排，Description = Cobra - Ni但前几天我才意识到，我需要更大的范围，其被延长并且适当的值应该是Cobra - Nisyor。

Column_ref  Description  Date   Money  Doc_nr 
123   Cobra - Ni  06-11-2015 505.50 2000/10 
123   Cobra - Toung 07-11-2015 505.50 2000/12 
123   Cobra - Brain 07-11-2015 505.50 2000/25 
123   Cobra - Nisyor 07-11-2015 505.50 2000/10 

我需要编写查询，查找这个示例表中的第一行和最后一行。

我试过这样：

SELECT t1.* 
FROM table as t1 
WHERE t1.Description in 
     (SELECT t2.Description 
     FROM table as t2 
     WHERE t1.Doc_nr = t2.Doc_nr 
     AND t1.Description != t2.Description) 

，但它不工作。

Answer 1

我假设“范围”是指列的宽度是10.因此，您正在寻找关联行，一个长度= 10，另一个以相同的字符串开始，长度> 10。可以使用LEN()函数来获取字符字段的长度，并使用LEFT()来获取子字符串 - 后者可用于比较“新”字和“旧”字。

例如：

with oldRows as (
    select * 
    from myTable 
    where LEN(Description) = 10 
), newRows as (
    select *, LEFT(Description, 10) as oldKey 
    from myTable 
    where LEN(Description) > 10 
) 
select n.*, o.* 
from oldRows o 
join newRows n on o.Description = n.oldKey 
-- Of course add any other comparisons you need to correlate rows: 
-- and o.Column_ref = n.Column_ref 
-- and o.[Date] = n.[Date] 
-- and o.[Money] = n.[Money] 
-- and o.Doc_nr = n.Doc_nr 

对于你可能不应该意识到这个问题后，插入额外的新行到表日后参考，并应该使用的更新替代。

Answer 2

要找到您要查找的行，您需要在doc_nr上执行self join，仅包括那些描述不匹配的行，SQL Fiddle。

 
CREATE TABLE basic 
(
    column_ref INT, 
    description VARCHAR(30), 
    dateField DATETIME, 
    amount DECIMAL(12,2), 
    doc_nr VARCHAR(30) 
); 

INSERT INTO basic (column_ref, description, dateField, amount, doc_nr) 
VALUES (123, 'Cobra - Ni', '06/11/2015',505.50,'2000/10'), 
     (123, 'Cobra - Toung', '07/11/2015',505.50,'2000/12'), 
     (123, 'Cobra - Brain', '07/11/2015',505.50,'2000/25'), 
     (123, 'Cobra - Nisyor', '07/11/2015',505.50,'2000/10'); 

SELECT * 
FROM basic b 
JOIN basic q ON b.doc_nr = q.doc_nr 
WHERE b.description != q.description 

╔════════════╦════════════════╦════════════════════════╦════════╦═════════╦════════════╦════════════════╦════════════════════════╦════════╦═════════╗ 
║ column_ref ║ description ║  dateField  ║ amount ║ doc_nr ║ column_ref ║ description ║  dateField  ║ amount ║ doc_nr ║ 
╠════════════╬════════════════╬════════════════════════╬════════╬═════════╬════════════╬════════════════╬════════════════════════╬════════╬═════════╣ 
║  123 ║ Cobra - Ni  ║ June, 11 2015 00:00:00 ║ 505.5 ║ 2000/10 ║  123 ║ Cobra - Nisyor ║ July, 11 2015 00:00:00 ║ 505.5 ║ 2000/10 ║ 
║  123 ║ Cobra - Nisyor ║ July, 11 2015 00:00:00 ║ 505.5 ║ 2000/10 ║  123 ║ Cobra - Ni  ║ June, 11 2015 00:00:00 ║ 505.5 ║ 2000/10 ║ 
╚════════════╩════════════════╩════════════════════════╩════════╩═════════╩════════════╩════════════════╩════════════════════════╩════════╩═════════╝ 

为了真正DELETE行，用下面的（前提是你要DELETE具有较短的描述行，你的实际标准可能会有所不同）取代上述SELECT声明。

DELETE b 
FROM basic b 
JOIN basic q ON b.doc_nr = q.doc_nr 
WHERE LEN(b.description) < LEN(q.description); 

对上述语法here的评价。