需要帮助来形成SQl查询 [英] Need help to form SQl query

问题描述

大家好，

我在sql server中有3个表，即Owner，Asset和AssetOwner.

Hi all,

I have 3 tables in sql server namely Owner, Asset and AssetOwner.

Owner:-

 Owner,    Domain, Position, Project,  ID
1. Alex  - ABC  -   0     -  X1   -   111
2. Peter - PQR  -   1     -  X2   -   222
3. Sam   - GHI  -   2     -  X3   -   333 
4. Alex  - LMN  -   3     -  X4   -   111
    
Asset :-

  Asset,   Position,   Center
1. ABC   - 0        -  Ivert 
2. PQR   - 1        -  SGYt
3. GHI   - 2        -  RenGT
4. LMN   - 3        -  IUOy

AssetOwner:- 
    
   Owner,     Asset,   Center
1. Alex   -   ABC     
2. Peter  -   PQR     
3. Sam    -   GHI     
4. Alex   -   LMN

使用前两个表数据(资产和所有者表)，我需要使用以下步骤填充AssetOwner表中第3列-Center的值.

在所有者"表中查找域的所有者.检查所有者的任何域是否具有相同的ID.
如果是(行1和4)，则获取对应的第一行(行1)并获得Position(列3)的值.在资产"表中，检索相应头寸的中心"值.在AssetOwner表中为相应所有者更新Center的值.

如果否(第2行和第3行)，则获取Project的ID列(第4列)以获取Position(第3列)的值.在资产"表中，检索相应头寸的中心"值.为相应所有者更新AssetOwner表中Center的值.

请帮助我形成以下查询.有帮助吗?

预先感谢.

Using the first 2 tables data(Asset and Owner table), I need to fill the value for column 3 -Center in AssetOwner table using below steps.

Find owner of a domain in Owner table. Check if any of the domain of an owner has same ID.
if yes (rows 1 & 4), take the first corresponding row (row 1) and get the value of Position(col 3). In Asset table, retrieve value of Center for the corresponding Position. Update value of Center in AssetOwner Table for the corresponding owner.

If no(rows 2 & 3), take ID column for Project ( col 4) get the value of Position(col 3). In Asset table, retrieve value of Center for the corresponding Position. Update value of Center in AssetOwner Table for the corresponding owner.

Please help me form the below query. Will a help be of any help

Thanks in advance.

推荐答案

Shruti ..

尝试以下代码块

Hi Shruti ..

Try the following code block

 WITH OACTE AS (
  SELECT O.Owner,O.Domain,A.Center,O.Id,ROW_NUMBER()
  OVER (PARTITION BY O.Owner,O.Id ORDER BY O.Id) ROWNUM
  FROM Owner O JOIN Asset A ON O.Domain = A.Asset
)

SELECT Owner,Domain,CASE WHEN ROWNUM > 1 THEN
 (SELECT TOP 1 Center FROM OACTE WHERE Owner = OA.Owner AND Id = OA.ID)
 ELSE OA.Center END As Center  FROM OACTE As OA

谢谢

Thank you

尝试一下

try this

update ao
set
ao.Center=a.center
from AssetOwner ao
join asset a1 on a1.asset=ao.Asset
join owner o1 on o1.Owner=ao.owner and a1.Asset=o1.Domain
join owner o2 on o1.owner=o2.owner and o1.Id=o2.Id and  o1.Owner=ao.owner
join asset a on a.possition=o1.position
where o1.domain=(select top 1 domain from  owner where id=o1.id and owner=o1.Owner)

我希望这对您有用

i hope this will work for you

这篇关于需要帮助来形成SQl查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！