在sql server中交换几行 [英] swap few rows in sql server

问题描述

我对SQL Server有疑问



来源：emp

I have question about SQL Server

Source: emp

id      | name  | check |deptname 
100     |  a    |  1    |ceo   
100     |  b    |  2    |hr
100     |  c    |  3    |po
100     |  d    |  5    |no
101     |  a    |  1    |pm
101     |  b    |  5    |ceo
102     |  a    |  1    |rn
102     |  b    |  2    |han

这里相同的ID有2和5的值，然后我们需要将检查值替换为2检查该ID的值。



基于上表，我想将加载/输出数据放入目标表中，如下所示



目标：emp1

Here same id have check 2 and 5 values then we need to replace check values to 2 check values for that id.

Based on above table I want load/output data into target table like below

Target : emp1

id      | name  | check |deptname
100     |  a    |  1    |ceo
100     |  d    |  2    |hr
100     |  c    |  3    |po
101     |  a    |  1    |pm
101     |  b    |  5    |ceo
102     |  a    |  1    |rn
102     |  b    |  2    |han

我试过以下

and I tried like below

select 
    a1.id,
    a1.name,
    isnull(a2.[check],a1.[check]) as [check]
from 
    emp as a1
left outer join 
    emp as a2 on a2.id = a1.id
              and a1.[check] in (2,5)
              and a2.[check] in (2,5)
              and a2.[check] <> a1.[check]
where 
    a2.id is null
    or (a1.[check] = 5
        and a2.[check] = 2)

该查询没有返回正确的结果。



请告诉我如何编写查询以获取SQL Server中的预期输出

That query does not return the right result.

Please tell me how to write query to get the expected output in SQL Server

推荐答案

检查一下：

Check this:

DECLARE @emp TABLE(id INT, [name] VARCHAR(30), [check] INT, deptname VARCHAR(30))
INSERT INTO @emp (id, [name], [check], deptname)
VALUES(100, 'a', 1, ' ceo'),
(100, 'b', 2, ' hr'),
(100, 'c', 3, ' po'),
(100, 'd', 5, ' no'),
(101, 'a', 1, ' pm'),
(101, 'b', 5, ' ceo'),
(102, 'a', 1, ' rn'),
(102, 'b', 2, ' han')

UPDATE t1 SET [name] = t2.[name]
--SELECT t1.id, t2.[name], t1.[check], t1.deptname
FROM (
	SELECT id, [name], [check], deptname
	FROM @emp
	WHERE id = 100 AND [check]=2
	) AS t1 INNER JOIN  (
		SELECT id, [name], [check], deptname
		FROM @emp
		WHERE id = 100 AND [check]=5
	) AS t2 ON t1.id = t2.id 

DELETE 
FROM @emp
WHERE id = 100 AND [check]=5

SELECT *
FROM @emp

不确定我是否正确理解了这些要求，但尝试使用类似的东西

Not sure if I understood the requirements correctly, but have a try with something like this

select a.id,
       a.name,
       a.[check],
       a.deptname
from emp a
where not exists (select 1
                  from emp b
                  where b.id = a.id
                  and   b.[check] = 2)
or   not exists (select 1
                  from emp b
                  where b.id = a.id
                  and   b.[check] = 5)
union all
select a.id,
       a.name,
       case a.[check]
          when 5 then 2
          else a.[check]
       end,
       a.deptname
from emp a
where exists (select 1
                  from emp b
                  where b.id = a.id
                  and   b.[check] = 2)
and  exists (select 1
                  from emp b
                  where b.id = a.id
                  and   b.[check] = 5)
and a.[check] <> 2
order by 1,3

我想知道deptname。在您的示例数据中，所有其他值来自原始值为5的行，但是deptname来自具有检查值2的行。不确定这是否是故意的，但如果是，那么该值应该单独获取。

I'm wondering about the deptname. In your example data all other values were from the row having originally value 5 but the deptname was from the row having check value 2. Not sure if this was intentional but if it was then that value should be fetched separately.

这篇关于在sql server中交换几行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！