有两个表
tableA
text orderID
PPR 1
PFR 2
SMS 3
...TableB --Season,pjectid,stage唯一,ID为主健
ID Season PjectID Stage .....
C001 FW09 P_001 PPR .....
C002 FW09 P_001 PFR ..
C003 FW09 P_001 SMS
C004 SS09 P_002 PFR
C004 SS09 P_002 PPR
....
根据Season,PjectID分组后,再根据tableB指定的排列顺序得到TableB的记录,正确的结果如下
ID Season PjectID Stage
C003 FW09 P_001 SMS
C004 SS09 P_002 PFR我的sql语句是这样的:
select * from tableB inner join
(select Season,PjectID,
(select top 1 stage from TableB inner join tableA on TableA.text=TableB.Stage where TableB.Season=b.season and tableB.pjectID=B.PjectID order by orderID desc) as Stage from TableB b group by Season,PjectID) a on TableB.Season=a.Season and TableB.PjectID=a.PjectID and TableB.Stage=a.Stage
想请教一下,还有没有更好的方式?
另外一个问题
假设有@Season变量等于FW09,则select * from TableB where Season in(@Season)可以执行
但如果我想把SS09也放入到@Season变量中,在@Season变量中应该是个什么样的格式,比如用什么字符隔开
因为我不想用字符连接后再用exec执行
求解
tableA
text orderID
PPR 1
PFR 2
SMS 3
...TableB --Season,pjectid,stage唯一,ID为主健
ID Season PjectID Stage .....
C001 FW09 P_001 PPR .....
C002 FW09 P_001 PFR ..
C003 FW09 P_001 SMS
C004 SS09 P_002 PFR
C004 SS09 P_002 PPR
....
根据Season,PjectID分组后,再根据tableB指定的排列顺序得到TableB的记录,正确的结果如下
ID Season PjectID Stage
C003 FW09 P_001 SMS
C004 SS09 P_002 PFR我的sql语句是这样的:
select * from tableB inner join
(select Season,PjectID,
(select top 1 stage from TableB inner join tableA on TableA.text=TableB.Stage where TableB.Season=b.season and tableB.pjectID=B.PjectID order by orderID desc) as Stage from TableB b group by Season,PjectID) a on TableB.Season=a.Season and TableB.PjectID=a.PjectID and TableB.Stage=a.Stage
想请教一下,还有没有更好的方式?
另外一个问题
假设有@Season变量等于FW09,则select * from TableB where Season in(@Season)可以执行
但如果我想把SS09也放入到@Season变量中,在@Season变量中应该是个什么样的格式,比如用什么字符隔开
因为我不想用字符连接后再用exec执行
求解
b.*
from
tableB a
join
tableB b on b.Season=a.Season and b.PjectID=a.PjectID and b.Stage=a.Stage
where
a.stage=(select top 1 [text] from tableA c where exists(select 1 from tableB where stage=c.[text]) order by orderID desc)
因为我不想用字符连接后再用exec执行 where Season=@Season--直接用