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'
union
select 1,1,2,'机箱'
union
select 1,2,1,'HP'
union
select 1,3,1,'索尼'union
select 2,1,1,'神舟'
union
select 2,2,1,'HP'
union
select 2,2,2,'机箱'
union
select 2,3,1,'HP'union
select 3,1,1,'宏基'
union
select 3,2,1,'HP'
union
select 3,3,1,'联想'
union
select 3,3,2,'鼠标'
union
select 4,1,1,'宏基'
union
select 4,2,1,'HP'
union
select 4,3,1,'联想'
union
select 4,3,2,'鼠标'
union
select 4,3,3,'键盘'
union
select 5,1,1,'宏基'
union
select 5,2,1,'长城'
union
select 5,3,1,'HP'
union
select 5,3,2,'机箱'
--想要的结果--提示 结果1结果2 当时没有id 为12以后的数据
--去除重复 重复只考虑uname
--结果1 及 只要有一个子项的
1 2 1 HP
1 3 1 索尼
2 1 1 神舟
2 3 1 HP
3 1 1 宏基
3 2 1 HP --结果2 根据结果1去除名称一样的 只保留一个
HP
索尼
神舟
宏基--结果三
uid为5 u_no为3的 与 uid为2 u_no为2的一致 去掉重复
6 2 2 1 HP
7 2 2 2 机箱
11 3 3 1 联想
12 3 3 2 鼠标
16 4 3 2 鼠标
17 4 3 3 键盘
(
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'
union
select 1,1,2,'机箱'
union
select 1,2,1,'HP'
union
select 1,3,1,'索尼'union
select 2,1,1,'神舟'
union
select 2,2,1,'HP'
union
select 2,2,2,'机箱'
union
select 2,3,1,'HP'union
select 3,1,1,'宏基'
union
select 3,2,1,'HP'
union
select 3,3,1,'联想'
union
select 3,3,2,'鼠标'
union
select 4,1,1,'宏基'
union
select 4,2,1,'HP'
union
select 4,3,1,'联想'
union
select 4,3,2,'鼠标'
union
select 4,3,3,'键盘'
union
select 5,1,1,'宏基'
union
select 5,2,1,'长城'
union
select 5,3,1,'HP'
union
select 5,3,2,'机箱'
--想要的结果--提示 结果1结果2 当时没有id 为12以后的数据
--去除重复 重复只考虑uname
--结果1 及 只要有一个子项的
1 2 1 HP
1 3 1 索尼
2 1 1 神舟
2 3 1 HP
3 1 1 宏基
3 2 1 HP --结果2 根据结果1去除名称一样的 只保留一个
HP
索尼
神舟
宏基--结果三
uid为5 u_no为3的 与 uid为2 u_no为2的一致 去掉重复
6 2 2 1 HP
7 2 2 2 机箱
11 3 3 1 联想
12 3 3 2 鼠标
16 4 3 2 鼠标
17 4 3 3 键盘
--数据表中内容是这样的：
id uid u_no u_no_sub uname
----------- ----------- ----------- ----------- -----------
1 1 1 1 dell
2 1 1 2 机箱
3 1 2 1 HP
4 1 3 1 索尼
5 2 1 1 神舟
6 2 2 1 HP
7 2 2 2 机箱
8 2 3 1 HP
9 3 1 1 宏基
10 3 2 1 HP
11 3 3 1 联想
12 3 3 2 鼠标
13 4 1 1 宏基
14 4 2 1 HP
15 4 3 1 联想
16 4 3 2 鼠标
17 4 3 3 键盘
18 5 1 1 宏基
19 5 2 1 长城
20 5 3 1 HP
21 5 3 2 机箱(21 row(s) affected)
--只有一个子项的select distinct t.uname
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
--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 HP
4 1 3 1 索尼
5 2 1 1 神舟
8 2 3 1 HP
9 3 1 1 宏基
10 3 2 1 HP
13 4 1 1 宏基
14 4 2 1 HP
18 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 HP
4 1 3 1 索尼
5 2 1 1 神舟
6 2 2 1 HP
7 2 2 2 机箱
8 2 3 1 HP
10 3 2 1 HP
11 3 3 1 联想
12 3 3 2 鼠标
14 4 2 1 HP
15 4 3 1 联想
16 4 3 2 鼠标
17 4 3 3 键盘
18 5 1 1 宏基
19 5 2 1 长城
20 5 3 1 HP
21 5 3 2 机箱
*/
对于同一个uid u_no一样说明这个包含子项
u_no_sub为子项编号
要得结果是 子项个数大于1 的 不重复的
例如
项 子项 名称
1 1 鼠标
1 2 键盘
1 3 光盘
2 1 鼠标
2 2 键盘
2 3 光盘
3 1 键盘
3 2 鼠标
对于项1来说 3个算个组合 判断重复 前6行应该去掉三行
结果为
项 子项 名称
1 1 鼠标
1 2 键盘
1 3 光盘
3 1 键盘
3 2 鼠标因为项 3 的2个结果和项1的不一致
(
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 dell
2 1 1 2 机箱
6 2 2 1 HP
7 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 HP
21 5 3 2 机箱
*/
结果是对的 但是重复没有去掉啊6 2 2 1 HP
7 2 2 2 机箱20 5 3 1 HP
21 5 3 2 机箱这2个结果是一样的啊 因为组合起来都是 HP机箱
;
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 dell
2 1 1 2 机箱
11 3 3 1 联想
12 3 3 2 鼠标
15 4 3 1 联想
16 4 3 2 鼠标
17 4 3 3 键盘
*/
现在的结果是这样的....
不好意思我忘了考虑顺序了 以下两个也是要去除重复的
假设
6 2 2 1 机箱
7 2 2 2 HP20 5 3 1 HP
21 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
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.uname
from maco a left join maco2 b on a.uid = b.uid
and a.u_no = b.u_no where b.uid is not NULL--重复的保留了一组，前后顺序不影响结果
/*
id uid u_no u_no_sub uname
----------- ----------- ----------- ----------- ---------
1 1 1 1 dell
2 1 1 2 机箱
6 2 2 1 HP
7 2 2 2 机箱
11 3 3 1 联想
12 3 3 2 鼠标
15 4 3 1 联想
16 4 3 2 鼠标
17 4 3 3 键盘
*/
--例如你的uname中有空格之累会导致结果不正确
' Dell'和' Dell'是不相同的
--你检查一下数据看看有什么特殊的地方。
select uid,u_no,u_no_sub,uname
from tb a
where id<13
and 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 HP
1 3 1 索尼
2 1 1 神舟
2 3 1 HP
3 1 1 宏基
3 2 1 HP(6 行受影响)*/
--2.
select distinct uname from(
select uid,u_no,u_no_sub,uname
from tb a
where id<13
and 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.看不懂楼主说的什么