select v.id from newversion v
inner join newparameter p on p.versionid = v.id
left join newfeature f on f.versionid = v.id
inner join newmodel m on v.modelid = m.id
inner join made md on md.id = v.madeid where v.id in
('DB314946-2340-4E1A-AC83-00488F58DC47','46FF0317-913B-4C79-8344-0055471BAD70','C57855A6-F847-4E12-A3B1-0068070D9D0F','15343F13-CB75-4D03-B2D7-0068CB691C67')
上述SQL语句他能够执行正确,但是,所查询出来的结果确没有按照SQL语句中的
('DB314946-2340-4E1A-AC83-00488F58DC47','46FF0317-913B-4C79-8344-0055471BAD70','C57855A6-F847-4E12-A3B1-0068070D9D0F','15343F13-CB75-4D03-B2D7-0068CB691C67')
顺序出现,查出来的结果如下:
请问该如何解决这问题,我现在要的结果必须是按照SQL语句的'DB314946-2340-4E1A-AC83-00488F58DC47','46FF0317-913B-4C79-8344-0055471BAD70','C57855A6-F847-4E12-A3B1-0068070D9D0F','15343F13-CB75-4D03-B2D7-0068CB691C67'
这顺序来出现。
select v.id from newversion v
inner join newparameter p on p.versionid = v.id
left join newfeature f on f.versionid = v.id
inner join newmodel m on v.modelid = m.id
inner join made md on md.id = v.madeid where v.id in
('DB314946-2340-4E1A-AC83-00488F58DC47','46FF0317-913B-4C79-8344-0055471BAD70','C57855A6-F847-4E12-A3B1-0068070D9D0F','15343F13-CB75-4D03-B2D7-0068CB691C67')
order by charindex(','+v.id+',',',DB314946-2340-4E1A-AC83-00488F58DC47,46FF0317-913B-4C79-8344-0055471BAD70,C57855A6-F847-4E12-A3B1-0068070D9D0F,15343F13-CB75-4D03-B2D7-0068CB691C67,' )
编上号,再关联!
解决方法有3
1 可以在插入的时候加一列表示顺序
2 或用 UNION
select v.id from newversion v
inner join newparameter p on p.versionid = v.id
left join newfeature f on f.versionid = v.id
inner join newmodel m on v.modelid = m.id
inner join made md on md.id = v.madeid where v.id =
('DB314946-2340-4E1A-AC83-00488F58DC47')
union
select v.id from newversion v
inner join newparameter p on p.versionid = v.id
left join newfeature f on f.versionid = v.id
inner join newmodel m on v.modelid = m.id
inner join made md on md.id = v.madeid where v.id =
('46FF0317-913B-4C79-8344-0055471BAD70')
...
3 查询做文章,没测试过不太确定是否是预期的,你试试咯select v.id, (cast(getdate() as varchar(20)) + v.id) from newversion v
inner join newparameter p on p.versionid = v.id
left join newfeature f on f.versionid = v.id
inner join newmodel m on v.modelid = m.id
inner join made md on md.id = v.madeid
where (cast(getdate() as varchar(20)) + v.id) in
(
cast(getdate() as varchar(20)) + 'DB314946-2340-4E1A-AC83-00488F58DC47',
cast(getdate() as varchar(20)) + '46FF0317-913B-4C79-8344-0055471BAD70',
cast(getdate() as varchar(20)) + 'C57855A6-F847-4E12-A3B1-0068070D9D0F',
cast(getdate() as varchar(20)) + '15343F13-CB75-4D03-B2D7-0068CB691C67'
)
order by (cast(getdate() as varchar(20)) + v.id)
呵呵
消息 402,级别 16,状态 1,第 1 行
数据类型 varchar 和 uniqueidentifier 在 add 运算符中不兼容。
晕,但仍然有错误呢。。
注意:我的ID类型是 uniqueidentifier 类型的
你是怎么测试的?难道也有那些表不成?不兼容的话,自己类型转换下就可以了,用convert函数都转换到varchar。
inner join newparameter p on p.versionid = v.id
left join newfeature f on f.versionid = v.id
inner join newmodel m on v.modelid = m.id
inner join made md on md.id = v.madeid where v.id in
('DB314946-2340-4E1A-AC83-00488F58DC47','46FF0317-913B-4C79-8344-0055471BAD70','C57855A6-F847-4E12-A3B1-0068070D9D0F','15343F13-CB75-4D03-B2D7-0068CB691C67')
order by charindex(','+CAST(v.id as varchar(50))+',',',DB314946-2340-4E1A-AC83-00488F58DC47,46FF0317-913B-4C79-8344-0055471BAD70,C57855A6-F847-4E12-A3B1-0068070D9D0F,15343F13-CB75-4D03-B2D7-0068CB691C67,' )
晕,不知道怎么转换,能否提示一下,偶的SQL比较菜