大家好, 我在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查询
发布评论