在sql需要帮助.....

本文介绍了在sql需要帮助.....的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 先生，我有2个包含以下列的表： - 1）第一个表是ClassRoom ClassRoomClassStrength ME-336 C-20249 2）第二张桌子是Matrix Roomno SeatNo ME-31 ME-32 ME-33 C-2021 C-2022 i有一个SQL查询： - sir,i have 2 tables that contain column like :-1)First table is "ClassRoom""ClassRoom" "ClassStrength""ME-3" 36"C-202" 492)Second Table is MatrixRoomno SeatNo "ME-3" 1"ME-3" 2 "ME-3" 3 "C-202" 1 "C-202" 2 i have a sql query:-select distinct list,max(cc) from(( select distinct Convert(varchar,ClassRoom,106) +' > '+ Convert(varchar,ClassStrength,106) +' > '+ '0' as List ,Classroom,0 as cc from ClassRoomleft join Matrix on matrix.roomno=classroom.classroomwhere classstrength is not nullgroup by classroom.classroom,ClassStrength )union all( select distinct Convert(varchar,ClassRoom,106) +' > '+ Convert(varchar,ClassStrength,106) +' > '+ case when Count(seatno) > 0 then Convert(varchar,Count(Seatno),106) else '0' end as List,Classroom ,Count(Seatno) as cc from ClassRoomleft join Matrix on matrix.roomno=classroom.classroomwhere examid=1 and examname='MST1' and classstrength is not nullgroup by classroom.classroom,ClassStrength)) as tgroup by list,Classroom 输出赞： - C-202> 49> 00 ME-3> 36> 00 ME-3> 36> 3636 A-302> 36> 00 A-302> 36> 3636 A-303> 42> 00 但我需要输出如下： - C-202> 49> 00 ME-3> 36> 3636 A-302> 36> 3636 A-303> 42> 00 我该怎么做.. Output Like:- "C-202 > 49 > 0""0" "ME-3 > 36 > 0""0" "ME-3 > 36 > 36""36" "A-302 > 36 > 0""0" "A-302 > 36 > 36""36" "A-303 > 42 > 0""0"But i need Output like:- "C-202 > 49 > 0""0" "ME-3 > 36 > 36""36" "A-302 > 36 > 36""36" "A-303 > 42 > 0""0"how can i do it..推荐答案 你应该删除UNION和因为ClassRoom不是日期时间字段，CONVERT的重点是什么？ 计数已经返回0所以你不需要CASE当返回0时，只需给出值，你正在使用LEFT JOIN，这将再次返回所有值，UNION不需要。 You should remove UNION and use only lower one. Also, what is the point of CONVERT since the ClassRoom isn't datetime field?Count will already return 0 so you don't need CASE WHEN returning 0, just give the value, you're using LEFT JOIN which will return all the values so again, UNION IS NOT NEEDED.SELECT DISTINCT list, MAX(cc) FROM ( SELECT DISTINCT CAST(ClassRoom as varchar(10)) +' > ' + CAST(ClassStrength as varchar(5)) +' > ' + CAST(Count(Seatno) as varchar(10)) as List, Classroom ,Count(Seatno) as ccFROM ClassRoom LEFT JOIN Matrix on matrix.roomno=classroom.classroomWHERE examid=1 and examname='MST1' and classstrength is not nullGROUP BY classroom.classroom,ClassStrength) tGROUP BY list 如果我错过了一些重要的事情CONVERT（可能是你的文化设置依赖）只是将演员表改回转换:) 如果这有帮助请花时间接受解决方案。谢谢。If I missed something important with CONVERT (maybe its your culture settings dependant) simply change casts back to converts :)If this helps please take time to accept the solution. Thank you. 这篇关于在sql需要帮助.....的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！