需要帮助来形成SQl查询

本文介绍了需要帮助来形成SQl查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

大家好， 我在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