求 sql语句 这个比较难 也比较急
create table tb( id int identity(1,1), uid int, --编号 u_no int, --项目 u_no_sub int, --子项 uname varchar(100) --名称)insert into tb(uid,u_no,u_no_sub,uname)select 1,1,1,'dell'unionselect 1,1,2,'机箱'union select 1,2,1,'HP'unionselect 1,3,1,'索尼'unionselect 2,1,1,'神舟'unionselect 2,2,1,'HP'union select 2,2,2,'机箱'unionselect 2,3,1,'HP'unionselect 3,1,1,'宏基'unionselect 3,2,1,'HP'union select 3,3,1,'联想'unionselect 3,3,2,'鼠标'unionselect 4,1,1,'宏基'unionselect 4,2,1,'HP'union select 4,3,1,'联想'unionselect 4,3,2,'鼠标'unionselect 4,3,3,'键盘'unionselect 5,1,1,'宏基'unionselect 5,2,1,'长城'union select 5,3,1,'HP'unionselect 5,3,2,'机箱'
--数据表中内容是这样的：id uid u_no u_no_sub uname----------- ----------- ----------- ----------- -----------1 1 1 1 dell2 1 1 2 机箱3 1 2 1 HP4 1 3 1 索尼5 2 1 1 神舟6 2 2 1 HP7 2 2 2 机箱8 2 3 1 HP9 3 1 1 宏基10 3 2 1 HP11 3 3 1 联想12 3 3 2 鼠标13 4 1 1 宏基14 4 2 1 HP15 4 3 1 联想16 4 3 2 鼠标17 4 3 3 键盘18 5 1 1 宏基19 5 2 1 长城20 5 3 1 HP21 5 3 2 机箱(21 row(s) affected)
[解决办法]
--只有一个子项的select distinct t.unamefrom tb t join (select uid,u_no from tb group by uid,u_no having count(*)=1) e on t.uid = e.uid and t.u_no = e.u_no
[解决办法]
--6L是结果二--结果一如下--只有一个子项的select t.*from tb t join (select uid,u_no from tb group by uid,u_no having count(*)=1) e on t.uid = e.uid and t.u_no = e.u_no
[解决办法]
--只要有一个子项的select a.* from tb a right join (select uid,u_no from tb group by uid,u_no having (count(1)=1))b on a.uid=b.uid and a.u_no=b.u_no/*id uid u_no u_no_sub uname----------- ----------- ----------- ----------- --------3 1 2 1 HP4 1 3 1 索尼5 2 1 1 神舟8 2 3 1 HP9 3 1 1 宏基10 3 2 1 HP13 4 1 1 宏基14 4 2 1 HP18 5 1 1 宏基19 5 2 1 长城*/
[解决办法]
--结果二select distinct a.uname from tb a right join (select uid,u_no from tb group by uid,u_no having (count(1)=1))b on a.uid=b.uid and a.u_no=b.u_no/*uname------HP长城宏基神舟索尼*/
[解决办法]
--结果三我也看不明白，搞不出你给出的结果select * from tb where uid=5 or u_no=3 or uid=2 or u_no=2/*id uid u_no u_no_sub uname----------- ----------- ----------- ----------- -----------3 1 2 1 HP4 1 3 1 索尼5 2 1 1 神舟6 2 2 1 HP7 2 2 2 机箱8 2 3 1 HP10 3 2 1 HP11 3 3 1 联想12 3 3 2 鼠标14 4 2 1 HP15 4 3 1 联想16 4 3 2 鼠标17 4 3 3 键盘18 5 1 1 宏基19 5 2 1 长城20 5 3 1 HP21 5 3 2 机箱*/
[解决办法]
--子项个数大于1的结果是这样的呀？
select a.* from tb a right join (select uid,u_no from tb group by uid,u_no having (count(1)>1))b on a.uid=b.uid and a.u_no=b.u_no/*id uid u_no u_no_sub uname----------- ----------- ----------- ----------- ---------1 1 1 1 dell2 1 1 2 机箱6 2 2 1 HP7 2 2 2 机箱11 3 3 1 联想12 3 3 2 鼠标15 4 3 1 联想16 4 3 2 鼠标17 4 3 3 键盘20 5 3 1 HP21 5 3 2 机箱*/
[解决办法]
;with maco as ( select a.* from tb a right join ( select uid , u_no from tb group by uid , u_no having ( count(1) > 1 ) ) b on a.uid = b.uid and a.u_no = b.u_no ), maco1 as ( select uid , u_no , uname = stuff(( select ',' + uname from maco t where uid = maco.uid and u_no = maco.u_no for xml path('') ), 1, 1, '') from maco group by uid , u_no ), maco2 as ( select a.* , s = case when charindex(',' + a.uname + ',', ',' + ( select uname from maco1 group by uname having ( count(1) > 1 ) ) + ',') > 0 then 0 else 1 end from tb a right join ( select uid , u_no from tb group by uid , u_no having ( count(1) > 1 ) ) b on a.uid = b.uid and a.u_no = b.u_no ) select a.id , a.uid , a.u_no , a.u_no_sub , a.uname from maco2 a left join ( select uid , u_no from maco2 group by uid , u_no having ( sum(s) = 0 ) ) b on a.uid = b.uid and a.u_no = b.u_no where b.uid is NULL/*id uid u_no u_no_sub uname----------- ----------- ----------- ----------- -------------2 1 1 2 机箱11 3 3 1 联想12 3 3 2 鼠标15 4 3 1 联想16 4 3 2 鼠标17 4 3 3 键盘*/
[解决办法]
;with maco as ( select a.* from tb a right join ( select uid , u_no from tb group by uid , u_no having ( count(1) > 1 ) ) b on a.uid = b.uid and a.u_no = b.u_no ) , maco1 as ( select uid , u_no , uname = stuff(( select ',' + uname from maco t where uid = maco.uid and u_no = maco.u_no for xml path('') ), 1, 1, '') from maco group by uid , u_no ), maco2 as ( select a.* , s = case when charindex(',' + a.uname + ',', ',' + ( select uname from maco1 group by uname having ( count(1) > 1 ) ) + ',') > 0 then 0 else 1 end from tb a right join ( select uid , u_no from tb group by uid , u_no having ( count(1) > 1 ) ) b on a.uid = b.uid and a.u_no = b.u_no ) select a.id , a.uid , a.u_no , a.u_no_sub , a.uname from maco2 a left join ( select uid , u_no from maco2 group by uid , u_no having ( sum(s) = 0 ) ) b on a.uid = b.uid and a.u_no = b.u_no where b.uid is NULL/*id uid u_no u_no_sub uname----------- ----------- ----------- ----------- ---------1 1 1 1 dell2 1 1 2 机箱11 3 3 1 联想12 3 3 2 鼠标15 4 3 1 联想16 4 3 2 鼠标17 4 3 3 键盘*/
[解决办法]
;with maco as ( select a.* from tb a right join ( select uid,u_no from tb group by uid ,u_no having ( count(1) > 1 ) ) b on a.uid = b.uid and a.u_no = b.u_no),maco1 as ( select uid ,u_no ,uname = stuff(( select ',' + uname from maco t where uid = maco.uid and u_no = maco.u_no order by uname for xml path('')), 1, 1, '') from maco group by uid ,u_no),maco2 as ( select * from ( select row_number() over ( partition by uname order by uid ) as rid ,* from maco1) a where rid = 1)select a.id ,a.uid ,a.u_no ,a.u_no_sub ,a.unamefrom maco a left join maco2 b on a.uid = b.uidand a.u_no = b.u_no where b.uid is not NULL--重复的保留了一组，前后顺序不影响结果/*id uid u_no u_no_sub uname----------- ----------- ----------- ----------- ---------1 1 1 1 dell2 1 1 2 机箱6 2 2 1 HP7 2 2 2 机箱11 3 3 1 联想12 3 3 2 鼠标15 4 3 1 联想16 4 3 2 鼠标17 4 3 3 键盘*/
[解决办法]
--1.select uid,u_no,u_no_sub,unamefrom tb awhere id<13and not exists(select 1 from tb where uid=a.uid and u_no=a.u_no and u_no_sub<>a.u_no_sub)order by uid,u_no/*uid u_no u_no_sub uname----------- ----------- ----------- ----------------------------------------------------------------1 2 1 HP1 3 1 索尼2 1 1 神舟2 3 1 HP3 1 1 宏基3 2 1 HP(6 行受影响)*/--2.select distinct uname from(select uid,u_no,u_no_sub,unamefrom tb awhere id<13and not exists(select 1 from tb where uid=a.uid and u_no=a.u_no and u_no_sub<>a.u_no_sub))t/*uname----------------------------------------------------------------HP宏基神舟索尼(4 行受影响)*/--3.看不懂楼主说的什么